1

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!

Emille C.
  • 562
  • 1
  • 7
  • 23
  • Can you post the result you get with the query above and how exactly is your expected result? That way we can see the difference and give some thought. I'm guessing the `OR` operation is incorrect. Your condition of _"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:"_ sounds more like doing a `CASE` expression rather than `OR` operation. – FanoFN Jan 20 '21 at 06:50

0 Answers0