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
CodePudding user response：
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
CodePudding user response：
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.
CodePudding user response：
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.