CODEWITHSUNDEEP
ruby-on-railsruby-on-rails-3arel
Bryan Ash
Bryan Ash

Reputation: 4499

Rails 3 equivalent of complex SQL query

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

Answers (1)

Awgy
Awgy

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

Related Questions