Rails ActiveRecord - querying on last record of relationship

Question

I have a model Book with a has_many relationship to State.

class Book < ActiveRecord::Base
   has_many :states
   ...
end

State sets the visibility of the book to "private", "restricted" or "public" through a name attribute. For auditing purposes, we keep a record of all state changes so to get the current state of a book I use

> book = Book.first
> book.states.last.name
> "public"

Now I need to query on all Book objects where the current state is public.

Something along the lines of:

Book.joins(:visibility_states).where(states: { state: "public"})

The problem is that the query above returns all books that are currently public, or have been public in the past. I only want it to return books that are currently "public" (i.e. book.states.last.name == "public").

I know I can do it with select but that's generating one query for each record:

Book.all.select { |b| b.states.last.name == "public" }

Is there a way to do it using ActiveRecord only?

Answer 1

You can use window function:

Book.joins(:visibility_states)
    .where(states: { state: "public"})
    .where("visibility_states.id = FIRST_VALUE(visibility_states.id)
            OVER(PARTITION BY books.id ORDER BY visibility_states.created_at DESC))")

Or may be in your situation it would be better to save current state in Book model

Answer 2

I will do something with better performance.

If you want to save the historical state changes, is it ok. But try to avoid to introduce more complexity to your application because of this.

Why don't you add a current_state attribute in your Book model?

It will be much faster, and easier to develop.

class Book < ActiveRecord::Base

  def set_new_current_state!(new_state)
     self.current_state = new_state # e.g State.public
     self.stats << State.create(new_state)
  end
end

And your query will be just this:

Book.where(current_state: 'public')