Reputation: 4499
Given the following models:
class Recipe < ActiveRecord::Base
has_many :recipe_ingredients
has_many :ingredients, :through => :recipe_ingredients
end
class RecipeIngredient < ActiveRecord::Base
belongs_to :recipe
belongs_to :ingredient
end
class Ingredient < ActiveRecord::Base
end
How can I perform the following SQL query using Arel in Rails 3?
SELECT * FROM recipes WHERE NOT EXISTS (
SELECT * FROM ingredients WHERE
name IN ('chocolate', 'cream') AND
NOT EXISTS (
SELECT * FROM recipe_ingredients WHERE
recipe_ingredients.recipe_id = recipes.id AND
recipe_ingredients.ingredient_id = ingredients.id))
Upvotes: 2
Views: 3951
Reputation: 1082
I am not sure how to do relational division using Arel or ActiveRecord. If it is acceptable to do two queries, this would be equivalent:
with_scope(includes(:recipes)) do
cream_recipes = Ingredient.where(:name => "cream").first.recipes
chocolate_recipes = Ingredient.where(:name => "chocolate").first.recipes
end
@recipes_with_chocolate_and_cream = cream_recipes & chocolate_recipes
Or you could just pass the SQL directly using find_by_sql.
Upvotes: 10