CODEWITHSUNDEEP
ruby-on-railsrubypostgresqlruby-on-rails-4activerecord
Rog
Rog

Reputation: 18670

Rails ActiveRecord - querying on last record of relationship

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?

Upvotes: 4

Views: 1233

Answers (2)

Fran Martinez
Fran Martinez

Reputation: 3042

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')

Upvotes: 1

lx00st
lx00st

Reputation: 1596

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

Upvotes: 4

Related Questions