Is there a better, SQL way to do this Ruby's Array.delete_if with a Rails.find request?

Question

Hi,

I have a Tag object (id, name) and a Tagging object (id, tag_id, type). I want to find all the tags that have a name like "kevin" and for which I can find a foreign Tagging object with type set to "people" (type can be set to people or some other tagging stuff).

I tried with a complex SQL request in a Rails Tag.find method but didn't go far, so I'm now trying with two lines, using Ruby's delete_if method:

people = Tag.find(:all, :conditions => ["name like ?", "Kevin%"])
people.delete_if { |p| Tagging.find(:all, :conditions => { :tag_id => p.id, :type => "people" }).empty? }

It actually works, but there must be a smarter way to do this directly into the database, right?

Thanks for your help,

Kevin

Answer 1

There are a few ways this could be improved.

The simplest improvement would be to do a "SELECT count(*)" instead of loading up ActiveRecords for those Taggings.

people = Tag.all(:conditions => ["name LIKE ?", "Kevin%"])
people.delete_if { |p| Tagging.count(:conditions => { :tag_id => p.id, :type => "people" }) > 0 }

But this whole thing could also be done in SQL with a subquery:

people = Tag.all(:conditions => [
     "name LIKE ? AND id IN (SELECT tag_id FROM people WHERE type=?)", 
     "Kevin%",
     "people",
])

Answer 2

Try this:

Tag.all(:joins => :taggings, :group => :id, 
        :conditions => ["tags.name LIKE ? AND taggings.type = ?", 
                         "Kevin%", "person"]
)

Returns the tags starting with Kevin for type = "person".

Note: The group option is required to eliminate duplicates.