user1575921 user1575921 - 7 months ago 15
SQL Question

Count / sum values in subquery and order by it

I have tables like below:

user
id | status
1 | 0

gallery
id | status | create_by_user_id
1 | 0 | 1
2 | 0 | 1
3 | 0 | 1

media
id | status
1 | 0
2 | 0
3 | 0

gallery_media
fk gallery.id fk media.id
id | gallery_id | media_id | sequence
1 | 1 | 1 | 1
2 | 2 | 2 | 1
3 | 2 | 3 | 2

monitor_traffic
1:gallery 2:media
id | anonymous_id | user_id | endpoint_code | endpoint_id
1 | 1 | | 1 | 2 gallery.id 2
2 | 2 | | 1 | 2 gallery.id 2
3 | | 1 | 2 | 3 media.id 3 include in gallery.id 2
these means gallery.id 2 contain 3 rows

gallery_information
fk gallery.id
id | gallery_id


gallery
includes
media
.

monitor_traffic.endpoint_code
:
1
.. gallery;
2
.. media

If
1
then
monitor_traffic.endpoint_id
references
gallery.id


monitor_traffic.user_id
,
monitor_traffic.anonymous_id
integer or null

Objective



I want to output
gallery
rows sort by count each
gallery
rows in
monitor_traffic
, then count the
gallery
related
media
rows in
monitor_traffic
. Finally sum them.

The query I provide only counts
media
in
monitor_traffic
without summing them and also does not count
gallery
in
monitor_traffic
.

How to do this?

This is part of a function, input option then output build query, something like this. I hope to find a solution (maybe with a subquery) that does not require to change other parts of the query.

Query:

SELECT
g.*,
row_to_json(gi.*) as gallery_information
FROM gallery g
LEFT JOIN gallery_information gi ON gi.gallery_id = g.id
LEFT JOIN "user" u ON u.id = g.create_by_user_id
-- start
LEFT JOIN gallery_media gm ON gm.gallery_id = g.id
LEFT JOIN (
SELECT
endpoint_id,
COUNT(*) as mt_count
FROM monitor_traffic
WHERE endpoint_code = 2
GROUP BY endpoint_id
) mt ON mt.endpoint_id = m.id
-- end
ORDER BY mt.mt_count desc NULLS LAST;


sql fiddle

Answer

I suggest a CTE to count both types in one aggregation and join to it two times in the FROM clause:

WITH mt AS (  -- count once for both media and gallery
   SELECT endpoint_code, endpoint_id, count(*) AS ct
   FROM   monitor_traffic
   GROUP  BY 1, 2
   )
SELECT g.*, row_to_json(gi.*) AS gallery_information
FROM   gallery g
LEFT   JOIN mt ON mt.endpoint_id = g.id  -- 1st join to mt
              AND mt.endpoint_code = 1   -- gallery
LEFT   JOIN (
   SELECT gm.gallery_id, sum(ct) AS ct
   FROM   gallery_media gm 
   JOIN   mt ON mt.endpoint_id = gm.media_id  -- 2nd join to mt
            AND mt.endpoint_code = 2          -- media
   GROUP  BY 1
   ) mmt ON mmt.gallery_id = g.id
LEFT   JOIN gallery_information gi ON gi.gallery_id = g.id
ORDER  BY mt.ct DESC NULLS LAST   -- count of galleries
       , mmt.ct DESC NULLS LAST;  -- count of "gallery related media"

Or, to order by the sum of both counts:

...
ORDER  BY COALESCE(mt.ct, 0) + COALESCE(mmt.ct, 0) DESC;

Aggregate first, then join. That prevents complications with "proxy-cross joins" that multiply rows:

The LEFT JOIN to "user" seems to be dead freight. Remove it:
LEFT JOIN "user" u ON u.id = g.create_by_user_id

Don't use reserved words like "user" as identifier, even if that's allowed as long as you double-quote. Very error-prone.

Comments