Question 89407 in PostgreSQL Question posted in
The official documentation can be found here.

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

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!

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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.


  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search