CODEWITHSUNDEEP
ruby-on-rails
Tom Lehman
Tom Lehman

Reputation: 89193

Complex filtering based on a many-to-many relationship

In my application, Annotations are considered "accepted" if either:

  1. They have been explicitly marked "accepted" (i.e., their state == 'accepted')
  2. They were last updated by a user who has the "editor" role

My question is how to find all accepted explanations with a single DB query. Basically I'm looking for the database-driven version of

Annotation.all.select do |a|
  a.last_updated_by.roles.map(&:name).include?('editor') or a.state == 'accepted'
end

My first attempt was

Annotation.all(:joins => {:last_updated_by => :roles}, :conditions => ['roles.name = ? or annotations.state = ?', 'editor', 'accepted'])

But this returns a bunch of duplicate records (adding a .uniq makes it work though)

Changing :joins to :include works, but this makes the query way too slow

Upvotes: 1

Views: 66

Answers (1)

elmac
elmac

Reputation: 228

Are the results of your first attempt just wrong or do they only need an ".uniq"? Have you tried 

:include => {:last_updated_by => [:roles]}

instead of the join?

or making two queries

@ids = Editor.all(:conditions => ["role = 'editor'"], :select => ["id"]).map{|e|e.id}
Annotation.all(:conditions => ["last_updated_by in (?) or state = ?", @ids.join(","), "accepted"]

is that any faster?

Upvotes: 1

Related Questions