[Answer]-How to translate this raw query to Django ORM

by

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'))
👤Mp0int

0👍

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')
👤Daniel Roseman

Leave a comment