Get sub One To Many relation from specific model in django

Question

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

score 1 · Answer 1 · answered May 05 '22 at 08:43

You can access the sales and buys of a specific user like this:

q = my_user.sale_set.all()

or

q = my_user.sale_set.filter(your fileter)

then you can access buyer or seller using :

buyer = q.buyer 

seller = q.seller

but be aware of using q.last() cause you can't access buyer or seller on a list of query sets .

i hope this helped enough.

Jura Brazdil · Accepted Answer · 2022-05-05T11:40:22.943

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

seokmin-kang · Answer 3 · 2022-05-05T10:08:55.963

~~you can use django values_list~~

~~https://docs.djangoproject.com/en/dev/ref/models/querysets/#values-list~~

~~so, you can do this by using below code~~

buyer_list = my_user.sales.values_list('buyer', flat=True)

~~Additional, if you want to remove duplicate value, Find distinct option.~~

~~https://docs.djangoproject.com/en/4.0/ref/models/querysets/#distinct~~

Someone commends that values_list option brings only value not a object. Thanks for commend.

So, I find select_related option.

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

If you want to use select_related, maybe this example which is similar to django document works

buyers = set()
for sale in my_user.sales.select_related('buyer').all():
  buyers.add(sale.buyer)

another solution is using 'values' method. but It has to write all user columns you want to use.

How to combine select_related() and value()? (2016)

`values` just returns a list of dicts as in: `>>> my_user.sales.values('id', 'username')` -> `[{'id': 1, 'username': 'bob_burgers'}, {'id': 2, 'username': 'frank_dog'}]` — Jura Brazdil, May 05 '22 at 11:35
Gabriel's original query through the `User` model is still the best solution. — Jura Brazdil, May 05 '22 at 11:36

Get sub One To Many relation from specific model in django

3 Answers3