Rails ActiveRecord where or clause

Question

I'm looking to write an ActiveRecord query and this is what I have below. Unfortunately you can't use OR like this. What's the best way to execute? category_ids is an array of integers.

.where(:"categories.id" => category_ids).or.where(:"category_relationships.category_id" => category_ids)

Solution 1

One way is to revert to raw sql...

YourModel.where("categories.id IN ? OR category_relationships.category_id IN ?", category_ids, category_ids)

Solution 2

Keep the SQL out of it and use ARel, like this:

.where(Category.arel_table[:id].in(category_ids).
  or(CategoryRelationship.arel_table[:category_id].in(category_ids))

Comments

[florish]

Some security advice: unless you can fully trust all of your query parameters, it's best to use the escaped query syntax, e.g. where("categories.id IN (?) OR category_relationships.category_id IN (?)", category_ids, category_ids).

[Philihp Busby]

While it's fun and neat to abstract out SQL, it's hard to see any gains by doing so. I would argue it's much harder to read. If you're bothering to compile your Ruby when you build, you could catch errors then, but your RSpec tests will also catch those errors. You did write tests, right?

[Wilson Freitas]

ARel is the perfect anti pattern example for people who want to apply the KISS principle.

[Dan]

To avoid having to say category_ids twice you can use this syntax: .where("categories.id IN :c OR category_relationships.category_id IN :c", c: category_ids)