CODEWITHSUNDEEP
ruby-on-railsactiverecord
pfrasdog
pfrasdog

Reputation: 13

How to use AND condition in an ActiveRecord query

I currently have these three tables:

create_table "cocktail_ingredients", force: :cascade do |t|
t.integer "cocktail_id"
t.integer "ingredient_id"
t.datetime "created_at", null: false
t.datetime "updated_at", null: false
t.index ["cocktail_id"], name: "index_cocktail_ingredients_on_cocktail_id"
t.index ["ingredient_id"], name: "index_cocktail_ingredients_on_ingredient_id"


create_table "cocktails", force: :cascade do |t|
t.string "title"
t.string "ingredients"
t.text "method"
t.datetime "created_at", null: false
t.datetime "updated_at", null: false

create_table "ingredients", force: :cascade do |t|
t.string "name"
t.datetime "created_at", null: false
t.datetime "updated_at", null: false

I'm trying to do a seach that returns all the cocktails that contain ALL of the ingredients in my search. So far i've managed to come up with this:

CocktailIngredient.where(ingredient_id: [1, 4]).map(&:cocktail).uniq

Which returns all the cocktails that include the ingredient_id of 1 or 4. I am trying to return only the cocktails that contain BOTH ingredients with the id 1 AND 4.

Any help would be much appreciated.

Upvotes: 1

Views: 95

Answers (2)

ArnoHolo
ArnoHolo

Reputation: 317

Here is a generalised version, if you have an ingredient_ids array:

cocktail_ingredients = [1, 4]
cocktail_ingredients = CocktailIngredient.where(ingredient_id: ingredient_ids).select(:cocktail_id)
cocktail_ingredients = cocktail_ingredients.group(:cocktail_id).having('COUNT(ingredient_id) >= ?', ingredient_ids.count)
cocktails = Cocktail.where(id: cocktail_ingredients.select(:cocktail_id))

Here is a tested scope for your Cocktail model (with Rspec and FactoryGirl). You'll just have to call Cocktail.with_ingredients([1,4]).

In cocktail.rb:

scope :with_ingredients, (lambda do |ingredient_ids|
   cocktail_ingredients = CocktailIngredient.where(ingredient_id: ingredient_ids).select(:cocktail_id)
   cocktail_ingredients = cocktail_ingredients.group(:cocktail_id).having('COUNT(ingredient_id) >= ?', ingredient_ids.count)
   where(id: cocktail_ingredients.select(:cocktail_id))
end)

And cocktail_spec.rb: https://gist.github.com/ArnoHolo/54b9259fbaa067d7abbf04a73d94ec40

Upvotes: 1

AntonTkachov
AntonTkachov

Reputation: 1784

The best I found for now is:

Cocktail.joins(:cocktail_ingredients).where(cocktail_ingredients: { ingredient_id: 1, cocktail_id: CocktailIngredient.where(ingredient_id: 4).select(:cocktail_id) })

I don't think it's the best we can do, but it's MUCH MORE optimized then previous solution

Upvotes: 1

Related Questions