Group By Django ORM

Question

I want to do a simple group by with django ORM.

SELECT * FROM user AS u JOIN user_photo AS up WHERE u.`user_id` = up.`user_id` GROUP BY up.`user_id`;

Structure user_photo: id | photo | user_id

Structure user: id | name | status | description | paid | user_id

I have another table with user photos that users upload to the database. I can pull the informations from the user but I want only one and the last photo that the user uploaded from the user photo table.

I want to group by, by my field not using any model function like count, max, min, avg, etc

Anyone can help?

Answer 1

Technically, you do a GROUP BY with .group_by(), but it's not exactly what you are looking for.

You can achieve your goal with the ORM using a prefetch_related.

The Prefetch objects allow you to do all kind of lookup using the ORM.

In your case :

User.objects.prefetch_related(
    Prefetch(
        'photos',
        queryset=Photo.objects.last(),
    )
)

Where 'photos' is you related_name or 'photo_set' by default. Also, the .last() method can be replaced by the .order_by() of your choice.

Take note that it will differs from your SQL example in the fact that the prefetch will do an additional query instead of a JOIN.