Ecto not returning the expected results from query

Questions / Help
,
1

Hi everyone,

Can you please help me out here.

I have two tables: payment_orders and invoices.
Relation: payment_orders has many invoices.
I am trying to: get all the payment orders and sum the total amount of invoices.

I have a simple query in ecto like this:

q1 =
      from(
        p in Epr.Payments.PaymentOrder,
        left_join: i in Epr.Debts.Invoice,
        where: p.id == i.payment_order_id,
        group_by: p.id,
        select: %{
          id: p.id,
          void: p.void,
          num: p.num,
          invoice_amount: sum(i.amount)
        }
      )

Inside IEx, this query returns:

iex(12)> Enum.map(list, fn p -> {p.id, p.num, p.void, p.invoice_amount} end)
[
  {2, 2, false, #Decimal<10000.00>},
  {8, 5, false, #Decimal<1000>},
  {10, 6, false, #Decimal<2000.00>},
  {11, 7, false, #Decimal<1200.00>},
  {12, 8, false, #Decimal<2500.00>},
  {14, 10, false, #Decimal<3333.00>},
  {15, 11, false, #Decimal<2000>},
  {16, 12, false, #Decimal<8888.00>}
]

In psql command line I have the following query:

select p.id, p.num, p.void, sum(i.amount) from payment_orders as p left join invoices as i on p.id = i.payment_order_id group by p.id;

When I execute this query in psql command it returns as expected:

 id | num | void |   sum    
----+-----+------+----------
 10 |   6 | f    |  2000.00
  6 |   3 | t    |         
 14 |  10 | f    |  3333.00
 13 |   9 | t    |         
  2 |   2 | f    | 10000.00
 16 |  12 | f    |  8888.00
 11 |   7 | f    |  1200.00
  7 |   4 | t    |         
 15 |  11 | f    |     2000
 12 |   8 | f    |  2500.00
  1 |   1 | t    |         
  8 |   5 | f    |     1000
(12 rows)

epr_dev=#

Please notice:

  1. The amount of row retuned (8 vs 12)
  2. Some rows in psql result don’t have any values in sum column

My first question is: are this two queries equivalent?
If so: why am I getting different results?

Any ideas?

Thanks

2

To answer your first question, use to_sql/3.

2 Likes
3

I translate your SQL as:

from(p in Epr.Payments.PaymentOrder, [
  left_join: i in Epr.Debts.Invoice, on: p.id == i.payment_order_id,
  group_by: p.id,
  select: %{
    id: p.id,
    void: p.void,
    num: p.num,
    invoice_amount: sum(i.amount)
  }
])

you are using a where instead of fully specifying the left join - this means you actually have an inner join rather than a left join.

Also running the query in iEX would show you the SQL that Ecto generates (example).

3 Likes