ActiveRecord Joins

Random thing I missed while turning all those selects into regular queries using joins: if you use the names of associations to join tables (ex. recipes.joins(:ingredients)), it creates an INNER join. Why is this important? Here’s my example:

I have recipes with ingredients, and each ingredient might have a subingredient (gluten, soy, etc). Every recipe has ingredients, so an inner join is fine there. However, every recipe does not have subingredients. Since I was doing a recipes.joins(ingredients: :subingredients), I was only getting recipes that did not have subingredients... definitely not what I wanted. Here’s the code:

BEFORE:
Code

Recipe.joins(ingredients: :subingredients).joins(:ingredients)

Resulting SQL

SELECT "recipes".* FROM "recipes" INNER JOIN "ingredient_measurements" ON "ingredient_measurements"."recipe_id" = "recipes"."id" INNER JOIN "ingredients" ON "ingredients"."id" = "ingredient_measurements"."ingredient_id" INNER JOIN "ingredients_subingredients" ON "ingredients_subingredients"."ingredient_id" = "ingredients"."id" INNER JOIN "subingredients" ON "subingredients"."id" = "ingredients_subingredients"."subingredient_id"

AFTER:
Code

Recipe.joins(:ingredients).joins('LEFT JOIN "ingredients_subingredients" ON "ingredients_subingredients"."ingredient_id" = "ingredients"."id" LEFT JOIN "subingredients" ON "subingredients"."id" = "ingredients_subingredients"."subingredient_id"')

Resulting SQL

SELECT "recipes".* FROM "recipes" INNER JOIN "ingredient_measurements" ON "ingredient_measurements"."recipe_id" = "recipes"."id" INNER JOIN "ingredients" ON "ingredients"."id" = "ingredient_measurements"."ingredient_id" LEFT JOIN "ingredients_subingredients" ON "ingredients_subingredients"."ingredient_id" = "ingredients"."id" LEFT JOIN "subingredients" ON "subingredients"."id" = "ingredients_subingredients"."subingredient_id"

UPDATE: As Darren (manvsmachine) mentions, you can also use .eager_load to create LEFT OUTER joins. However, that does give you a different query. It seems to have the same-ish results, but the SQL output is slightly different. It looks something like this:

Code

Recipe.eager_load(:subingredients)

Resulting SQL

SELECT "recipes"."id" AS t0_r0, "recipes"."name" AS t0_r1, "recipes"."instructions" AS t0_r2, ... "recipes"."image" AS t0_r14, "subingredients"."id" AS t1_r0, "subingredients"."name" AS t1_r1, "subingredients"."created_at" AS t1_r2, "subingredients"."updated_at" AS t1_r3 FROM "recipes" LEFT OUTER JOIN "ingredient_measurements" ON "ingredient_measurements"."recipe_id" = "recipes"."id" LEFT OUTER JOIN "ingredients" ON "ingredients"."id" = "ingredient_measurements"."ingredient_id" LEFT OUTER JOIN "ingredients_subingredients" ON "ingredients_subingredients"."ingredient_id" = "ingredients"."id" LEFT OUTER JOIN "subingredients" ON "subingredients"."id" = "ingredients_subingredients"."subingredient_id"