How do I get this distant relationship in a query?

Ruby on Rails
1

In my app I have students who belong to school classes via class memberships and there are teachers who teach school classes via timeslots.

Students and teachers are each end of a fairly long set of relationships:

Student < > Class_Membership < > School_Class < > Timeslot < > Teacher

I can call @student.teachers and receive all the student’s teachers for all classes they are in.

I’m trying to write a query to return all the students who have zero teachers, pseudo-code:

@unassigned_students = Student.where(teachers.count is 0)

I’m having trouble navigating what to include in the query and how to reach across so many relationships. Or am I making it too complicated?

Basic relationships:

class Student < ActiveRecord::Base 
  ..
  has_many :class_memberships
  has_many :school_classes, through: :class_memberships
  has_many :teachers, through: :school_classes
  ...
end

class Teacher < User
  ...
  has_many :timeslots
  has_many :school_classes, through: :timeslots
  has_many :students, through: :school_classes
  ...
end

class SchoolClass < ActiveRecord::Base
  ...
  has_many :class_memberships
  has_many :students, through: :class_memberships
  has_many :timeslots
  has_many :teachers, through: :timeslots
  ...
end
2

Dan did anyone help you with this? If not I may have a crack at it. Interesting problem.

3

The core unit of your system is the School_Class, right? I’m trying to think of why you would need to know that a student has zero teachers vs needing to know that he is in zero classes.

4

Hi @pdbradley , I’m still working on this. I should tell you there are a few complications that have stumped me along the way:

  1. Every student has a private class by default (for one-to-one learning) and that private class can have many teachers. Students can also belong to group classes, which can also be taught my multiple teachers. So I can’t just look for student’s without any class memberships. I could look for students in only one class and check that class is a private one but that seems equally complicated.

  2. Teacher is inherited from the main Devise user model. Not sure if this is a complication, just mentioning for clarity.

  3. The app is multi-tenanted using default_scope and a school_id. This makes working with queries in the console umm… challenging.

The only working solution I have is this:

Student.all.select {|s| s.teachers.length == 0 }

but as you can tell, that queries the teacher count for every student which is quite a performance hit.

Each school only has hundreds of students, not thousands or hundreds of thousands, so it may be feasible to use this but it does smell quite bad.

5

Dan, what about this, in the second it block. Try it and see if it works. This test passes for me.

Basically if you include the farthest association, the query will contain a series of left outer joins, and you can pick an associated column in one of the joined tables; if it is nil, then the table contains no records in the join and so your originating model is “orphaned” in the sense of having no associated records.

So by finding records (Students) for which the teacher_id in timeslot is nil, you are finding Students without any teachers. Clunky but maybe it is enough. Here is the repo:

git@github.com:pdbradley/danweaver.git

require 'rails_helper'

describe "Finding absent associations" do
  it "finds students with no associated schoolclass" do
    aclass = Schoolclass.create(name: "Fourth Period")
    s1 = Student.create(name: "Phil")
    s2 = Student.create(name: "Dan")
    aclass.students << s1

    expect(Student.includes(:class_memberships).
           where(class_memberships: {student_id: nil})).to eq [s2]
  end

  it "finds students with no teachers" do
    aclass = Schoolclass.create(name: "Fourth Period")
    s1 = Student.create(name: "Phil")
    s2 = Student.create(name: "Dan")
    t1 = Teacher.create(name: "Mcgillicudy")

    aclass.students << s1
    aclass.teachers << t1

    expect(Student.includes(:teachers).
           where(timeslots: {teacher_id: nil})).to eq [s2]

  end

end
1 Like
6

@pdbradley by jove, that’s it! It does exactly what I need. It doesn’t seem clunky at all to me.

The closest I previously got along this route was:

scope :teacherless, includes(:timeslots).where(:timeslots => {:student_id => nil})

where you can see I was trying to work with timeslots to get what I want, but you nailed it!

You solution works perfectly. Thanks so much for putting your time into this. This not only solved my current problem but has armed me with the knowledge to solve future problems like this myself.

:beers:

7

You know what @weavermedia it makes more sense to do this:

Student.includes(:teachers).where(teachers: {id: nil})

Which gives the same result. that way you are checking for the existence of a column on the teachers table and it is a little bit clearer what you are doing.

8

@weavermedia sigh. I turned it into a small blog post (following Ben O’s advice about blogging to help find work)

http://philipbradley.net/finding-records-with-no-associated-records-via-has-many-through-in-rails-4/

1 Like
9

Your shorter solution looks good but fails for me, I assume because of the the Teacher < User inheritance I mentioned earlier.

scope :teacherless, includes(:teachers).where(teachers: {id: nil})

ActiveRecord::StatementInvalid:
  PG::UndefinedTable: ERROR:  missing FROM-clause entry for table "teachers"
  LINE 1: ...acher_id" AND "users"."type" IN ('Teacher') WHERE "teachers"...

That’s actually one of the first things I tried and got stumped when it wouldn’t let me reference teachers in the query.

I can’t figure out how to include teachers as users. I read the APIDock page but includes doesn’t seem to have options like as:.

Any ideas? I’m fine with the slightly longer timeslots solution but I’d still like to know how to reference an STI model like this in a query.

Great blog post by the way :smile:

10

hey @weavermedia does this work?

scope :teacherless, includes(:teachers).where(users: {id: nil})