# How do I find a records based on contents of a has_many :through join?

I think I've been at this problem for too long and the obvious answer has evaded me.

Say you have students, classes (klass for ruby's sake), and enrollements as the join between them.

```
class Student < ApplicationRecord
has_many :enrollments
has_many :klasses, through: enrollments
end
class Enrollment < ApplicationRecord
belongs_to :student
belongs_to :klass
end
class Klass < ApplicationRecord
has_many :enrollments
has_many :students, through: enrollments
end
```

So far just a simple many-to-many.

Let's give some data and a scenario.

**Students:**

- Alice
- Bob
- Charlie

**Classes:**

- Math
- Science
- Literature
- Phys Ed.

Alice is enrolled in Math, Science, and Phys Ed.

Bob is enrolled in Math, Science, and Literature.

Charlie is enrolled in Science and Phys Ed.

How could I construct a query to say "Who is enrolled in Math & Science?" and have the query return unique records "Alice & Bob".

I started going down the path of something like this, but keep getting tripped up somewhere:

```
Student.joins(:enrollments).joins(:klasses).where(klass: { id: [math.id, science.id] }).uniq
```

But since Charlie is enrolled in Science as well, he gets thrown into my results.

Again, I think I've overthought it and I am doing something insanely stupid. At this point I am assuming the answer is probably clear to everyone but me 🤣

Thanks for your help!

I think the basic issue is that you want an `AND`

query, but what you're getting is an `OR`

query. IOW, the query may actually be 'where klass.id is math.id OR science.id' .

I think it's doing a 'where klass.id IN (math.id, science.id)...' but I don't have time to check right now.

Hm. This is straightforward if you use `scopes`

but I'm not sure that's what you're looking for.

```
class Student < ApplicationRecord
has_many :enrollments
has_many :klasses, through: :enrollments
scope :all_in_klass, ->(k){ joins(:klasses).joins(:enrollments).where(enrollments: {klass: k} ).distinct }
end
```

then:

```
in_math = Student.all_in_klass(math)
=> Student Load (0.2ms) SELECT DISTINCT "students".* FROM "students" INNER JOIN "enrollments" ON "enrollments"."student_id" = "students"."id" INNER JOIN "klasses" ON "klasses"."id" = "enrollments"."klass_id" INNER JOIN "enrollments" "enrollments_students" ON "enrollments_students"."student_id" = "students"."id" WHERE "enrollments"."klass_id" = ? LIMIT ? [["klass_id", 1], ["LIMIT", 11]]
#<ActiveRecord::Relation [#<Student id: 1, name: "Alice", created_at: "2019-02-22 00:05:20", updated_at: "2019-02-22 00:05:20">, #<Student id: 2, name: "Bob", created_at: "2019-02-22 00:05:29", updated_at: "2019-02-22 00:05:29">, #<Student id: 3, name: "Charlie", created_at: "2019-02-22 00:05:36", updated_at: "2019-02-22 00:05:36">]>
in_sci = Student.all_in_klass(science)
Student Load (0.2ms) SELECT DISTINCT "students".* FROM "students" INNER JOIN "enrollments" ON "enrollments"."student_id" = "students"."id" INNER JOIN "klasses" ON "klasses"."id" = "enrollments"."klass_id" INNER JOIN "enrollments" "enrollments_students" ON "enrollments_students"."student_id" = "students"."id" WHERE "enrollments"."klass_id" = ? LIMIT ? [["klass_id", 2], ["LIMIT", 11]]
=> #<ActiveRecord::Relation [#<Student id: 1, name: "Alice", created_at: "2019-02-22 00:05:20", updated_at: "2019-02-22 00:05:20">, #<Student id: 2, name: "Bob", created_at: "2019-02-22 00:05:29", updated_at: "2019-02-22 00:05:29">]>
in_math_and_sci = in_math & in_sci
=> [#<Student id: 1, name: "Alice", created_at: "2019-02-22 00:05:20", updated_at: "2019-02-22 00:05:20">, #<Student id: 2, name: "Bob", created_at: "2019-02-22 00:05:29", updated_at: "2019-02-22 00:05:29">]
```

Well well well! That passed one of my Rspec tests but not the other! (So much further than I got before so thank you so much!)

Test 1 basically finds **Charlie** by passing in **Phys Ed** and **Science** eliminating the other two students.

Test 2 finds Alice and Bob as described above. But only Alice gets put out because of what I imagine is the `distinct`

?

Sorry, this is a little confusing because I am translating my client's problem to a different domain of school children so may be unclear when testing your approach and returning the results!

HEY I GOT BOTH TESTS TO PASS!! THANKS SO MUCH ASHLEY!

Current solution until I find a better way to refactor it (and hopefully into one query since this method will likely be called a lot)...

```
class Student < ApplicationRecord
has_many :enrollments
has_many :klasses, through: :enrollments
scope :all_in_klass, ->(k){ joins(:enrollments).where(enrollments: {klass: k} )}
end
```

```
def find_students_who_share_classes
in_math = Student.all_in_klass(@math)
in_sci = Student.all_in_klass(@sci)
in_math.merge(in_sci)
return in_math.distinct
end
```

Glad to have helped! :-)

(Some folks would rather write that scope as a class method, of course)

If the number of "classes" varies, then you can call that scope/method as many times as needed, of course.

And ya -- I know what it's like to have to translate a domain/problem so that you can talk about it, while not losing any important information in translation either way.

Ugh... turns out this isn't solved! I am getting all kids enrolled in science even if they don't take math!

I added more data and tested a few more scenarios and found out it wasn't working properly.

I will return with an update, but until then, if anyone has any recommendations I'd love to hear them!

Peter --

I didn't notice it before, but in your code:

```
def find_students_who_share_classes
in_math = Student.all_in_klass(@math)
in_sci = Student.all_in_klass(@sci)
in_math.merge(in_sci)
return in_math.distinct
end
```

you're using a `merge`

which will give you an AND and not the OR that you want.

I think I finally figured it out. Took long enough but I figured I'd share it.

```
class Student < ApplicationRecord
has_many :enrollments
has_many :klasses, through: :enrollments
scope :all_in_klass, ->(klasses) {
joins(:enrollments)
.where(enrollments: {klass_id: klasses} )
.having('COUNT(*) = ?', klasses.size)
.group(:id)
}
end
Student.all_in_klass([@sci.id, @math.id])
```

So the trick I have here is to query the join table for matching ID's (does with `WHERE IN`

when passing arrays), then group them by the student ID and avoid duplicates. Before grouping, the `HAVING`

clause filters out results which don't share the amount of classes in the array (two classes in this case).

Further testing required as I am worried about that last `HAVING`

clause. Not entirely sure I have a good understanding of how the SQL works but RSpec is all green so I am going with it.