あけましておめでとうございます。
2022年も、オンラインコンサルタントをよろしくお願いいたします。
さて、弊社の開発の中で、1対多のテーブルをJOINし駆動テーブルの方でLIMITをしたいということがありました。かなり苦戦したので、ここに解決方法を記載しておきます。
イメージは下記です。
＜イメージ＞
とあるSNSのユーザーと、そのフォロワー情報を一覧で取ってくる。
【要件】
・ユーザーとフォロワーのid、名前、ログインID、パスワードを取得する
・取得するユーザー数（フォロワーの数は関係なし）の上限を100件とする
・usersテーブル
| id | name | login_id | password |
| 1 | SNS太郎 | snstarou | 123456 |
| 2 | SNS次郎 | snsjirou | 123456 |
| 3 | SNS三郎 | snssaburou | 123456 |
| 4 | SNS四郎 | snssirou | 123456 |
・followersテーブル
| followee_id | follower_id |
| 1 | 2 |
| 1 | 3 |
| 2 | 4 |
LIMITなしで情報を取得しようとすると、下記のようなSQLになります。
SELECT
followee.id, followee.name, followee.login_id, followee.password,
follower.id as follower_id, follower.name as follower_name,
follower.login_id as follower_login_id, follower.password as follower_password
FROM
users AS followee
LEFT JOIN
followers
ON
followee.id = followers.followee_id
LEFT JOIN
users AS follower
ON
follower.id = followers.follower_id
この結果に対して、取得するfolloweeの数を制限したいのですが、今回のレコード数が「フォローフォロワー関係の数」となり、followeeのLIMITをすることができません。。。
今回は、GROUP_CONCATを使った方法で解決しました。
下記のSQLです。
SELECT
followee.id, followee.name, followee.login_id, followee.password,
GROUP_CONCAT(COALESCE(follower.id, 'NULL') ORDER BY follower.id) AS follower_ids,
GROUP_CONCAT(COALESCE(follower.name, 'NULL') ORDER BY follower.id) AS follower_names,
GROUP_CONCAT(COALESCE(follower.login_id, 'NULL') ORDER BY follower.id) AS follower_login_ids,
GROUP_CONCAT(COALESCE(follower.password, 'NULL') ORDER BY follower.id) AS follower_passwords
FROM
users AS followee
LEFT JOIN
followers
ON
followee.id = followers.followee_id
LEFT JOIN
users AS follower
ON
follower.id = followers.follower_id
GROUP BY followee.id
ORDER BY followee.id ASC
LIMIT 100
これにて、ユーザーとフォロワーの情報を取得し、LIMITを付けることもできました。
あとがき
今回、SNSの例を使ったんですが、実際にはフォロワーの数が膨大になるので今回の解決方法は現実的でないですね。。。
1つのデータに対するJOIN数が少ない場合は、今回の方法が役に立つかと思います。
もっと良い方法があるという場合は、コメントいただけると嬉しいです。