1対多のテーブルをJOINし、駆動テーブルにLIMITを付ける

あけましておめでとうございます。
2022年も、オンラインコンサルタントをよろしくお願いいたします。

さて、弊社の開発の中で、1対多のテーブルをJOINし駆動テーブルの方でLIMITをしたいということがありました。かなり苦戦したので、ここに解決方法を記載しておきます。
イメージは下記です。

<イメージ>
とあるSNSのユーザーと、そのフォロワー情報を一覧で取ってくる。
【要件】
・ユーザーとフォロワーのid、名前、ログインID、パスワードを取得する
・取得するユーザー数(フォロワーの数は関係なし)の上限を100件とする

・usersテーブル

idnamelogin_idpassword
1
SNS太郎snstarou123456
2SNS次郎snsjirou123456
3SNS三郎snssaburou123456
4SNS四郎snssirou123456

・followersテーブル

followee_idfollower_id
12
13
24

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数が少ない場合は、今回の方法が役に立つかと思います。
もっと良い方法があるという場合は、コメントいただけると嬉しいです。

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です