Railsで配列の値でAND検索

Question

お世話になっております。
Railsアプリでの検索についてです。

やりたいこと

配列の値が全て含まれるレコードを取得したい。

SQLでのINがあると思うのですが、それのAND検索というイメージです。

DB

user

• id
• name

tag

• id

user_tags

• user_id
• tag_id

class User < ActiveRecord::Base
  has_many :tags
end

class Tag < ActiveRecord::Base

end

class UserTag < ActiveRecord::Base
  belongs_to :user
  belongs_to :tag
end

例

実際の使い方としては、以下のようなシチュエーションです。

「タグID 10と20の両方を持っているユーザーを取得したい」

この目的がある時のactive recordの書き方です。

現在以下のようにやろうとしているのですが、期待通りに動きません。

tag_ids = [10,20]

users = User.joins(:user_tag)
tag_ids.each do |tag|
  users = users.where(user_tag: {tag_id: tag})
end

users.count

=> 0

発行されるSQLは以下の通りです。

SELECT COUNT(*) 
FROM `users` 
INNER JOIN `user_tags` ON `user_tags`.`user_id` = `users`.`id` 
WHERE `user_tags`.`tag_id` = 10
 AND `user_tags`.`tag_id` = 20;

10と20の両方のtag_idを持っているuserを取得する方法をご教授ください。
このtag_idの配列の数は可変です。

[10,20]や[10,20,30]など、最大で13個の数値が入ります。

ちなみに生のSQLでの方法も探りましたが、こちらもうまくいきませんでした。

実際の開発環境のDB構造よりも簡略化しているため、不備があるかもしれません。何なりとお申し付けください。

Comments

[suama]

tag_idsは例だと2つですが、可変なんでしょうか？

[kentos]

suana様　ご質問ありがとうございます。　おっしゃる通り可変でございます。

[suama]

ありがとうございます。２つだけなら where().where() でチェーンでつなげれば行けそうですが、動的に変わる可能性があるのですね。

Answer 1

こんにちは。
最初のSQLでは、おそらく普通に発行してもデータは抽出できないかな？と思いました。
こういうSQLになればいいでしょうか？

-- 1つめ
SELECT *
FROM `users`
  LEFT JOIN user_tags ON
      users.id = user_tags.user_id
  WHERE users.id IN 
     (SELECT user_id FROM user_tags WHERE 
        user_tags.tag_id = 20)
  AND users.id IN 
     (select user_id FROM user_tags 
       WHERE user_tags.tag_id = 10)


-- 2つめ
SELECT * from users
  LEFT JOIN user_tags ON users.id = user_tags.user_id
  WHERE user_tags.tag_id IN (10, 20)
  GROUP BY users.id
  HAVING COUNT(users.id) = 2

※それぞれのユーザが持つtag_idは、重複しないものと想定。
（user: 1 -> tag_id: 10, 20, 10 と10が重複することは無し）

また、Userはこうだったとして考えてみました。

class User < ActiveRecord::Base
  has_many :user_tags  # has_many: tagsでない
end

２つめのパターンでの例ですが、これでできますでしょうか..
（Tagは使っていないので、前提が間違っていたらすみません）

ids = [10, 20]

# 基本の条件
condition = User.joins(:user_tags)

# 長くなってすみません...
result = condition.merge(UserTag.where(tag_id: ids)).group(:user_id).having('count(users.id) = ?', ids.length)

result.length # 数を算出
result.to_sql # sqlを出力して確認

Comments

[kentos]

suama 様
ご回答ありがとうございます。

またコードも文章も非常にわかりやすくありがたいです。

提示していただいた”ふたつめのパターン”を試してみたのですが、このコードだと以下の問題が発生してしまいます。。。

user1のタグが 10,20,30,40,50の時

```
ids = [10,20]

~

~.having('count(users.id) = ?', ids.length)

~
```

このhavingで、 `ids.length` が2になるので5個のtagがついているため、取得できません。
「両方のタグを持っているUserを取得する」ことが目標なので、この状況でもuser 1 は取得したいところです。。。

言葉足らずで申し訳ないです。

[suama]

お返事ありがとうございます！
.having('count(users.id) >= ?', ids.length) でもダメでしょうか？
実際のデータを拝見していないので、もっと複雑かと思いますが....

[moke]

ids = [10,20]
を
ids = [10,20,30,40,50]
に書き換えるのでは?
パターン2は目から鱗ですが
squeelというgemを使うと
パターン１も割と簡単にかけます。

Answer 2

当方Rails使いでは無いのでできるかわかりませんが、冗長でいいならこのようなSQLで作れます。

select
	u.id
from
	(
	select
		u.id
	from
		user_tags as ut
		inner join user as u on ut.user_id = u.id
	where
		ut.tag_id = 1
	union all
	select
		u.id
	from
		user_tags as ut
		inner join user as u on ut.user_id = u.id
	where
		ut.tag_id = 3
	) as u
group by
	u.id
having
	count(u.id) >= 2
;

union allでそれぞれのタグに紐づくすべてのuserを取得し、それらすべてを持っているものをgroup by及びhavingで絞り込む方法です。
検索タグが増えた場合は上記union all以下のコードのut.tag_idの指定を変更して繋げることで可能です。
その際はhavingの数も検索するタグ数に変更してください。

もっと少なく書ける気がしますが、当方の腕ではここまでが限界でした。
ご参考になればと思います。