How can I convert Inner Join and Group By to Django ORM?

Question

I want to change this raw SQL to Django ORM but I couldn't manage to convert.

most_read_students = LendedBook.objects.raw('
SELECT student_id as id, name, surname, COUNT(*) as count 
FROM "Book_lendedbook" 
INNER JOIN User_student on Book_lendedbook.student_id=User_student.id 
where did_read=1 
group by student_id 
order by count DESC 
LIMIT 5')`

I tried this and I get close result.But unfortunately, I couldn't do what I want. Because I want to join this table with another table.

most_read_students = LendedBook.objects.values('student_id')
.filter(did_read=True, return_date__month=(datetime.datetime.now().month))
.annotate(count=Count('student_id'))

When I use select_related with "User_student" table like this;

most_read_students = LendedBook.objects.select_related('User_student')
.values('student_id', 'name', 'surname')
.filter(did_read=True, return_date__month=(datetime.datetime.now().month))
.annotate(count=Count('student_id'))

It throws an error like Cannot resolve keyword 'name' into field. Choices are: book, book_id, did_read, id, is_returned, lend_date, return_date, student, student_id

But I should be able to get student properties like name and surname when I join "User_student" table.

Thank you for your help!

score 1 · Answer 1 · answered Apr 28 '21 at 10:59

I solved it! How to combine select_related() and value()? (2016)

Funny fact; my problem was not about ORM i guess. I didn't know I could reach student object's properties by just adding this to .values('student__name', 'student__surname') in the last code I've shared on this post.

This code ;

 LendedBook.objects.select_related('User_student')
.values('student_id', 'name', 'surname')
.filter(did_read=True, return_date__month=(datetime.datetime.now().month))
.annotate(count=Count('student_id'))

To this code ;

LendedBook.objects.select_related('User_student')
.values('student_id', 'student__name', 'student__surname')
.filter(did_read=True, return_date__month=(datetime.datetime.now().month))
.annotate(count=Count('student_id'))

By the way, deleting .select_related('User_student') doesn't affect the result.

So, using _ _ solved my problem!

How can I convert Inner Join and Group By to Django ORM?

1 Answers1