I run this complicated query on Spring JPA Repository. My goal is to get all info from the site table, ordering it by events severity on each site.
This is my query:

SELECT alls.* FROM sites AS alls JOIN 
( 
    SELECT distinct ets.id FROM 
    ( 
        SELECT s.id, et.`type`, et.severity_level, COUNT(et.`type`) FROM sites AS s  
            JOIN users_sites AS us ON (s.id=us.site_id)  
            JOIN users AS u ON (us.user_id=u.user_id) 
            JOIN areas AS a ON (s.id=a.site_id) 
            JOIN panels AS p ON (a.id=p.area_id) 
            JOIN events AS e ON (p.id=e.panel_id) 
            JOIN event_types AS et ON (e.event_type_id=et.id) 
        WHERE u.user_id="98765432-123a-1a23-123b-11a1111b2cd3"  
        GROUP BY s.id , et.`type`, et.severity_level 
        ORDER BY et.severity_level, COUNT(et.`type`) DESC 
   ) AS ets 
) as etsd ON alls.id = etsd.id

The second select (the one with "distinct") returns site_ids ordered correctly by severity. Note that there are different event_types severity in each site, and I use pagination on the answer, so I need the distinct.

The problem is - the main select doesn't keep this order. Is there any way to keep the order in one complicated query?

Another related question - one of my ideas was making two queries:

1. The "select distinct" query that will return me the order --> saved in a list "order list"
2. The main "sites" query (that becomes very simple) with "where id in {"order list"}
3. Order the second query in code by "order list".

I use the query every 10 seconds, so it is very sensitive on performance. What seems to be faster in this case - original complicated query or those 2?

Any insight will be appreciated. Tnx a lot.

CodePudding user response：

A quirk of SQL's declarative set-oriented syntax for us procedural programmers: ORDER by clauses in subqueries are not carried through to the outer query, except sometimes by accident. If you want ordering at any query level, you must specify it at that level or you will get unpredictable results. The query optimizers are usually smart enough to avoid wasting sort operations.

Your requirement: give at most one sites row for each sites.id value, ordered by the worst event. Worst: lowest event severity, and if there are more than one event with lowest severity, the largest count.

Use this sort of thing to get the "worst" for each id, in place of DISTINCT.

      SELECT id, MIN(severity_level) severity_level, MAX(num) num
        FROM (
           /* your inner query */
             ) ets
       GROUP BY id

This gives at most one row per sites.id value. Then your outer query is

SELECT alls.*
  FROM sites alls
  JOIN (
      SELECT id, MIN(severity_level) severity_level, MAX(num) num
        FROM (
           /* your inner query */
             ) ets
       GROUP BY id
       ) worstevents ON alls.id = worstevents.id
 ORDER BY worstevents.severity_level, worstevents.num DESC, alls.id 

Putting it all together:

SELECT alls.*
  FROM sites alls
  JOIN (
      SELECT id, MIN(severity_level) severity_level, MAX(num) num
        FROM (
             SELECT s.id, et.severity_level, COUNT(et.`type`) num
               FROM sites AS s  
               JOIN users_sites AS us ON (s.id=us.site_id)  
               JOIN users AS u ON (us.user_id=u.user_id) 
               JOIN areas AS a ON (s.id=a.site_id) 
               JOIN panels AS p ON (a.id=p.area_id) 
               JOIN events AS e ON (p.id=e.panel_id) 
               JOIN event_types AS et ON (e.event_type_id=et.id) 
              WHERE u.user_id="98765432-123a-1a23-123b-11a1111b2cd3"  
              GROUP BY s.id , et.`type`, et.severity_level 
             ) ets
       GROUP BY id
       ) worstevents ON alls.id = worstevents.id
 ORDER BY worstevents.severity_level, worstevents.num DESC, alls.id 

An index on users.user_id will help performance for these single-user queries.

If you still have performance trouble, please read this and ask another question.