How to join these two queries in Ecto?

Phoenix Forum Questions / Help
,
1

I have this query:

    last_count = 
  Stock.Moves.Move
    |> join(:left, [move], item in Stock.Items.Item, move.item_id == item.id)
    |> select([move, item], %{
      item_id: max(move.item_id),
      docdate: max(move.docdate)
      })
      |> where([move, item], move.class == 7)
      |> group_by([move, item], item.effective_item_id)

And this:

    moves =
      from(e in subquery(moves),
        select: %{
          name_he: e.name_he,
          item_id: e.item_id,
          item_unit: e.item_unit,
          quantity: e.quantity,
          flag: e.flag,
          status: e.status
        },
        order_by: e.name_he
      )

How to join these two queries on A.item_id == B.item_id in Ecto and return the result set?

2

Is that possible?

3

How are you wanting to join them, like what are you wanting returned and all?

1 Like
4

I want basically to add docdate from the first query result to the second set of results matched by item_id. Both queries return data grouped by item_id.

5

I’m still unsure on what you are asking… ^.^;

What’s the SQL of what you are trying to accomplish? I can convert that to Ecto.

1 Like
6

Here is the working SQL:

SELECT t2.docdate,
       s0."name_he",
       s0."item_id" AS item_id,
       s0."item_unit",
       s0."quantity",
       s0."flag",
       s0."status"
FROM
  (SELECT max(i1."name_he") AS "name_he",
          i1."effective_item_id" AS "item_id",
          max(i1."unit") AS "item_unit",
          sum(m0."quantity") AS "quantity",
          max(i1."flag") AS "flag",
          max(i1."status") AS "status"
   FROM "moves" AS m0
   LEFT OUTER JOIN "items" AS i1 ON m0."item_id" = i1."id"
   WHERE (m0."stock_count" = 1)
     AND (i1."status" = 1)
     AND (m0."flag" = 1)
     AND (m0."inserted_at" >= '2017-10-01 00:00:00')
   GROUP BY i1."effective_item_id",
            i1."name_he"
   ORDER BY i1."name_he") AS s0
LEFT JOIN
  (SELECT m1.item_id AS item_id,
          max(docdate) AS docdate
   FROM moves m1
   WHERE m1.class = 7
     AND flag = 1
     AND stock_count = 1
   GROUP BY m1."item_id") t2 ON (s0.item_id = t2.item_id)
ORDER BY s0."name_he";

And here are the Ecto queries to be joined:


    last_count = # A. To be joined with B on item_id
      Stock.Moves.Move
      |> join(:left, [move], item in Stock.Items.Item, move.item_id == item.id)
      |> select([move, item], %{
        item_id: max(move.item_id),
        docdate: max(move.docdate)
      })
      |> where([move, item], move.stock_count == 1)
      |> where([move, item], item.status == 1)
      |> where([move, item], move.flag == 1)
      |> where([move, item], move.class == 7)
      |> group_by([move, item], item.effective_item_id)

    moves =
      Stock.Moves.Move
      |> join(:left, [move], item in Stock.Items.Item, move.item_id == item.id)
      |> select([move, item], %{
        name_he: max(item.name_he),
        item_id: item.effective_item_id,
        item_unit: max(item.unit),
        quantity: sum(move.quantity),
        flag: max(item.flag),
        status: max(item.status)
      })
      |> where([move, item], move.stock_count == 1)
      |> where([move, item], item.status == 1)
      |> where([move, item], move.flag == 1)
      |> group_by([move, item], item.effective_item_id)
      |> group_by([move, item], item.name_he)
      |> StockWeb.Filters.Stock.build_query(conn, _params)
      |> order_by([move, item], asc: item.name_he)
      
    moves = # B to be joined with A
      from(e in subquery(moves),
        select: %{
          name_he: e.name_he,
          item_id: e.item_id,
          item_unit: e.item_unit,
          quantity: e.quantity,
          flag: e.flag,
          status: e.status
        },
        order_by: e.name_he
      )
      |> Repo.all()
7

Solved like this:

...
    moves =
      from(e in subquery(moves),
       join: lca in subquery(last_count_at),
           on:
             lca.item_id == e.item_id,
        select: %{
          name_he: e.name_he,
          item_id: e.item_id,
          item_unit: e.item_unit,
          quantity: e.quantity,
          flag: e.flag,
          status: e.status,
          last_count_at: lca.docdate
        },
        order_by: e.name_he
      )
      |> Repo.all()
1 Like