ActiveRecord で別のテーブル内を条件検索する方法

で別のテーブルを複数の値で検索する方法を質問したのですが
１度に多くの内容を質問しすぎていただいた回答のSQL文が理解できないので
別テーブルを１つのデータで検索する方法を理解したいです

users テーブル（今はIDしか使わない）と

以下のようなマイグレーションで作成された user_status テーブル (ユーザ x ステータス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

たとえば
users table に
1
2
3
と３人のユーザがはいっていて

user_status table に
1 1
1 2
2 2
2 3
3 3
3 4
(ユーザ1 がステータス[1, 2], ユーザ2 がステータス[2, 3], ユーザ3 がステータス[3, 4] をもっている)
とします

このときステータス 2 をもっているユーザを検索したい（結果は users table のレコード [<user1>, <user2>] を得たい）場合
Rails のコードで Users.where(...) の中に何とかけばいいんでしょうか

最終的には上記関連質問にあるとおり複数のステータスをすべてもったユーザを検索したいのですが
group by とか count を使う必要があるみたいなんですが１度に複数要素が入ると理解できなかったので
とりあえず１つのキーに対して別テーブルを参照する方法を理解したいです

よろしくおねがいします

追記：

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

たとえばこんな感じで user_status の全レコード中で status_id をもってる user_id を全部取得して
その中に含まれてるユーザだけ取り出すというのはかけそうなのですが
status_id は index 化されてないためレコードすべてをなめてパフォーマンス的に問題がありそうな気がします
できれば user_status の中は index されてる user_id のほうで検索をかけたいです

行動規範の内容に同意します

回答1件

ベストアンサー

できれば user_status の中は index されてる user_id のほうで検索をかけたいです

status_idを条件にするのに、それを使わずに検索する、なんていうのは無理な相談です。そして、

status_id は index 化されてないためレコードすべてをなめてパフォーマンス的に問題がありそうな気がします

status_idが任意の値を取れるものならまた別ですが、リストが決まっているならマスターテーブルを作って外部キーをかけておきましょう。うっかりリストの範囲外の値が入ったりすると、何もかもめちゃくちゃになります。

投稿2018/10/24 04:45

maisumakun

総合スコア145183

退会済みユーザー

2018/10/24 05:04 編集

＞status_idを条件にするのに、それを使わずに検索する、なんていうのは無理な相談です使わずにではなくインデックスとしてテーブルをひくのはあくまで user_id だけにしたいです検索中の user_id (たとえばuser1) でまず status_table をひいて（インデックス化されてるので早いはず）検索結果の status_id (1 と 2 がかえってくる) の中に探してる status_id(2) があれば結果に含めるというようなアルゴリズムで検索を行いたいのですが不可能なのでしょうか ``` result = [] for user in Users.all #result << user if UserStatus.find_by(user_id: user.id, status_id: 2).present? ではなく result << user if UserStatus.where(user_id: user.id).include?(2) end ``` という感じで検索すれば UserStatus に関しては index 付きのアクセスだけですむのではないかと思ってるのですが、これを１つのリクエストに記述する方法がわからないのです >status_id が任意の値を取れるものならまた別ですが任意かリストがきまっているかでやり方がかわってくるのでしょうか外部キーというのもよくわかっていなくて単に index 化されててテーブルを全部みなくてもレコードを早くひけるのかな？ぐらいにしか思ってないのですが

maisumakun

2018/10/24 06:00 編集

> 検索結果の status_id (1 と 2 がかえってくる) の中に探してる status_id(2) があれば結果に含めるというようなアルゴリズムで検索を行いたいのですがもとの「ステータス 2 をもっているユーザを検索したい（特に条件がないので、全員分、と解釈しました）」とやりたいこと自体が変わっているようなのですが、どちらが意図したものなのでしょうか。 > 任意かリストがきまっているかでやり方がかわってくるのでしょうか外部キーは、関連づいたテーブルにあるものしか入れられなくなる、という機能なので、任意のものを入れられる場合には適用できません。

退会済みユーザー

2018/10/24 06:20 編集

>もとの「ステータス 2 をもっているユーザを検索したい（特に条件がないので、全員分、と解釈しました）」とやりたいこと自体が変わっているようなのですが、どちらが意図したものなのでしょうか。全員分ですすいませんよく考えるとユーザごとに全員のIDでステータステーブルをひくとインデックスがついていたとしても結局全レコードはみることになってしまうんですよね… ということははじめから両方のデータを条件にいれてしまうほうが早そうですね… @users = Users.where("id IN (SELECT user_id FROM user_statuses WHERE status_id = #{status_id})") 結局こうかくのが一番はやいんでしょうか… とにかく複数のテーブルにわかれている条件検索がはじめてで… レコードの設計含めてどうするがいいのか知識がなさすぎて申し訳ありません

maisumakun

2018/10/24 06:31

status_idで検索する用事が多いなら、「インデックスを付ける」のが正攻法かと思います。そうすれば不自然に「status_idを避けて検索」なんてことをする必要もなくなるわけですし。

退会済みユーザー

2018/10/24 06:41

なるほどですデータベースの設計はもっとなれてる人がやってくれたのでインデックスをつけるという発想がなかったですマイグレーションに add_index : user_statuses, status_id とかくだけで上記検索は早くなるんでしょうか？

maisumakun

2018/10/24 06:43 編集

実際に張った上で、速度測定やEXPLAINなどで確認してみましょう（検索時のSQLに使うインデックスの指示を入れる必要は、通常ありませんが、インデックスが使われるかどうかの確認は必要です）。

退会済みユーザー

2018/10/24 06:45

わかりました何度も返信していただいてありがとうございました