Django models - self join on both FK in a M2M scenario using QuerySet

Question

Let's take following M2M scenario.

I want to get all the colleagues of any given student, and the number of courses both of them attend. (Meaning how many courses the given student has in common with each and any 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 would 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'd appreciate any hint on how to accomplish this using QuerySet methods.

Thanks!

Answer 1

Well, here's an interesting idea: how about creating a db view with that query and a model onto the view?