Rails/Postgresql query to get all recipes where recipe ingredients contains all ingredient ids from array

Question

I know there are a few other similar questions out there, but I cannot seem to make this query work.

I have a Rails 7 app with Recipe and Ingredient models joined through a Portions table (see models below).

Given an array of ingredient ids, I need to be able to get all of the recipes that contain all of the ingredients with those ids. (Not just where any id matches).

The models and schema I'm working with are identical to an app I previously built. The primary difference is that this is an API-only Rails 7 app vs. the standard Rails 7 build. Both use Postgresql as the db. In the earlier version, I was able to make this work using the query below.

  def search_all_recipes(params)
    # formats ingredient id array for postgresql
    ingredient_ids = '{' + params[:ingredientIds].join(', ') + '}' if params[:ingredientIds]

    # should return all recipes whose ingredient list contains all ingredient_ids
    recipes = Recipe.joins(:ingredients)
                .group(:id)
                .having('array_agg(ingredients.id) @> ?', ingredient_ids)

    return recipes
  end

However, I am now getting this error message:

ActiveRecord::StatementInvalid (PG::GroupingError: ERROR:  column "ingredients.id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: ...ed_at" AS t0_r4, "recipes"."updated_at" AS t0_r5, "ingredien...
                                                             ^
):

If I add ingredients.id to the group clause, I only get one of the ingredients in the result, rather than all of the associated ingredients. I'm also using ingredients.id in an aggregate function in the having clause so I'm confused by the error message.

My expectation is that the SQL output in the log would look like this, as it does in the previous version of the app:

Recipe Load (34.6ms)

 
SELECT "recipes".* 
FROM "recipes" 
INNER JOIN "portions" ON "portions"."portionable_type" = $1 AND "portions"."portionable_id" = "recipes"."id" 
INNER JOIN "ingredients" ON "ingredients"."id" = "portions"."ingredient_id" 
GROUP BY "recipes"."id" 
HAVING (array_agg(ingredients.id) @> '{103}') 
ORDER BY "recipes"."name" ASC LIMIT $2 OFFSET $3  [["portionable_type", "Recipe"], ["LIMIT", 25], ["OFFSET", 0]]

Instead, I get this SQL output that does not seem (to me) to be making the same query, but SQL is not my forte and maybe this is what Rails is actually doing under the hood with the above query.:

SQL (1.0ms)

SELECT "recipes"."id" AS t0_r0, "recipes"."description" AS t0_r1, "recipes"."name" AS t0_r2, "recipes"."slug" AS t0_r3, "recipes"."created_at" AS t0_r4, "recipes"."updated_at" AS t0_r5
, "ingredients"."id" AS t1_r0, "ingredients"."type" AS t1_r1, "ingredients"."name" AS t1_r2, "ingredients"."sub_type" AS t1_r3, "ingredients"."brand" AS t1_r4, "ingredients"."product" AS t1_r5, "ingredients"."abv" AS t1_r6, "ingredients"."age" AS t1_r7, "ingredients"."created_at" AS t1_r8, "ingredients"."updated_at" AS t1_r9
, "categories"."id" AS t2_r0, "categories"."name" AS t2_r1, "categories"."created_at" AS t2_r2, "categories"."updated_at" AS t2_r3
, "steps"."id" AS t3_r0, "steps"."name" AS t3_r1, "steps"."description" AS t3_r2, "steps"."recipe_id" AS t3_r3, "steps"."created_at" AS t3_r4, "steps"."updated_at" AS t3_r5
, "tools"."id" AS t4_r0, "tools"."tool_type" AS t4_r1, "tools"."tool_brand" AS t4_r2, "tools"."created_at" AS t4_r3, "tools"."updated_at" AS t4_r4
, "users"."id" AS t5_r0, "users"."email" AS t5_r1, "users"."encrypted_password" AS t5_r2, "users"."reset_password_token" AS t5_r3, "users"."reset_password_sent_at" AS t5_r4, "users"."remember_created_at" AS t5_r5, "users"."default_cabinet_id" AS t5_r6, "users"."created_at" AS t5_r7, "users"."updated_at" AS t5_r8 
FROM "recipes" 
INNER JOIN "portions" ON "portions"."portionable_type" = $1 AND "portions"."portionable_id" = "recipes"."id" 
INNER JOIN "ingredients" ON "ingredients"."id" = "portions"."ingredient_id" 
LEFT OUTER JOIN "categories_recipes" ON "categories_recipes"."recipe_id" = "recipes"."id" 
LEFT OUTER JOIN "categories" ON "categories"."id" = "categories_recipes"."category_id" LEFT OUTER JOIN "steps" ON "steps"."recipe_id" = "recipes"."id" 
LEFT OUTER JOIN "recipes_tools" ON "recipes_tools"."recipe_id" = "recipes"."id" 
LEFT OUTER JOIN "tools" ON "tools"."id" = "recipes_tools"."tool_id" 
LEFT OUTER JOIN "recipes_users" ON "recipes_users"."recipe_id" = "recipes"."id" 
LEFT OUTER JOIN "users" ON "users"."id" = "recipes_users"."user_id" 
GROUP BY "recipes"."id" 
HAVING (array_agg(ingredients.id) @> '{105}') 
ORDER BY "recipes"."name" ASC LIMIT $2 OFFSET $3

