Improving postgres subquery using coalesce
Picture this: two tables, cases and case_messages. The case can be any number of messages or not. One of these messages may be the final answer in the event. Now I want to return a list of cases with a bool column that tells me if there is a definitive answer or not. If there are no messages at all, the request should return "false". Only if there is at least one related message in case_messages, where isfinalanswer = true, if the request returns true. I managed to get this working with coalesce and a sub-query, but it looks super ugly:
SELECT cases.id, cases.title,
COALESCE((SELECT isfinalanswer FROM case_messages WHERE caseid = cases.id ORDER BY isfinalanswer DESC LIMIT 1), false) AS hasfinalanswer,
FROM cases;
How can I improve this?
source to share
I would recommend using exists
:
SELECT c.id, c.title,
(exists (SELECT 1 FROM case_messages cm WHERE cm.caseid = c.id and isfinalanswer = true)
) as hasfinalanswer
FROM cases c;
I don't know if you would think it is less ugly.
I must add that for performance (which is not mentioned in the question) you need an index on case_messages(caseid, isfinalanswer)
. With an index like this, this is likely to be the most efficient solution.
source to share
A correlated subquery (a query in a select list) can be troublesome in performance. Make left join
anddistinct on
select distinct on (c.id)
c.id, c.title,
coalesce(cm.isfinalanswer, false) as hasfinalanswer
from
cases c
left join
case_messages cm on cm.caseid = c.id
order by 1, 3 desc
In this case, coalesce
you can replace withis true
cm.isfinalanswer is true as hasfinalanswer
Edit: To avoid the cost order by
required distinct on
, do pre-aggregation
select id, title, coalesce(isfinalanswer, false) as hasfinalanswer
from
cases c
left join (
select caseid as id, bool_or(isfinalanswer) as isfinalanswer
from case_messages
group by 1
) cm using (id)
source to share