Is there a better, SQL way to do this Ruby's Array.delete_if with a Rails.find request?
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
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.
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",
])
精彩评论