group twice in one query

Question

I use below code but doesn't return what I expect,

the table relationship,
each gallery is include multiple media and each media is include multiple media_user_action. I want to count each gallery how many media_user_action and order by this count

rows: [
 {
   "id": 1
 },
 {
   "id": 2
 }
]

and this query will return duplicate gallery rows something like

rows: [
 {
   "id": 1
 },
 {
   "id": 1
 },
 {
   "id": 2
 }
 ...
]

I think because in the LEFT JOIN subquery select media_user_action rows only group by media_id, need to group by gallery_id also ?

SELECT
 g.*
 FROM gallery g 
   LEFT JOIN gallery_media gm ON gm.gallery_id = g.id
   LEFT JOIN (
     SELECT 
       media_id, 
       COUNT(*) as mua_count
       FROM media_user_action
       WHERE type = 0
       GROUP BY media_id
   ) mua ON mua.media_id = gm.media_id
   ORDER BY g.id desc NULLS LAST OFFSET $1 LIMIT $2

table

gallery
id |
1  |
2  |

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

media_user_action
id | media_id fk media.id | user_id | type
1  | 1                    | 1       | 0
2  | 1                    | 2       | 0
3  | 3                    | 1       | 0
...

media
id | 
1  |
2  |
3  |

UPDATE
There's more other table I need to select, this is a part in a function like this https://jsfiddle.net/g8wtqqqa/1/ when user input option then build query.

So I correct my question I need to find a way if user want to count media_user_action order by it, I wanna know how to put these in a subquery possible not change any other code

Base on below @trincot answer I update code, only add media_count on top change a little bit and put those in sub query. is what I want,
now they are group by gallery.id, but sort media_count desc and asc are same result not working I can't find why?

SELECT
 g.*,
 row_to_json(gi.*) as gallery_information,
 row_to_json(gl.*) as gallery_limit,
 media_count
 FROM gallery g 
 LEFT JOIN gallery_information gi ON gi.gallery_id = g.id
 LEFT JOIN gallery_limit gl ON gl.gallery_id = g.id
 LEFT JOIN "user" u ON u.id = g.create_by_user_id
 LEFT JOIN category_gallery cg ON cg.gallery_id = g.id
 LEFT JOIN category c ON c.id = cg.category_id 
   LEFT JOIN (
     SELECT 
       gm.gallery_id,
       COUNT(DISTINCT mua.media_id) media_count
       FROM gallery_media gm
       INNER JOIN media_user_action mua
         ON mua.media_id = gm.media_id AND mua.type = 0
       GROUP BY gm.gallery_id
   ) gm ON gm.gallery_id = g.id
   ORDER BY gm.media_count asc NULLS LAST OFFSET $1 LIMIT $2

Answer 1

The join with gallery_media table is multiplying your results. The count and grouping should happen after you have made that join.

You could achieve that like this:

SELECT    g.id,
          COUNT(DISTINCT mua.media_id)
FROM      gallery g
LEFT JOIN gallery_media gm
       ON gm.gallery_id = g.id
LEFT JOIN media_user_action mua
       ON mua.media_id = gm.id AND type = 0
GROUP BY  g.id
ORDER BY  2 DESC

If you need the other informations as well, you could use the above (in simplified form) as a sub-query, which you join with anything else that you need, but will not multiply the number of rows:

SELECT    g.*
          row_to_json(gi.*) as gallery_information,
          row_to_json(gl.*) as gallery_limit,
          media_count
FROM      gallery g
LEFT JOIN (
            SELECT     gm.gallery_id,
                       COUNT(DISTINCT mua.media_id) media_count
            FROM       gallery_media gm
            INNER JOIN media_user_action mua
                    ON mua.media_id = gm.id AND type = 0
            GROUP BY   gm.gallery_id
          ) gm
       ON gm.gallery_id = g.id
LEFT JOIN gallery_information gi ON gi.gallery_id = g.id
LEFT JOIN gallery_limit gl ON gl.gallery_id = g.id
ORDER BY  media_count DESC NULLS LAST 
OFFSET    $1
LIMIT     $2

The above assumes that gallery_id is unique in the tables gallery_information and gallery_limit.

Answer 2

If you only want to show data from table gallery (with select g.*) then why do you join the other tables? Outer joins either join one ore more records to each main record (depending on how many matches are found in the outer-joined table), so no surprise you get duplicates (in your case because gallery ID 1 has two matches in gallery_media).

Answer 3

You're grouping by media_id to get a count, but since one gallery can have many gallery_media, you still end up with multiple rows for one gallery. You can either sum the mua_count from your subselect:

SELECT g.*, sum(mua_count)
FROM gallery g 
LEFT JOIN gallery_media gm ON gm.gallery_id = g.id
LEFT JOIN (
  SELECT media_id, 
  COUNT(*) as mua_count
  FROM media_user_action
  WHERE type = 0
  GROUP BY media_id
) mua ON mua.media_id = gm.media_id
GROUP BY g.id
ORDER BY g.id desc NULLS LAST;

 id | sum
----+-----
  2 |   1
  1 |   2

Or you can just JOIN all the way through and group once on g.id:

SELECT g.id, count(*)
FROM gallery g
JOIN gallery_media gm ON gm.gallery_id = g.id
JOIN media_user_action mua ON mua.media_id = gm.id
GROUP BY g.id
ORDER BY count DESC;

 id | count
----+-------
  1 |     2
  2 |     1