EDIT: Please see my Recipe, Ingredient and Portion models below:

class Recipe < ApplicationRecord
  before_validation :generate_slug

  include Portionable
  
  has_and_belongs_to_many :users
  has_and_belongs_to_many :tools
  has_and_belongs_to_many :categories
  has_many :ingredients, through: :portions
  has_many :steps, dependent: :destroy
  has_many :favorite_recipes
  has_many :favorited_by, through: :favorite_recipes, source: :user

  accepts_nested_attributes_for :steps, allow_destroy: true
  accepts_nested_attributes_for :categories_recipes, allow_destroy: false
  accepts_nested_attributes_for :portions, allow_destroy: true, reject_if: proc { |att| att['ingredient_id'].blank? }

  validates :name, presence: true
  validates :slug, presence: true

  # ordering scopes
  scope :alphabetical, -> { includes(:ingredients, :categories, :steps, :tools, :users).order(:name) }

  # filtering scopes
  scope :search, lambda{ |search_term| self.where('recipes.name ILIKE ?', "%#{ search_term }%").distinct if search_term.present? }
  scope :by_category, lambda{ |category_ids| self.joins(:categories).where(categories: { id: category_ids }) if category_ids.present? }

  scope :by_any_ingredient, lambda{ |ingredient_ids| self.joins(:ingredients).where(ingredients: { id: ingredient_ids }).distinct }
  scope :user_has_all_ingredients, lambda{ |user_ingredients| self.joins(:ingredients).group(:id).having('array_agg(ingredients.id) <@ ?', user_ingredients) if user_ingredients.present? }

  

  def to_param
    slug
  end

  def generate_slug
    self.slug ||= name.parameterize
  end

  def self.search_all_recipes(params)
    # formats ingredient id array for postgresql
    ingredient_ids = '{' + params[:ingredientIds].join(', ') + '}' if params[:ingredientIds]

    # should return all recipes whose ingredient list contains all ingredient_ids
    recipes = Recipe.joins(:ingredients)
                .group(:id)
                .having('array_agg(ingredients.id) @> ?', ingredient_ids)

    return recipes
  end
end

class Ingredient < ApplicationRecord
  has_many :portions
  has_many :recipes, through: :portions, source: :portionable, source_type: 'Recipe'
  has_many :cabinets, through: :portions, source: :portionable, source_type: 'Cabinet'
  validates :name, presence: true
  validates :name, uniqueness: true
  validates :sub_type, presence: true
end

class Portion < ApplicationRecord
  belongs_to :portionable, :polymorphic => true
  belongs_to :ingredient
  validates :ingredient_id, presence: true
  validates :portionable_type, presence: true
end

module Portionable
  extend ActiveSupport::Concern

  included do
    has_many :portions, :as => :portionable
  end
end

EDIT:

Here are a few more query formations that I've tried. Similar to my query above, they all come back to the PG::GroupingError: ERROR: column "ingredients.id" must appear in the GROUP BY clause or used in an aggregate function. I can't add "ingredients.id" to the Group clause as it limits the returned ingredients to one.

So if I pass id 105 for Bourbon, I will get all the recipes with Bourbon, but Bourbon will be the only ingredient sent through, even if there are others in the recipe.

   recipes = Recipe.joins(:ingredients)
                .where(ingredients: { id: ingredient_ids })
                .group('recipes.id')
                .having(
                  'count(*) = ?', ingredient_ids.length
                )

    recipes = Recipe.joins(:ingredients)
                .group(:id)
                .where(ingredients: { id: ingredient_ids })
                .having(
                  Ingredient.arel_table[Arel.star].count.gteq(ingredient_ids.length)
                )

    recipes = Recipe.joins(:ingredients)
                .where('ingredients.id IN (?)', params[:ingredientIds])
                .group('recipes.id')
                .having(
                  'COUNT(ingredients.id) >= ?', params[:ingredientIds].length
                )

Thanks to everyone who's taken the time to help out so far. I really appreciate it!

