Search code examples
sqlpostgresqlselectsql-order-bystring-agg

In postgres, how to sort on, among others, a result value of a string_agg function, without selecting that field? Subquery not possible

I have this query (already stripped down but more complex in reality):

SELECT
    e.id,
    string_agg(csb.description, ',' ORDER BY cs.id ASC) AS condition_set_descriptions
FROM event e
JOIN event_trigger_version etv ON e.event_trigger_version_id = etv.id
LEFT JOIN event_condition_set ecs ON ecs.event_id = e.id
JOIN condition_set cs ON cs.id = ecs.condition_set_id
JOIN condition_set_base csb ON cs.condition_set_base_id = csb.id
JOIN subject s ON e.subject_id = s.id
GROUP BY
    e.id, event_level, s.name
ORDER BY s.name ASC, condition_set_descriptions ASC, event_level DESC
LIMIT 20 OFFSET 0

Now I can have a dynamic ORDER BY including other columns too (that are omitted in this example), but always including condition_set_descriptions somewhere in the order. This column is the result of a string_agg function. I cannot move this to a subquery because the LIMIT that is set should apply to the result of the combination of ORDER BY columns that are defined.

The example works fine, but the downside is that the condition_set_descriptions column is also returned as a result of the query, but this is a lot of data and it's not actually needed (as the actual descriptions are looked up in another way using some of the omitted data). All that is needed is that the result is sorted. How can I do this without selecting this in a subquery that would ruin the correctness of the multi-sort limited result set?

Solution

  • ORDER BY can work on calculated expressions too; it doesn't have to be that you calculate something in the SELECT that you then alias and reference the alias in the ORDER BY

    Take a look at doing:

    SELECT
    e.id
FROM 
  ...
ORDER BY 
    s.name ASC,
    string_agg(csb.description, ',' ORDER BY cs.id ASC) ASC, 
    event_level DESC
LIMIT 20 OFFSET 0

    Also double check that left join you have there in the middle of the inner joins; any nulls it produces will be removed again when the next table is inner joined into it, so you can either inner join it or if you're losing data adopt a pattern of:

    w
JOIN x
LEFT JOIN (
  y
  JOIN z
) a

    ie don't left join y to x then inner join z to y, inner join y and z first then left join the result onto x