Is there a better way to execute this SQL query?

Question

I have written this SQL query to get data for each customer in my database.

As you can see, I'm trying to get the total of unpaid orders and the total of orders to my query.

My goal at the end is to get only the users with unpaids orders (I think i will have to make it with a HAVING at the end of the query).

Here is my query

SELECT 
u.id, 
u.first_name, 
u.last_name, 
u.email, 
u.phone, 
(SELECT COUNT(*) FROM account_order WHERE account_order.user_id = u.id AND account_order.is_paid = False AND account_order.max_meals > 0) as total_not_paid,
(SELECT COUNT(*) FROM account_order WHERE account_order.user_id = u.id) AS total_orders
FROM account_user u

Do you believe there is a better way to get the records ?

How can I get the users with only one total_not_paid and only one total_order ?

Answer 1

If you want unpaid orders, you can use explicit aggregation and a having clause:

SELECT u.*,
       COUNT(*) FILTER (WHERE NOT is_paid AND ao.max_meals > 0) as total_not_paid,
       COUNT(*) AS total_orders
FROM account_user u JOIN
     account_order ao
     ON ao.user_id = u.id
GROUP BY u.id
HAVING COUNT(*) FILTER (WHERE NOT is_paid AND ao.max_meals > 0) > 0;