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



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

2 answers

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)


And your request would be like this:

Book.where(current_state: 'public') 




You can use window function:

    .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



All Articles