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)

which table are your trying to retrieve, Category or something else associated with it? — tihom, Sep 18 '13 at 09:53
possible duplicate of [ActiveRecord OR query](http://stackoverflow.com/questions/3639656/activerecord-or-query) — user, Jul 26 '15 at 03:52

score 15 · Accepted Answer · answered Sep 17 '13 at 22:40

15

One way is to revert to raw sql...

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

answered Sep 17 '13 at 22:40

Paul Sturgess

3,254
2
23
22

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)` – Dan Apr 11 '18 at 17:39

score 7 · Answer 2 · answered Sep 17 '13 at 22:37

7

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))

answered Sep 17 '13 at 22:37

pdobb

17,688
5
59
74

4

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? – Philihp Busby Oct 29 '14 at 21:21
2

ARel is the perfect anti pattern example for people who want to apply the KISS principle. – Wilson Freitas Jun 16 '16 at 18:22

score 0 · Answer 3 · answered Sep 18 '13 at 07:32

Assuming you want to return Categories, you need to OUTER JOIN category_relationships and put a OR condition on the combined table.

Category.includes(:category_relationships).where("categories.id IN (?) OR category_relationships.category_id IN (?)",category_ids,category_ids )

This query is creating an outer join table by combining columns of categories and category_relationships. Unlike an inner join (e.g. Category.joins(:category_relationships)), outer join table would also have categories with no associated category_relationship. It would then apply the conditions in whereclause on the outer join table to return the matching records.

includes statement without conditions on the association usually makes two separate sql queries to retrieve the records and their association. However when used with conditions on the associated table, it would make a single query to create an outer join table and run conditions on the outer join table. This allows you to retrieve records with no association as well.

See this for a detailed explanation.

score -3 · Answer 4 · answered Sep 17 '13 at 22:36

-3

What you want to do is manually write the OR part of the query like this:

.where("category.id in (#{category_ids.join(',')}) OR category_relationships.category_id in (#{category_ids.join(',')})")

answered Sep 17 '13 at 22:36

Jason Pudzianowski

359
3
9

4

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)`. – florish Mar 12 '14 at 11:19

Rails ActiveRecord where or clause

4 Answers4

Linked

Related