Sub-selects in ecto queries

versilov

How can I write this query in Ecto?

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)"
      )

4 comments

#ecto #postgres #ecto-query

0 4308 4

2020-11-22 01:02:40 UTC

Showing Posts 1 to 4

Show Best Posts
Show All (oldest first)
Show All (newest first)

hauleth

You can just use JOINs. I am on a mobile right now, so I will not rewrite that query for you, but I believe that you can do it on your own.

Post #1

versilov

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)

Post #2

hauleth

What you probably want is something like:

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}

Post #3

versilov

With minor modifications it worked, thanks!
Had to add group_by and coalesce to deal with nil count values.
Here is the final working query:

   orders_query =
      from(o in Order,
        left_join: s in assoc(o, :shipments),
        where: not o.archived and is_nil(s.id),
        group_by: o.company_id,
        select: %{company_id: o.company_id, count: count()}
      )

    shipments_query =
      from(s in Shipment,
        inner_join: o in assoc(s, :order),
        where: is_nil(s.batch_id),
        group_by: o.company_id,
        select: %{company_id: o.company_id, count: count()}
      )

    Company
    |> filter_by_user(user)
    |> only_active(true)
    |> join(:left, [c], o in subquery(orders_query), on: o.company_id == c.id)
    |> join(:left, [c], s in subquery(shipments_query), on: s.company_id == c.id)
    |> group_by([c, o, s], [c.id, o.count, s.count])
    |> select_merge([c, o, s], %{
      orders_count: coalesce(o.count, 0),
      shipments_count: coalesce(s.count, 0)
    })
    |> Repo.all(prefix: tenant)

Post #4

— All posts loaded —