結合先のモデルのカラムに指定値が存在しない場合のActiveRecord(もしくはSQL)を教えてください

Question

以下のような多体多のUserテーブルとGroupテーブルが存在したとき、ActiveRecord(もしくはSQL)で「roleが2(mall)なGroupを1つも持ってないUsers」を抽出したいのですが、どうしてもわからないので教えてください。下に記載したDBテーブルのデータだと空の配列が返ってくるイメージです。

（質問のために作ったコードとデータなので多少の粗は目をつむっていただけるとありがたいです）

どうぞよろしくお願いいたします。

DBテーブル

Userテーブル

id	name	role
1	田中	1
2	鈴木	1

Groupテーブル

id	name	role
1	セブン	1
2	イオン	2

UserGroupテーブル

id	user_id	group_id
1	1	1
2	1	2
3	2	2

Rails model

class User < ApplicationRecord
  extend Enumerize
  enumerize :role, in {user: 0, admin: 1, store: 2}

  has_many :group
  has_many :groups, through: :user_groupes
end

class Group< ApplicationRecord
  enumerize :role, in {store: 0, convenience_store: 1, mall: 2, greengrocer: 3}

  has_many :user
  has_many :users, through: :user_groupes
end

class UserGroup< ApplicationRecord
  belongs_to :user
  belongs_to :group
end

モデルの関連付けのイメージ

> User.first.groupes
[<group id: 1, role: 1>, <group id: 2, role: 2>]

> User.second.groupes
[<group id: 2, role: 2>]

Answer 1

SQLで回答します。
MySQLで簡単に確認してみました。

解1

SELECT U.id, U.name
FROM user U
WHERE NOT EXISTS (
    SELECT 1 FROM `group` G
    INNER JOIN user_group U_G ON U_G.group_id=G.id
    WHERE G.role=2 AND U_G.user_id=U.id
)
ORDER BY U.id;

「roleが2なgroupを持ってないuser」をそのままSQLに訳した感じですね。合ってるのかな。

解2

SELECT U.id, MIN(U.name) AS name
FROM user U
  LEFT JOIN user_group U_G
    ON U_G.user_id=U.id
  LEFT JOIN `group` G
    ON G.id=U_G.group_id
      AND G.role=2
GROUP BY U.id
HAVING MIN(G.id) IS NULL
ORDER BY U.id;

JOINでも書けるかなぁと思って書いてみたら、なんか複雑になってしまいました。合ってるのかな。

Comments

[gochuumonha]

2つともそういう書き方できるんだと、非常に勉強になりました！
ありがとうございます！！

Answer 2

間違えて返信ではなくこちらに書いてしまったので消します