Get sub One To Many relation from specific model in django

Issue

I have two models User and Sale, each sale has a buyer and a seller. I would like to get all buyers (also Users) from a specific user my_user.

class User(models.Model):
    name = models.CharField(max_length=250, default='', blank=True)
    # more fields....

class Sale (models.Model):
    buyer = models.ForeignKey(
        User,
        blank=False,
        null=False,
        related_name='purchases',
        on_delete=models.CASCADE,
    )
    seller = models.ForeignKey(
        User,
        blank=False,
        null=False,
        related_name='sales', 
        on_delete=models.CASCADE,
    )
    # more fields....

This works fine:

User.objects.filter(purchases__seller = my_user)

But I would like to know if there is a way to do it by using the my_user object directly. Something like this

my_user.sales.buyer

Thanks in advance guys

Solution

There’s a big difference between your line and seokmin’s solution:

my_user.sales.values_list('buyer', flat=True)  # returns a list of ids
User.objects.filter(purchases__seller=my_user)  # returns a QuerySet

You could do an imho uglier comprehension (select_related makes sure you have all the buyer data and won’t send one query per every sale, more here) :

buyers = {sale.buyer for sale in my_user.sales.select_related('buyer')}

Fetching from multiple users

Much worse will be the case when you don’t have a single user, but multiple users, which I think will eventually be the case. You’ll have to do a bit more magic here if you want Users to come out to prevent calling the DB too many times.

Inspired by your original query would be a very simple 2 line snippet:

user_ids = my_user_qs.values_list('id', flat=True)
buyers = User.objects.filter(purchases__seller__id__in=user_ids)

Easy.


Now let’s think you wanna re-use the buyers = {sale.buyer... as a method on the user, let’s call it user.get_buyers():

buyers = [user.get_buyers() for user in my_users]

This will run 1 query to the DB for every user, and will likely be catastrophically slow with even just hundreds of users.

How do you optimise that?

First you have to define a Prefetch – that tells Django to download extra data when it originally runs your query for users.

from django.db.models import Prefetch

sales_prefetch = Prefetch('sales', Sale.objects.select_related('buyer', 'seller')
# no query runs yet

The Prefetch says "when evaluating a queryset this prefetch is applied to, fetch a Sale query at the same time and attach it to the ‘sales’ subfield. Moreover, when you’re at it, join the sales table with the user table on the buyer and seller".

Then you have to make sure that when you run the query that gets you my_users, you use this prefetch.

my_users = Users.objects.prefetch_related(sales_prefetch)

This allows you to do the following:

buyers = [user.get_buyers() for user in my_users]
# sends a single query and returns a list of lists of User objects 
# you still have to flatten the list of lists

Conclusion

Overall you’re gonna be better of with your original query through the User model. With the Prefetch you’re still running a risk of then using a related field on the user that you didn’t run prefetch/select_related on and you’ll fire a DB query for every single item in the final list.

I highly recommend reading and understanding both select_related and prefetch_related as they are Django’s way of correctly doing JOINs.

https://docs.djangoproject.com/en/4.0/ref/models/querysets/#select-related
https://docs.djangoproject.com/en/4.0/ref/models/querysets/#prefetch-related

Answered By – Jura Brazdil

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply

(*) Required, Your email will not be published