0

Let's say have Student and a Teacher models:

class Student < ApplicationRecord
  has_and_belongs_to_many :teachers
end

class Teacher < ApplicationRecord
  has_and_belongs_to_many :students
end

and the DB schema looks something like this

create_table "students", force: :cascade do |t|
  t.datetime "created_at", null: false
  t.datetime "updated_at", null: false
end

create_table "students_teachers", id: false, force: :cascade do |t|
  t.bigint "student_id", null: false
  t.bigint "teacher_id", null: false
  t.index ["student_id", "teacher_id"], name: "index_students_teachers_on_student_id_and_teacher_id"
  t.index ["teacher_id", "student_id"], name: "index_students_teachers_on_teacher_id_and_student_id"
end

create_table "teachers", force: :cascade do |t|
  t.datetime "created_at", null: false
  t.datetime "updated_at", null: false
end

and I have seeded the DB with following:

students = Student.create([{}, {}, {}, {}])
teacher_1 = Teacher.create
teacher_2 = Teacher.create
teacher_3 = Teacher.create

teacher_1.students << [students[0], students[1]]
teacher_2.students << students[1]

Now I want to get all teachers which don't have students[0]. But when I run 

Teacher.includes(:students).where.not(students: {id: students[0].id})

it returns teacher_1 and teacher_2. Is there any way to get all teachers which don't have the specified student? (teacher_2 & teacher_3 in this case).

I also tried 

Teacher.includes(:students).where("students.id != ? OR students.id IS NULL", students[0].id).references(:students)

but it returns all three teachers.

I tried hard finding anything that can help me on the internet but no luck so far :(

Update 1:

As @ruby_newbie commented below, the query results includes teacher_1 because teacher_1 have one other student (student[1]) but I want to get all teachers which do not have a particular student say student[0] no matter if they have other students.

rkb
  • 514
  • 2
  • 9
  • 19
  • 1
    what is the output if you do `teacher_3 << students[0]`? I think your query is working but that because both teacher_1 and teacher_2 have student_1 they both return as results. – ruby_newbie Jan 10 '18 at 20:52
  • I know that but is there any way to return teachers which don't have student[0] no matter they have another student or not. – rkb Jan 11 '18 at 07:31
  • I am not sure it will work or not I found something similar https://stackoverflow.com/a/11279406/3759158 – Manishh Jan 11 '18 at 10:41
  • Teacher.joins(:students).where("students_teachers.student_id is NULL OR students_teachers.student_id <> ?", students[0].id) – Manishh Jan 11 '18 at 11:02
  • @Manishh both `<>` and `!=` operators do the same thing :) https://stackoverflow.com/a/723426/2902497 – rkb Jan 12 '18 at 06:34
  • 1st, this isn't really a rails/ruby question - it's just a SQL question. Once you realize it's a SQL/db question - the answer is always let your tools do what they were made for & get out of their way! You can just do an `.where` with inverse lookup with SQL, ...NOT activerecord - https://stackoverflow.com/questions/19682816/sql-statement-select-the-inverse-of-this-query. 2nd,relationship - ditch the HAB2M relationship. Go with `has_many` paired with `has_many: :through` setup per rails guide & then you could do a rails call from student looking for teacher records not having student – Mirv - Matt Jan 15 '18 at 12:56
  • you can try with `Teacher.joins('LEFT OUTER JOIN students_teachers ON students_teachers.teacher_id = teachers.id').where(students_teachers: { student_id: students[0], teacher_id: nil })` – mr_sudaca Jan 15 '18 at 14:34
  • @mr_sudaca it doesn't work either. Returns no results. Here is the SQL it formed `SELECT "teachers".* FROM "teachers" LEFT OUTER JOIN students_teachers ON students_teachers.teacher_id = teac hers.id WHERE "students_teachers"."student_id" = 1 AND "students_teachers"."teacher_id" IS NULL LIMIT $1 [["LIMIT", 11]]` – rkb Feb 09 '18 at 18:03

0 Answers0