CODEWITHSUNDEEP
sqlruby-on-railsactiverecordwhere-clause
bcackerman
bcackerman

Reputation: 1554

Rails ActiveRecord where or clause

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)

Upvotes: 8

Views: 16974

Answers (4)

tihom
tihom

Reputation: 8003

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.

Upvotes: 0

Paul Sturgess
Paul Sturgess

Reputation: 3294

One way is to revert to raw sql...

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

Upvotes: 15

pdobb
pdobb

Reputation: 18037

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

Upvotes: 7

Jason Pudzianowski
Jason Pudzianowski

Reputation: 369

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(',')})")

Upvotes: -3

Related Questions