CODEWITHSUNDEEP
pythonmysqldjangodjango-orm
Ankita
Ankita

Reputation: 165

Convert raw SQL query to Django ORM with multiple joins

I have this raw query which I need to rewrite in Django's ORM:

SELECT count(u.username) as user_count, l.id as level_id, l.name as level_name
FROM users u
JOIN user_levels ul ON ul.username = u.username
JOIN sub_levels sl ON sl.id = ul.current_sub_level_id
JOIN levels l ON l.id = sl.level_id
WHERE u.created_at::DATE >= '2018-01-01' AND u.created_at::DATE <= '2018-01-17' AND u.type = 'u'
GROUP BY l.id, l.name

So far I have been able to write it in this way:

Users.objects.select_related('user_levels', 'sub_levels', 'levels')
.filter(created_at__date__gte='2018-01-01', created_at__date__lte='2018-01-17', type='u')
.values('userlevel__current_sub_level_id__level_id', 'userlevel__current_sub_level_id__level__name')
.annotate(user_count=Count('username'))

The output has "userlevel__current_sub_level_id__level_id" and "userlevel__current_sub_level_id__level__name" columns. I need them aliased as "level_id" and "level_name".

How can I do that?

Upvotes: 0

Views: 1508

Answers (2)

MarcinEl
MarcinEl

Reputation: 219

Using F() expression is suitable in this context. Try this:

from django.db.models import F

Users.objects.select_related('user_levels', 'sub_levels', 'levels')
.filter(created_at__date__gte='2018-01-01', created_at__date__lte='2018-01-17', type='u')
.annotate(level_id=F('userlevel__current_sub_level_id__level_id'), level_name=F('userlevel__current_sub_level_id__level__name'))
.values('level_id', 'level_name')
.annotate(user_count=Count('username'))

Upvotes: 1

Davit Tovmasyan
Davit Tovmasyan

Reputation: 3588

Have you tried F() expressions ?

You can use it in this way.

from django.db.models import F

queryset.annotate(final_name=F('selected_name'))

For more information check this.

Don't forget to change the values with final_name(s).

Upvotes: 2

Related Questions