63159


**前提**  
3テーブルあります

  * blogテーブル。多
  * tagテーブル。多
  * blog_tagテーブル

tagテーブルのデータ格納例  
・「tag_idカラム」2。「tagカラム」y  
・「tag_idカラム」5。「tagカラム」x

* * *

**やりたいこと**  
下記のようなデータを取得したい

[code]

    [blog_id] => 1
    [tag_id] => 2,5
    [tag] => y,x
    
[/code]

あるいは下記のようなデータを取得したい

[code]

    [blog_id] => 1
    [tag_id] => 5,2
    [tag] => x,y
    
[/code]

* * *

**現状**  
取得結果

[code]

    [blog_id] => 1
    [tag_id] => 2,5
    [tag] => x,y
    
[/code]

SQL

[code]

    SELECT
     GROUP_CONCAT(DISTINCT bt.tag_id) AS tag_id,
     GROUP_CONCAT(DISTINCT t.tag) AS tag
    FROM
     blog AS b
    LEFT JOIN blog_tag AS bt ON (b.id = bt.blog_id)
    LEFT JOIN tag AS t ON (t.tag_id = bt.tag_id)  
    WHERE b.id = :id
    
[/code]

* * *

**質問**  
・現状のSQLで、意図した通り順番を取得できない理由は？  
・やりたい取得結果を取得するSQLは？


CC BY-SA 4.0
2020-02-17T02:58:08.477
63101
2020-02-18T14:34:39.360
2020-02-18T00:26:27.343
3060
7886
question
mysql
MySQLの多 対 多 テーブルから GROUP_CONCAT で、期待した組み合わせ通り結果を取得したい


タグの名前が重複していようが、無理やり表示する別解としてこちらを残しておきます。

JSON_ARRAYAGG (5.7.22 から導入) を用いて、

[code]

    select id, agg->'$[*][0]'  as tag_ids, agg->'$[*][1]' as tags from (
      select id, JSON_ARRAYAGG(JSON_ARRAY(tag_id, tag)) as agg
      from (
        select distinct b.id, t.tag_id, t.tag
        FROM
         blog AS b
        LEFT JOIN blog_tag AS bt ON (b.id = bt.blog_id)
        LEFT JOIN tag AS t ON (t.tag_id = bt.tag_id)
        where b.id = 1
      ) as t
      group by id
    ) as t;
    
[/code]

とすることで、

[code]

    +----+---------+------------+
    | id | tag_ids | tags       |
    +----+---------+------------+
    |  1 | [2, 5]  | ["y", "x"] |
    +----+---------+------------+
    
[/code]

を得ます。

そして、自分でしたら、 json 配列を `2,5` などに整形する必要がある場合、それはアプリケーション側で実行すると思います。

* * *

ただ、ここまでだと tag に紐づかない blog については、

[code]

    +----+---------+--------+
    | id | tag_ids | tags   |
    +----+---------+--------+
    |  3 | [null]  | [null] |
    +----+---------+--------+
    
[/code]

が得られてしまいます。

8.0 以上の CTE を用いれば、以下のようにできます。

[code]

    with target_id as ( select 3 ),  -- ここを実際のプレースホルダにかえる
    agg as (
      select JSON_ARRAYAGG(JSON_ARRAY(tag_id, tag)) as agg
      from (
        select distinct b.id, t.tag_id, t.tag
        FROM
         blog AS b
        INNER JOIN blog_tag AS bt ON (b.id = bt.blog_id)
        INNER JOIN tag AS t ON (t.tag_id = bt.tag_id)
        where b.id = (select * from target_id)
      ) as t
      group by id
    )
    select (select * from target_id) as id,
      coalesce(
        (select agg->'$[*][0]' from agg),
        JSON_ARRAY()
      ) as tag_ids,
      coalesce(
        (select agg->'$[*][1]' from agg),
        JSON_ARRAY()
      ) as tags
    
[/code]

[code]

    +------+---------+------+
    | id   | tag_ids | tags |
    +------+---------+------+
    |    3 | []      | []   |
    +------+---------+------+
    
[/code]

* * *

以下、手軽に検証するための CTE

[code]

    with blog as (
      select 1 as id
      union all
      select 2 as id
      union all
      select 3 as id
    ), tag as (
      select 2 as tag_id, 'y' as tag
      union all
      select 5, 'x'
      union all
      select 1, 'z'
    ), blog_tag as (
      select 1 as blog_id, 2 as tag_id
      union all
      select 1 as blog_id, 2 as tag_id
      union all
      select 1 as blog_id, 5 as tag_id
      union all
      select 2 as blog_id, 1 as tag_id
    ),
    -- 以下、実際のクエリ部分
    target_id as ( select 3), -- ここを実際のプレースホルダにかえる
    agg as (
      select JSON_ARRAYAGG(JSON_ARRAY(tag_id, tag)) as agg
      from (
        select distinct b.id, t.tag_id, t.tag
        FROM
         blog AS b
        INNER JOIN blog_tag AS bt ON (b.id = bt.blog_id)
        INNER JOIN tag AS t ON (t.tag_id = bt.tag_id)
        where b.id = (select * from target_id)
      ) as t
      group by id
    )
    select (select * from target_id) as id,
      coalesce(
        (select agg->'$[*][0]' from agg),
        JSON_ARRAY()
      ) as tag_ids,
      coalesce(
        (select agg->'$[*][1]' as tags from agg),
        JSON_ARRAY()
      ) as tags;
    
[/code]


CC BY-SA 4.0
2020-02-18T13:09:13.307
63157
2020-02-18T14:34:39.360
2020-02-18T14:34:39.360
754
754
63101
answer

[code]

    select
      b.id,
      group_concat(distinct t.tag_id),
      group_concat(distinct t.tag order by t.tag_id)
    FROM
     blog AS b
    left outer JOIN blog_tag AS bt ON (b.id = bt.blog_id)
    left outer JOIN tag AS t ON (t.tag_id = bt.tag_id)
    where b.id = 1;
    
[/code]

とすると、意図する結果が得られると思います。

<https://dev.mysql.com/doc/refman/8.0/en/group-by-functions.html>

上記にもありますが、 group_concat はその記法として、 order by
を指定できるようになっています。そして、ドキュメントには明記されていませんが、どうやら、 distinct
が付与されていた場合、暗黙的に与えられたカラムでソートしてしまう様子です。

なので、やりたいことを実現するためには、自前で order by を指定してやれば良い様子です。

これが動作する前提として、 tag.tag の名前は、ユニークである必要があります。


CC BY-SA 4.0
2020-02-18T14:30:43.873
63159
2020-02-18T14:30:43.873
754
63101
answer