Given the models User and Invoice, a user has many invoices and an invoice belongs to a user.

Invoices have a status and amount_cents columns.

I need to write a query that gets all the User columns but also adds the following columns:

• a total_paid alias column that sums the amount_cents of all paid invoices for each User
• a total_unpaid alias column that sums the amount_cents of all unpaid invoices for each User

I'm kind of lost as to what the correct structure is when using multiple subqueries that I assign an alias to, but I've come up with something pretty basic for the first part of the task:

select users.*, (SELECT SUM(amount_cents) FROM invoices) as total_paid from users
join invoices on users.id = invoices.user_id
where invoices.status = 'paid'
group by users.id

I'm not sure if I should be writing the query from the parent or children side (I suppose from the parent (User) side since all the data I need is in the users column) but the above query seems to be returning the same amounts in the total_paid column for all the different users instead of the right amount for each user.

Any help would be appreciated.

CodePudding user response：

The statement (SELECT SUM(amount_cents) FROM invoices) returns the total amount for all the users, which is different from the amount per user that you want :

Solution with a LATERAL JOIN :

select u.*
    , paid.total as total_paid
    , unpaid.total as total_unpaid
 FROM users AS u
 LEFT JOIN LATERAL
    ( SELECT sum(amount_cents) AS total
        FROM invoices
       WHERE user_id = u.id
         AND status = 'paid'
    ) AS paid
   ON True
 LEFT JOIN LATERAL
    ( SELECT sum(amount_cents) AS total
        FROM invoices
       WHERE user_id = u.id
         AND status = 'unpaid'
    ) AS unpaid
   ON True

Solution with a JOIN & window function :

SELECT u.*
     , t.total_paid
     , t.total_unpaid
  FROM users AS u
 INNER JOIN 
(
SELECT DISTINCT ON (user_id)
     , user_id
     , sum(amount_cents) FILTER (WHERE status = 'paid') OVER (PARTITION BY user_id  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS total_paid
     , sum(amount_cents) FILTER (WHERE status = 'unpaid') OVER (PARTITION BY user_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS total_unpaid
  FROM invoices
 ORDER BY u.user_id
) AS t
ON u.id = t.user_id

CodePudding user response：

You can use scalar subqueries

select u.*, 
       (select sum(amount_cents) from invoices where user_id = u.id and status = 'paid') total_paid,
       (select sum(amount_cents) from invoices where user_id = u.id and status = 'unpaid') total_unpaid
from users u;

or a lateral join which might be a bit more efficient.

select u.*, l.*
from users u
left join lateral 
(
  select sum(amount_cents) filter (where status = 'paid') total_paid,
         sum(amount_cents) filter (where status = 'unpaid') total_unpaid
  from invoices where user_id = u.id
) l on true;

If however users.id is primary key (which is probably the case) then things can be simplified to

select u.*, 
       sum(i.amount_cents) filter (where i.status = 'paid') total_paid,
       sum(i.amount_cents) filter (where i.status = 'unpaid') total_unpaid 
from users u
left outer join invoices i on u.id = i.user_id
group by u.id;

CodePudding user response：

This can be done using subqueries as follows:

Select users.id,
       (Select Sum(amount_cents) 
        From invoices Where status = 'paid' And user_id=users.id) As total_paid,
       (Select Sum(amount_cents)
        From invoices Where status = 'unpaid' And user_id=users.id) As total_unpaid
From users
Group by users.id

CodePudding user response：

Another alternative is to use Outer Joins

  users_table = User.arel_table
  paid_invoices_table = Arel::Table.new(Invoice.arel_table.name, as: 'paid_invoices')
  unpaid_invoices_table = Arel::Table.new(Invoice.arel_table.name, as: 'unpaid_invoices')

  paid_join = Arel::Nodes::OuterJoin.new(
    paid_invoices_table,
    Arel::Nodes::On.new(
      users_table[:id].eq(paid_invoices_table[:user_id])
        .and(paid_invoices_table[:status].eq('paid'))
    )
  )

  unpaid_join = Arel::Nodes::OuterJoin.new(
    unpaid_invoices_table,
    Arel::Nodes::On.new(
      users_table[:id].eq(unpaid_invoices_table[:user_id])
        .and(unpaid_invoices_table[:status].not_eq('paid'))
    )
  )

  User.joins(paid_join,unpaid_join)
    .select(
       User.arel_table[Arel.star],
       paid_invoices_table[:amount_cents].sum.as('total_paid'), 
       unpaid_invoices_table[:amount_cents].sum.as('total_unpaid'))   
    .group(:id)

Resulting Query:

SELECT 
  users.*,
  SUM(paid_invoices.amount_cents) AS total_paid,
  SUM(unpaid_invoices.amount_cents) AS total_unpaid
FROM 
  users 
  LEFT OUTER JOIN invoices AS paid_invoices ON users.id = paid_invoices.user_id
    AND paid_invoices.status = 'paid' 
  LEFT OUTER JOIN invoices AS unpaid_invoices ON users.id = unpaid_invoices.user_id
    AND unpaid_invoices.status <> 'paid'
GROUP BY 
  users.id