Following this question: Remove duplicates on MySQL JOIN query on JSON_ARRAYAGG with INNER JOIN
I have an app with users; the users can create bookmarks, and each bookmark can have multiple tags, as well as be private.
So, if I'm not mistaken in the notation: user =* bookmark *=* tag
I want to retrieve the users with the tags they used in their bookmarks. But, there is a detail: we are going to have the @SESSION_ID
available in the query: if the bookmark is private and the user that created it is not the one in @SESSION_ID
, don't return it; do it otherwise:
SELECT
`user`.`id`,
(
SELECT
JSON_ARRAYAGG(
JSON_OBJECT(
'id', subQuery.id,
'name', subQuery.name
)
)
FROM (
SELECT DISTINCT
tag.id,
tag.name,
bookmark.user_id,
bookmark.isPrivate
FROM tag
INNER JOIN bookmark_tag ON bookmark_tag.tag_id = tag.id
INNER JOIN bookmark ON bookmark.id = bookmark_tag.bookmark_id
INNER JOIN `user` ON user.id = bookmark.user_id
) subQuery
WHERE user.id = subQuery.user_id
AND
(
subQuery.isPrivate IS NOT TRUE
OR
subQuery.user_id = @SESSION_ID
)
) AS tags
FROM `user`
GROUP BY `user`.`id`
;
As you may see, if the bookmark is not private or @SESSION_ID
doesn't match the user in bookmark.user_id, there will be no duplicates; but if the bookmark is private and @SESSION_ID
matches, some duplicates will be returned.
Here is a fiddle: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=7ae76ba61c5870c3a9f31d9564538fa1
Any idea will be welcome!