Convert raw SQL query to Django ORM with multiple joins

Question

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?

Davit Tovmasyan · Accepted Answer · 2018-01-17 13:17:49Z

2

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).

answered Jan 17, 2018 at 13:17

Davit Tovmasyan

3,6382 gold badges26 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ankita Over a year ago

I wrote the query with F() object in annotate, this is returning me the desired keys "level_id" and "level_name" along with "userlevel__current_sub_level_id__level_id" and "userlevel__current_sub_level_id__level__name". Is there no way to avoid these 2 long keys too?

Davit Tovmasyan Over a year ago

@AnkitaGupta yes, just remove them from values. >>print(User.objects.annotate(user_name=F('username')).values('user_name')[0]) >>{'user_name': 'davit'}

Ankita Over a year ago

This is not helping because I need to apply grouping on level_id and name. If I move .values() after annotate(), the grouping doesn't take place

MarcinEl · Accepted Answer · 2018-03-23 12:45:54Z

1

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

answered Mar 23, 2018 at 12:45

MarcinEl

2192 silver badges6 bronze badges

Collectives™ on Stack Overflow

Convert raw SQL query to Django ORM with multiple joins

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related