How to do joins and grouping in Ecto

Phoenix Forum Questions / Help
, ,
1

I know this can be achieved using raw SQL and Ecto.Adapters.SQL.query() but what is the Ecto way to it? Any help is highly appreciated, thank you.

SELECT COUNT(*) AS count, products.name AS product, categories.name AS category, stores.name AS store FROM "items"
  INNER JOIN "stores" ON "stores"."id" = "items"."store_id"
  INNER JOIN "products" ON "products"."id" = "items"."product_id"
  INNER JOIN "categories" ON "categories"."id" = "products"."category_id"
  WHERE "items"."store_id" IN (#{store_ids}) AND "items"."product_id"
		IN (#{product_ids}) AND (products.category_id IN (#{category_ids}))
  GROUP BY products.name, categories.name, stores.name
  ORDER BY products.name
2

Let’s assume you’ve got ecto schemas with associations setup:

from i in Item,
  join: s in assoc(i, :store),
  join: p in assoc(, :product),
  join: c in assoc(p, :category),
  where: i.store_id in ^store_ids and i.product_id in ^product_ids,
  where: p.category_id in ^category_ids,
  group_by: {p.name, c.name, s.name},
  order_by: p.name,
  select: %{count: count(p.name), product: p.name, category: c.name, store: s.name}

It’s basically an exact translation. The only difference is that I think Ecto makes you more explicit about what you’re counting.

4 Likes