You say "in the past I was able to make this query work..", do you mean it actually worked? Because then you say "...similar queries in the past". So it's unclear if something broke or never worked in the first place. Also are we to assume that relations don't matter since you don't show any? And finally what exactly is the "mess"? All the joins? all the "AS t0_r1"? — Beartech, Mar 14 '23 at 04:17
ARRAY_AGG isn't what you want here. Instead you want something like `Recipe.joins(:ingredients).where(ingredients: { id: ingredient_ids }).having(Ingredient.arel_table[Arel.star].count.gte(ingredient_ids.length))`. Why I think this code is breaking through is that your calling `.eager_load` or `.includes` on the relation which tries to load the associated tables in a single query. That won't work when a grouping is applied. https://stackoverflow.com/questions/36131803/sql-where-joined-set-must-contain-all-values-but-may-contain-more — max, Mar 14 '23 at 07:07
If you need to do that you need to use a different approach such as a subquery or lateral join. I agree with beartech here that we need to see your models and enough context to see whats going on here. — max, Mar 14 '23 at 07:10
https://stackoverflow.com/questions/48990859/ruby-on-rails-5-activerecord-query-where-model-association-ids-includes-all-ids https://stackoverflow.com/questions/33731332/rails-scope-where-in-exact-matches https://stackoverflow.com/questions/54750813/how-to-chain-mutiples-in-and-on-where-in-sql https://stackoverflow.com/questions/63336722/activerecord-search-for-multiple-ids-in-association-with-and-condition — engineersmnky, Mar 14 '23 at 13:02
Thanks @BearTech! I clarified relevant parts of my question above and added my models as well as additional queries that I've tried based on responses here. — baconsocrispy, Mar 14 '23 at 18:05

score 2 · Answer 1 · answered Mar 14 '23 at 15:19

2

The simple way to do this is to just add a where clause and use a count per group:

class Recipe
  def self.with_ingredients(*ingredient_ids)
    joins(:ingredients)
       .group(:id)
       .where(ingredients: { id: ingredient_ids })
       .having(
         Ingredient.arel_table[Arel.star].count.gte(ingredient_ids.length)
       )
  end 
end

If you can't apply a grouping (for example if you're using eager_load) you can do a subquery:

Recipe.where(
  id: Recipe.with_ingredients(*params[:recipe_ids]).select(:id)
).eager_load(:ingredients)

Thats avoids the error that would occur if you're selecting columns off the joined that are not used in the group.

answered Mar 14 '23 at 15:19

max

96,212
14
104
165

Thanks @max! I made edits based on your response and BearTech's above. I've tried each of the proposed solutions, yet continue to come back to the PG::Grouping Error with each one. Given that these all appear to be accepted solutions to this problem, I'm confounded why I'm getting this error. I sense the problem must lie outside the query somewhere. – baconsocrispy Mar 14 '23 at 18:04
What happens if you do `Recipe.unscoped.with_ingredients(1, 2, 3)`? Could it be something like a default scope? – max Mar 14 '23 at 20:27
Yes. Thank you @max for all your help with this. I was calling the .includes method in an alphabetical scope that was getting called in my controller. It was eager loading a bunch of other tables and throwing off the search query. A simple fix and now everything is working fine. Thanks again. – baconsocrispy Mar 14 '23 at 20:57

score 0 · Answer 2 · answered Mar 14 '23 at 20:54

I discovered the issue I was having here. In case anyone else finds themselves in this situation, here's what I was doing to cause the issue.

I had an :alphabetical scope in my Recipe model that looked like this:

 scope :alphabetical, -> { includes(:ingredients, :categories, :steps, :tools, :users).order(:name) }

I was calling this method in my recipes_controller. It was eager loading the ingredients, categories, steps, tables, etc..., which was adding LEFT OUTER JOINS to the SQL query and making it so that the GROUP BY clause couldn't condense the records by Recipe ids alone.

I don't remember when or why I added the includes method into the alphabetical scope and it isn't in my earlier version of the app that worked, so by resetting the alphabetical scope to simply:

 scope :alphabetical, -> { order(:name) }

I was able to fix the PG:GroupingError and now all of the suggested methods for querying the inclusion of all ids in the array work.

What happens here is you use `.includes` referencing the associations through `.where` or the `.references` method will cause it to delegate to `.eager_load` which creates a single database query and loads everything off the "main" table as well as the joined tables. Thats why you get those messy `t2_r2, "categories"."updated_at" AS t2_r3 , "steps"."id" AS t3_r0, "steps"."name" AS t3_r1, "steps"."description" AS t3_r2` aliases. — max, Mar 14 '23 at 21:02
That breaks when you have a group applied in Postgres as it won't let you fetch non-aggregated columns off the joined tables that are not part of the group clause as it would give ambigous results. — max, Mar 14 '23 at 21:05

Rails/Postgresql query to get all recipes where recipe ingredients contains all ingredient ids from array

2 Answers2