SELECT
*,
(SELECT COUNT(o.id) FROM tenant_versilov.orders o
LEFT OUTER JOIN tenant_versilov.shipments s ON s.order_id = o.id
WHERE o.company_id = c.id AND s.id IS NULL AND NOT o.archived) orders_count,
(SELECT COUNT(s.id) FROM tenant_versilov.shipments s
LEFT OUTER JOIN tenant_versilov.orders o ON s.order_id = o.id
WHERE o.company_id = c.id AND s.batch_id IS NULL) shipments_count
FROM tenant_versilov.companies c
WHERE c.active = TRUE;
The closest approach is to use select_merge with fragments, but fragments do not allow to interpolate strings to set schemas:
query
|> select_merge([c], %{
shipments_count:
fragment(
"(SELECT COUNT(s.id) FROM tenant_versilov.shipments s
LEFT OUTER JOIN tenant_versilov.orders o ON s.order_id = o.id
WHERE o.company_id = c0.id AND s.batch_id IS NULL)"
)
Yes, I used JOINs with subqueries, but as I need two aggregate parameters, and they both use the same tables, my JOINs somehow mix between each other and give wrong numbers.
Here is my approach:
orders_query =
from(ord in Order,
select: [:id, :company_id],
left_join: s in assoc(ord, :shipments),
where: not ord.archived and is_nil(s.id)
)
shipments_query =
from(sh in Shipment,
left_join: o2 in assoc(sh, :order),
where: is_nil(sh.batch_id),
select: %{id: sh.id, company_id: o2.company_id}
)
Company
|> filter_by_user(user)
|> only_active(true)
|> join(:left, [c], o in subquery(orders_query), on: o.company_id == c.id, as: :orders)
|> join(:left, [c], s in subquery(shipments_query), on: s.company_id == c.id, as: :shipments)
|> group_by([c], c.id)
|> select_merge([c, o, s], %{
orders_count: count(o.id),
shipments_count: count(s.id)
})
|> Repo.all(prefix: tenant)
SELECT
c.*,
orders.count,
s.count
FROM companies c
INNER JOIN (SELECT o.company_id company_id, COUNT(*) count
FROM orders o
LEFT OUTER JOIN shipments s ON s.order_id = o.id
WHERE s.id IS NULL AND NOT o.archived
GROUP BY o.company_id) orders
ON orders.company_id = c.id
-- and so on
So in Elixir it would be like:
orders_query =
from o in Order,
left_outer_join: s in assoc(o, :shipments),
where: not o.archived and is_nil(s.id),
group_by: o.company_id,
select: %{id: o.company_id, count: count()}
shipments_query =
from s in Shipment,
left_inner_join: o in assoc(sh, :order),
where: is_nil(s.batch_id),
select: %{id: o.company_id, count: count()}
from c in Company,
left_inner_join: o in subquery(orders_query), on: o.id == c.id,
left_inner_join: s in subquery(shipments_query), on: s.id == c.id,
select_merge: %{orders_count: o.count, shipments_count: s.count}