Selfjoin in Django to get list of combinations and their number

Viewed 11

unfortunately I am missing the right idea right now, so I am trying my first post here:

My model:

class MyModel(models.Model):
    interesting_value = PositiveIntegerField()
    other_value2 = PositiveIntegerField()
    other_value3 = PositiveIntegerField()
    ...

Now I want to do something like this in django:

with query as (select interesting_value, other_value2, other_value3 from mymodel)

select q1.interesting_value, q2.interesting_value, count(*) AS cnt from query q1
inner join query q2 on q1.other_value2 = q2.other_value2
                   and q1.other_value3 = q2.other_value3
                   and q1.interesting_value <> q2.interesting_value
                   and q1.interesting_value < q2.interesting_value
group by q1.interesting_value, q2.interesting_value;

To get such an result which finds the number of pairs under the given conditions:

q1.interesting_value, q2.interesting_value, cnt 
1,4,2
1,9,3
1,38,1
1,100,1

How could I realize this selfjoin in Django without using pure SQL in my app?

To get a idea of the question I want to answer, this example: How often is the combination of two of exams (=interesting_value) taken by one student (=other_value2) in one semester (=other_value3)?

I would be really thankful for a little hint to the solution!

Jan

0 Answers
Related