CODEWITHSUNDEEP
ruby-on-railsrubyactiverecord
Code Ninja
Code Ninja

Reputation: 1

How to combine two queries in Rails using OR support

I'd like to join two queries using Active Record's "OR":

@category_ids = [4,5,6,7]
@sub_category_ids = [1,3,5,6]

@products1 = @products.joins(:inventory_categorizations).distinct.where(inventory_categorizations: {inventory_category_id: @sub_category_ids, inventory_category_type: "SubCategory"})

@products2 = @products.joins(:inventory_categorizations).distinct.where(inventory_categorizations: {inventory_category_id: @category_ids, inventory_category_type: "Category"})

@products = @products1 | @products2

Is there anyway that I can use this?

@products = @products.joins(:inventory_categorizations).distinct.where(inventory_categorizations: {inventory_category_id: @sub_category_ids, inventory_category_type: "SubCategory" or inventory_category_id: @category_ids, inventory_category_type: "Category"})

Upvotes: 0

Views: 128

Answers (2)

Foo Bar Zoo
Foo Bar Zoo

Reputation: 216

You can achieve that goal in two different ways;

  1. Writing the where clause on your own:

    where_clause = <<~SQL
  (
    inventory_categorizations.inventory_category_id = ?
    AND 
    inventory_categorizations.inventory_category_type = ?
  )
  OR 
  (
    inventory_categorizations.inventory_category_id = ?
    AND
    inventory_categorizations.inventory_category_type = ?
  )
SQL

@products.joins(:inventory_categorizations)
        .where(where_clause, @sub_category_ids, "SubCategory", @category_ids, "Category")
        .distinct

  2. Using Active Record's QueryMethods#or method if you are using Rails version 5 or above. All you need to do is use or instead of the |(or) operator:

    @products = @products1.or(@products2)

Upvotes: 0

Gautam
Gautam

Reputation: 1812

You can do something like -

@products.joins(:inventory_categorizations)
         .distinct
         .where(inventory_categorizations: {inventory_category_id: @sub_category_ids, inventory_category_type: "SubCategory"})
         .or(
           @products.joins(:inventory_categorizations)
           .distinct
           .where(inventory_categorizations: {inventory_category_id: @category_ids, inventory_category_type: "Category"})
         )

Upvotes: 1

Related Questions