0

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?

Sebastiaan van den Broek
  • 5,818
  • 7
  • 40
  • 73
  • FIX NEEDED in the SQL: The columns in 'group by' clause should exist in SELECT column projection list [ it has only 'e.id' , missing 'event_level' and 's.name']. POSSIBLE SOLUTION: You can wrap the whole SQL in an INLINE VIEW and the use the order by on the INLINE VIEW. – rajorshi Feb 07 '22 at 06:10
  • @rajorshi That’s incorrect, s.name is clearly present and event_level is a column of event (could have been more explicit but isn’t needed). This query works with the database that I have. – Sebastiaan van den Broek Feb 07 '22 at 06:14
  • I'm not really understanding why you can't just move the string_agg expression to the order by block. `SELECT e.id ... ORDER BY a, b, STRING_AGG(..), c, d...` – Caius Jard Feb 07 '22 at 06:52
  • @CaiusJard that’s because I didn’t realize that was possible! I’ll try that when I get home. If you can make that into an answer I’ll accept it. – Sebastiaan van den Broek Feb 07 '22 at 06:53

1 Answers1

2

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

Caius Jard
  • 72,509
  • 5
  • 49
  • 80