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.