How to translate this raw query to Django ORM

Question

I have a model where we have candidates, languages and language Level entities:

class Candidate(models.Model):
  user = models.ForeignKey(MyUser)
  telephone = models.CharField(max_length=20, unique=True)
  postcode = models.CharField(max_length=10)

class Language(models.Model):
  name = models.CharField(max_length=50)

class LanguageLevel(models.Model):
  candidate = models.ForeignKey(Candidate)
  language = models.ForeignKey(Language)
  level = models.IntegerField(max_length=1, choices=LANGUAGE_LEVEL, default=0)

My raw query is:

SELECT c.id, COUNT(c.id) as total
FROM candidates as c
  JOIN language_level as ll ON ll.candidate_id=c.id
  JOIN languages as l ON ll.language_id=l.id
WHERE ((ll.level >=1 AND l.id = 1 ) OR
       (ll.level >=1 AND l.id = 2 ) OR
       (ll.level >=1 AND l.id = 3 ) OR
       (ll.level >=3 AND l.id = 4 ) OR
       (ll.level >=3 AND l.id = 5 ) OR
       (ll.level >=2 AND l.id = 6 ))
GROUP By c.id

I need to be able to sort the results by the the total field, and I also need to be able to display that total in the template. Somehow I need to create a list of bigger objects that combines those 3 entities + the group by.

Any idea what's the best approach? I tried getting the ids of the candidates and do just a Candidates.objects.find(id__in = ids) but there is no way later to sort this.

Thanks in advance

Answer 1

Candidate.objects.filter(
    Q(Q(languagelevel__language__id__in=(1, 2, 3)) & Q(languagelevel__language__level__gte=1)) |
    Q(Q(languagelevel__language__id__in=(4, 5)) & Q(languagelevel__language__level__gte=3)) |
    Q(Q(languagelevel__language__id=6) & Q(languagelevel__language__level__gte=2))
)).values('id').annotate(total=Count('id'))

values will only return the id of each candidate with a calculated total as total.

If you want to get all of the fields (not only id), you can remove .values('id') as:

Candidate.objects.filter(
    Q(Q(languagelevel__language__id__in=(1, 2, 3)) & Q(languagelevel__language__level__gte=1)) |
    Q(Q(languagelevel__language__id__in=(4, 5)) & Q(languagelevel__language__level__gte=3)) |
    Q(Q(languagelevel__language__id=6) & Q(languagelevel__language__level__gte=2))
)).annotate(total=Count('id'))

Answer 2

I don't really see how your SQL does what you describe in your comment. However, this code does something similar to that description.

from django.db.models import Max
Candidate.objects.filter(
    languagelevel__language__name__in=['English', 'Spanish'],
    languagelevel__level__gte=2
).annotate(max_level=Max('languagelevel__level')).order_by('max_level')