开发者

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",
])
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