Using Arel for ROR I am trying to query based on my Classes associated attributes

Question

I have a Recipe class and an Ingredient class which are connected with a has_many to a has_many through a join table RecipeIngredients. I am trying to create some filters and am trying to sort my Recipes by the number of Ingredients they contain. I cannot figure out the proper SQL, I am also trying to use Arel to find my answer. But I will take any proper way to query for this at this point. In reverse, I will also be trying to Query the Ingredients got how many Recipes they are in.

Thank you in advance for any help anyone can offer, I am having trouble with my queries and completely ran out of all thoughts for tonight. Thanks.

score 1 · Answer 1 · answered Oct 01 '17 at 11:47

I'd consider using Arel for this kind of problem to be overly complicated. ActiveRecord itself, which is a layer above Arel can solve this quite comfortably.

I assume you have the following models

class Recipe
  has_many :recipe_ingredients
  ...
end

class RecipeIngredient
  has_one: :recipe
  has_one: :ingredient
  ...
end

class Ingredient
  has_many :recipe_ingredients
  ...
end

In order to get the recipes ordered by the number of ingredients you'll have to generate the following SQL statement:

SELECT 
  recipes.id
  ... 
  , recipes.[last_column_name]
  # optionally
  , COUNT(*) ingredients_count        
FROM
  recipes
OUTER JOIN
  recipe_ingredients
  ON
    recipe_ingredients.recipe_id = recipe.id
GROUP BY
  ingredient_count DESC

which can be done by

Recipe
  .joins(:recipe_ingredients)
  .group(Recipe.column_names)
  .select(Recipe.column_names, 'COUNT(*) ingredients_count')
  .order('ingredients_count DESC') # Or ASC

The Recipe instances returned will be sorted by the number of ingredients. They will also have an additional method ingredients_count which returns the number of ingredients.

This could also be placed into a scope inside the Recipe class.

def self.ordered_by_ingredients_count
  joins(:recipe_ingredients)
  .group(column_names)
  .select(column_names, 'COUNT(*) ingredients_count')
  .order('ingredients_count DESC') # Or ASC
end

For the reverse, number of recipes an ingredient is in, just swap some names:

Ingredient
  .joins(:recipe_ingredients)
  .group(Ingredient.column_names)
  .select(Ingredient.column_names, 'COUNT(*) recipe_count')
  .order('recipe_count DESC') # Or ASC

Using Arel for ROR I am trying to query based on my Classes associated attributes

1 Answers1