多段のMySQLのクエリー

Question

複数テーブルがからむリクエストをはじめてかくのでどうかいたらいいかわかりません

users と user_status というテーブルがあって
user_status のカラムは user_id と status_id のペアがはいってます

class CreateUserStatuses < ActiveRecord::Migration
  def change
    create_table :user_statuses do |t|
      t.references  :user, foreign_key: true, null: false
      t.integer     :status_id,               null: false
      t.timestamps                            null: false
    end
  end
end

status_id の配列を検索キーにして
user_status にそのペアが存在するユーザ ID のユーザだけ検索するにはどうすればいいでしょうか

正確な文法ではないのですが

User.where(|user| UserStatus.where(user_id: user.id, p_status_id: p_status_list) != nil)

みたいな条件で検索したいです

つまり検索中のユーザのIDを他のテーブル検索の条件につかいたいんですがこういうことってできるのでしょうか

サンプル：

[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 をもってないので除外）
を id ではなく [<User1> <User2>] のような users のリレーションとして出力したいです
(このあと Users に対しても where でしぼりこみをかけるため）

users.where("id IN (SELECT user_id FROM user_statuses WHERE status_id IN #{status_list})")

のようにかけば動きそうな気がするのですが user_statuses をインデックスのついてない status_id で検索してることになって遅くならなりそうなので、user_statuses の検索は foreign key? (意味がよくわかってないですが）のついてるuser_id をキーに検索をかけたいです

Rails の ActiveRecord メソッドとSQLがまざってしまってますがすいません…

Comments

[yambejp]

テーブルの構造と、サンプルデータ、どういった結果を希望するのか提示してください

Answer 1

• 元データ

create table users(user_id int primary key,user_name varchar(20));
insert into users values(1,'user1'),(2,'user2'),(3,'user3');
create table user_statuses(user_id int, status_id int,unique key(user_id,status_id));
insert into user_statuses values(1,1),(1,2),(1,3),(2,2),(2,3),(2,4),(3,3),(3,4),(3,5);

• status_idが2と3の両方を含むもの

select user_id from user_statuses where status_id in(2,3) group by user_id
having count(*)=2

※count(*)=2の「2」は2,3の2個を含むという意味

• リレーションして

select * from users as t1 where exists(
select 1 from user_statuses where status_id in(2,3) group by user_id
having count(*)=2 and user_id =t1.user_id
)

Comments

[退会済みユーザー]

回答ありがとうございます
as を使うことで検索中のレコードのカラムをさらに他のテーブルの条件に使うことができるのですね

すいません
知識不足ですぐに理解できないのでとりあえずお礼だけさせてください

リレーションで使いたいというのは Rails のアクティブレコードの where 文で条件をつなげたかったからなのですが、select ではなく where の中に as を含む構文をいれる方法がわからないのと
group by と count もわりと定番の書き方みたいですがはじめてみたので
意味を理解するためにもう少し調べてみたいと思います

[yambejp]

まぁ今回は自己結合ではないので別名（users as t1）をつかわずに
参照時にテーブル名を直接指定してもいいですね
「user_id =t1.user_id」→「user_id =users.user_id」

[退会済みユーザー]

ようやくわかりました
group by がなぜ必要かよくわかってなくて
status_id in (1, 2) && user_id == user.id のレコードが２個かどうか調べるだけでいいと思ったんですけど
having_count を使うのに group by が必要だったのですね