Rails how to find record by association's ids contain array

Question

course has_many tags by has_and_belongs_to, now given two id of tags, [1, 2], how to find all courses that have those both two tags

Course.joins(:tags).where("tags.id IN (?)" [1, 2]) will return record that have one of tags, not what I wanted

# app/models/course.rb
has_and_belongs_to_many :tags



# app/models/tag.rb
has_and_belongs_to_many :courses

I guess it will return all the records with `tags.id = 1`and `tags.id = 2`. — Pavan, May 20 '14 at 08:49
maybe not work because tags is a association and id can't equal 1 and at the same time equal 2 — William Herry, May 20 '14 at 08:51
Try with this `Course.joins(:tags).where("tags.id" => [1, 2])` — Pavan, May 20 '14 at 08:59
Are you using PostgreSQL? If so you can use the [`ALL`](http://www.postgresql.org/docs/9.1/static/functions-subquery.html#FUNCTIONS-SUBQUERY-ALL) operator. — Vapire, May 20 '14 at 09:11
this will return all courses have tag.id equal 1 OR tag.id equal 2, same with using `IN` — William Herry, May 20 '14 at 09:12
@Vapire I am using PostgreSQL, but I am new to PostgreSQL, could you please give me the full code? — William Herry, May 20 '14 at 09:29
http://stackoverflow.com/questions/11468572/postgresql-where-all-in-array — Richard Peck, May 20 '14 at 09:39

Vapire · Answer 1 · 2014-05-21T09:03:11.933

7

Since you're working with PostgreSQL, instead of using the IN operator you can use the ALL operator, like so:

Course.joins(:tags).where("tags.id = ALL (?)", [1, 2])

this should match all ids with an AND instead of an OR.

edited May 21 '14 at 09:03

answered May 20 '14 at 10:22

Vapire

4,568
3
24
41

still not work: `Course.joins(:tags).where("tags.id ALL (?)", [1, 2])` `: SELECT "courses".* FROM "courses" INNER JOIN "courses_tags" ON "courses_tags"."course_id" = "courses"."id" INNER JOIN "tags" ON "tags"."id" = "courses_tags"."tag_id" WHERE (tags.id ALL (1,2)) ActiveRecord::StatementInvalid: PG::SyntaxError: ERROR: syntax error at or near "ALL" LINE 1: ...gs"."id" = "courses_tags"."tag_id" WHERE (tags.id ALL (1,2)) ^ ` – William Herry May 20 '14 at 10:33
Thanks, I forgot the `,` after the where string. – Vapire May 20 '14 at 10:47
I think you need to use an operator, as well… edited my answer. – Vapire May 21 '14 at 09:04
Use PostgreSQL array syntax: `Course.joins(:tags).where("tags.id = ALL (?)", '{1, 2}')` – Vlad Hilko Sep 20 '17 at 11:51

score 3 · Accepted Answer · answered May 20 '14 at 10:26

3

This is not a single request, but might still be as quick as other solutions, and can work for any arbitrary number of tags.

tag_ids = [123,456,789,876] #this will probably come from params
@tags = Tags.find(tag_ids)
course_ids = @tags.inject{|tag, next_tag| tag.course_ids & next_tag.course_ids} 
@courses = Course.find(course_ids)

answered May 20 '14 at 10:26

Max Williams

32,435
31
130
197

this works, thanks, I am gonna wait for some time to see if there are more better answer – William Herry May 20 '14 at 11:07

score 0 · Answer 3 · answered Feb 16 '23 at 13:52

0

referring to @Vapire's answer

Course.joins(:tags).where("tags.id = ALL ('{?}')", [1, 2])

answered Feb 16 '23 at 13:52

Yaroslav Sulyma

37
1
6

Rails how to find record by association's ids contain array

3 Answers3