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?

+3


source to share


2 answers


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.

+2


source


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)

      

+2


source







All Articles