Rais の構文で複数テーブルにまたがる条件で検索したい

Question

https://teratail.com/questions/153904

こちらで複数テーブルにまたがるSQLの書き方を教えていただいたのですが
これを Rails の ActiveRecord でかく方法がわかりません

というより回答でいただいた SQL が複雑過ぎて理解できないので
Rails の構文でかけば少しは理解できるかなと思って質問した次第です
（あるいは上記回答のSQL文にどこからどこまでが細かく（）をいれてもらえるだけでもだいぶ読みやすくなるのですが
AS、GROUP、BY、HAVING、COUNTといったはじめてみる要素が多々でてくるためどこからどこまでが区切りなのか全くわからないです）

データベースは
user { id: int, ...その他データ }
user_status { id: int(incremental), user_id: int, status_id: int }
というテーブルカラムをもっていて

たとえば

[users table]
1
2
3

[user_statuses table]
user_id status_id
1 1
1 2
1 3
2 2
2 3
2 4
3 3
3 4
3 5

(ユーザ1 はステータス 1,2,3
ユーザ2 はステータス 2,3,4
ユーザ3 はステータス 3,4,5
をもってる）

場合に status_list = [2,3] を検索キーとしてあたえたときにこの全部をもっているユーザを探して
この場合 ユーザ1 とユーザ2 (ユーザ3 はステータス 2 をもってないので除外） が検索結果となる

こういう検索を

@users = User.where(...) # 他の条件でしぼりこみ
@users = @users.where( ステータス 2, 3 をもってるユーザを絞り込みたい )

という Rails の構文でかくにはどうしたらいいんでしょうか

Answer 1

db/schema.rb

ActiveRecord::Schema.define(version: 2018_10_29_120428) do

  create_table "user_statuses", force: :cascade do |t|
    t.integer "user_id"
    t.integer "status"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
    t.index ["user_id"], name: "index_user_statuses_on_user_id"
  end

  create_table "users", force: :cascade do |t|
    t.string "name"
    t.date "birthday"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
  end

end

にたいして、 db/seed.rb を

user = User.create(name: "name_a")
user.user_statuses = [
  UserStatus.create(status:1),
  UserStatus.create(status:2),
  UserStatus.create(status:3)
]
user.save!

user = User.create(name: "name_b")
user.user_statuses = [
  UserStatus.create(status:2),
  UserStatus.create(status:3),
  UserStatus.create(status:4)
]
user.save!

user= User.create(name: "name_c")
user.user_statuses = [
  UserStatus.create(status:3),
  UserStatus.create(status:4),
  UserStatus.create(status:5)
]
user.save!

として、 rails db:migre と rails db:seeds して DB を作りました。

User.joins(:user_statuses).where("user_statuses.status in (2,3)").group(:name).having('COUNT(*) == 2').order(:name)
とすると、つぎの　SQL が 発行できます。

SELECT "users".* FROM "users" INNER JOIN "user_statuses" ON "user_statuses"."user_id" = "users"."id" 
WHERE (user_statuses.status in (2,3))
GROUP BY "users"."name" HAVING (COUNT(*) == 2) 
ORDER BY "users"."name" ASC

rails c で操作して試してみます。

追記：
joins を使わない方法も考えてみました。

User.where(id: UserStatus.where(status: [2,3]).group(:user_id).having("count(*)=2").select(:user_id))
は
SELECT "users".*
FROM   "users" 　WHERE "users"."id" IN 　　
   (
      SELECT   "user_statuses"."user_id" 　　
      FROM     "user_statuses"
      WHERE    "user_statuses"."status" IN (?,?)
                GROUP BY "user_statuses"."user_id"
                HAVING   (count(*)=2)
    )

の QL を発行します。

参考
slformatter といページをつかうと 長い SQL を整形してくれます。
http://www.dpriver.com/pp/sqlformat.htm

Comments

[退会済みユーザー]

ありがとうございます
group by に改行があるだけですごくわかりやすくなりました

SELECT "users".* FROM "users" INNER JOIN "user_statuses" ON "user_statuses"."user_id" = "users"."id"

users と user_statuses テーブルを user_id が同じもの同士で結合するって意味ですよね

ユーザーテーブル
user_id = [1, 2, 3] だったのが
(user_id, status_id)
1 1
1 2
1 3
2 2
2 3
2 4
3 3
3 4
3 5
という状態になって
WHERE (user_statuses.status in (2,3)) によって
1 2
1 3
2 2
2 3
3 3
というレコードになって
GROUP BY "users"."name" で user_id でひとくくりにして
1 => (1, 2), (1, 3)
2 => (2, 2), (2, 3)
3 => (3, 3)
となって HAVING COUNT で要素数が２のもの
1 => (1, 2), (1, 3)
2 => (2, 2), (2, 3)
のユーザレコードがえられるという流れなんでしょうか

ようやく group by の意味わかった気がします

まだ INNER JOIN がなぜ必要かが微妙にしか把握できないんですが
これらの操作をあくまでユーザレコード上の操作にして最後にユーザレコードを得るために必要ということなんでしょうか
p_status 上で where p_status_id in (2,3) だと user_id は得られるけど p_status のレコードになってしまうので以降の users 上の where がかけない
ということなんでしょうか
自分でかけるかはまだわからないですが…

本当にすごく丁寧にありがとうございました

[katoy]

joins を使わない書き方を回答に追記しました。
他にもいろいろな書き方を考えることはできると思います。
"activerecord hasmany where"
などで google 検索してみると良いです。

[退会済みユーザー]

こちらのほうがわかりやすいかもです
というか一番最初にこのロジックで書こうと思っていたんですが具体的にどうかけばいいのかがわからなくて困っていました
何度も丁寧にありがとうございました
おかげで group by と count の使い方は理解できた気がします