Trevor Turk

A chess-playing machine of the late 18th century, promoted as an automaton but later proved a hoax.

Active Record and the IN clause

So here's the setup:
Site => Categories => Forums => Topics => Posts
That's a pretty standard setup for a web-based forum, where Topics are in a particular Forum and those Forums are, in turn, organized by Category. Retrieving the Topics in a particular Forum is easy, since I have a "forum_id" column in my "topics" table:
@topics = Topic.find(:all, :conditions => ["forum_id = ?", @forum.id])
But what if I want to retrieve the Topics in a particular Category? That's not quite as easy, since there's no "category_id" column in my "topics" table. What to do? The "WHERE IN" clause is what you're supposed to use here, I think. That's when you make a query something like this:
@topics = Topic.find(:all, :conditions => ["forum_id in ?", @forums])
This might read something like "find all topics where the forum_id is in my @forums object". But (of course) that won't work. What to do? Hack together a string of forum_ids that's worthy of passing into this query? There's got to be a better way. When in doubt, I always turn to the fabulous open-source work of one Rick Olson, because searching around the Rails API or plain old Google isn't nearly as informative as seeing real live code examples. And when it comes to really nice real live code examples, it doesn't get any better than techno-weenie. So, what's the solution?
@category = Category.find(params[:id])
@forums = Forum.find_all_by_category_id(@category.id)
@topics = Topic.find(:all, :conditions => ["forum_id in (?)", @forums.collect(&:id)])
Easy as pie.

« Home page