reusing subquery in postgresql

Question

I have a query which works just fine, however I use the same subquery twice in it. I would like to know if there is a way to reuse this subquery. Here is the query:

SELECT DISTINCT homeworks.*
FROM homeworks
INNER JOIN homework_messages ON homeworks.id = homework_messages.homework_id
WHERE homework_messages.message_type = 'submit'
  AND homework_messages.created_at::date <= (CURRENT_DATE - '5 days'::interval)
  AND (
    (
      SELECT MAX(homework_messages.created_at) FROM homework_messages
      WHERE homework_messages.homework_id = homeworks.id AND homework_messages.message_type != 'submit'
    ) < homework_messages.created_at 
    OR 
    (
      SELECT MAX(homework_messages.created_at) FROM homework_messages
      WHERE homework_messages.homework_id = homeworks.id AND homework_messages.message_type != 'submit'
    ) IS NULL
  )
GROUP BY homeworks.id

As you can see I repeat these lines twice:

(
  SELECT MAX(homework_messages.created_at) FROM homework_messages
  WHERE homework_messages.homework_id = homeworks.id AND homework_messages.message_type != 'submit'
)

I tried to use WITH clause and it seems it doesn't suit this problem, or maybe I'm using it wrong. Anyway thanks for you help.

If you check the execution plan I would bet the statement is only actually executed once; consider using it in a lateral join — Stu
– Stu, Commented Dec 23, 2021 at 21:09
Unrelated, but the distinct as uses is a code smell. You can probably get rid of that, by turning that inner join into an exists condition. — user330315
– user330315, Commented Dec 23, 2021 at 21:27
homeworks.id is likely the primary key, otherwise PG would generate a functional dependence error. That also suggests DISTINCT is not required due to GROUP BY homeworks.id. — Jon Armstrong
– Jon Armstrong, Commented Dec 23, 2021 at 22:13

Stefanov.sm · Accepted Answer · 2021-12-23 21:32:07Z

1

Here is a cross join lateral suggestion. I have not delved into your business logic and just tried to keep it equivalent.

SELECT DISTINCT homeworks.*
FROM homeworks
INNER JOIN homework_messages ON homeworks.id = homework_messages.homework_id
cross join lateral ( -- your subquery follows
  SELECT MAX(created_at) as created_at FROM homework_messages
  WHERE homework_id = homeworks.id AND message_type != 'submit'
) as lat
WHERE homework_messages.message_type = 'submit'
  AND homework_messages.created_at::date <= (CURRENT_DATE - '5 days'::interval)
  AND (lat.created_at < homework_messages.created_at OR lat.created_at IS NULL)
GROUP BY homeworks.id;

edited Dec 23, 2021 at 21:32

answered Dec 23, 2021 at 21:17

Stefanov.sm

13.2k2 gold badges24 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

reusing subquery in postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related