Django models - self-join FK in M2M script using QuerySet

Question

Take the following M2M scenario.

I want to get all the colleagues of any student, as well as the number of courses in which they participate. (This means how many courses this student has in common with each and every one of his colleagues.)

class Student(models.Model):
    pass

class Course(models.Model):
    students = models.ManyToManyField(Student, through='Attendance')

class Attendance(models.Model):
    student = models.ForeignKey(Student)
    course = models.ForeignKey(Course)

The query will look something like this:

SELECT 
    S.id AS student_id, 
    A2.student_id AS colleague_id, 
    COUNT(A2.course_id) AS number_of_courses_both_of_them_attend
FROM student S
JOIN attendance A1 
    ON S.id = A1.student_id
JOIN attendance A2 
    ON (A1.course_id = A2.course_id AND A1.student_id != A2.student_id)
GROUP BY 1, 2

I would be grateful for how to do this using the QuerySet methods.

Thanks!

Answer 1

colleagues = Student.objects.filter(course_set__students=your_student).exclude(id=your_student).distinct()

Add classes_cnt property for each student:

colleagues = colleagues.annotate(courses_cnt = Count('attendance_set'))