user1575921 user1575921 - 7 months ago 31
SQL Question

group twice in one query

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

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.

Comments