Rails ActiveRecord - Query for Last Relationship Record
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" using an attribute name
. For auditing purposes, we keep a record of all state changes to get the current state of the workbook I'm using.
> book = Book.first
> book.states.last.name
> "public"
Now I need to query all objects Book
where the current state is public.
Something line by line:
Book.joins(:visibility_states).where(states: { state: "public"})
The problem is that the above query returns all books that are currently public or were public in the past. I only want it to return books that are currently "public" (ie Book.states.last.name == "public").
I know I can do it with select, but creating one query for each record:
Book.all.select { |b| b.states.last.name == "public" }
Is there a way to do this using only ActiveRecord?
source to share
I'll do something with better performance.
If you want to preserve historical state changes, that's fine. But try to avoid the more complex application of your application because of this.
Why don't you add the current_state attribute to 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 request would be like this:
Book.where(current_state: 'public')
source to share
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 maybe in your situation it would be better to keep the current state in a book model
source to share