Smoke Signals

Tuesday, May 03, 2005

Related Tags and Sub-Selects

I am using a query to fetch related tags to an object. It seemed fast enough but then again, I am the only one using the system right now. Court3nay from wrote a good article as to why my way sucked and how to make it better. So here is what I ended up with:

In my application controller I created a helper_method that found all items using the tag in question: items = Item.find_by_sql("SELECT it.item_id FROM items_tags it, tags t WHERE it.tag_id = AND = #{}") then I made an array of the id's: items.each {|item| item_ids << item.item_id} #make an array of each item using that tag. Then I passed that to the model method: find_by_sql("SELECT *, COUNT(it.page_id) AS count FROM items_tags it, tags t WHERE it.item_id IN (" + item_ids.join(',') + ") and viola, a more efficient query by splitting it up into 2.


Post a Comment

<< Home