英文:
Top 10 Sold Products with Django queryset order_by "external value"
问题
I am trying to get top 10 sold products from my models.
Models are (they are in different apps):
Products:
class Product(models.Model):
user = models.ForeignKey(Vendor, on_delete=models.CASCADE)
category = models.ForeignKey(Category, on_delete=models.CASCADE)
subcategory = models.ForeignKey(SubCategory, on_delete=models.CASCADE, blank=True, null=True)
product_name = models.CharField(max_length=100)
product_slug = models.SlugField(max_length=100, unique=True)
SoldProducts (list of products included after sold):
class SoldProduct(models.Model):
order = models.ForeignKey(Order, on_delete=models.CASCADE)
user = models.ForeignKey(User, on_delete=models.CASCADE)
product = models.ForeignKey(Product, on_delete=models.CASCADE, related_name='soldproducts')
quantity = models.IntegerField()
I have got a list with Product PK and a Sum of sold products with:
best_seller_products = SoldProduct.objects.filter(product__in=products, order__in=orders).values(
'product__pk',).annotate(qty=Sum('quantity')).order_by('-qty')[:6]
as a result of this queryset:
<QuerySet [{'product__pk': 5, 'qty': 22}, {'product__pk': 6, 'qty': 6}]>
So I made two lists. One with the product_pk and the other with qty. With the product_pk, I got a list of the products with this query:
best_seller_products = products.filter(pk__in=list_best_seller_products_pk)
The problem I would like to solve is: How to sort this products list that I got with product_pk with the qty list. I would mostly prefer to use a query instead of creating a "sold" field in the Product model.
I really appreciate any help or directions on how to do it!
Thank you very much.
英文:
I am trying to get top 10 sold products from my models.
Models are (they are in diferent apps):
Products:
class Product(models.Model):
user = models.ForeignKey(Vendor, on_delete=models.CASCADE)
category = models.ForeignKey(Category, on_delete=models.CASCADE)
ubcategory = models.ForeignKey(SubCategory, on_delete=models.CASCADE, blank=True, null=True)
product_name = models.CharField(max_length=100)
product_slug = models.SlugField(max_length=100, unique=True)
SoldProducts (list of products included after sold):
class SoldProduct(models.Model):
order = models.ForeignKey(Order, on_delete=models.CASCADE)
user = models.ForeignKey(User, on_delete=models.CASCADE)
product = models.ForeignKey(Product, on_delete=models.CASCADE, related_name='soldproducts')
quantity = models.IntegerField()
I have got a list with Product PK and a Sum of sold products with:
best_seller_products = SoldProduct.objects.filter(product__in=products, order__in=orders).values(
'product__pk',).annotate(qty=Sum('quantity')).order_by('-qty')[:6]
as result of this queryset:
<QuerySet [{'product__pk': 5, 'qty': 22}, {'product__pk': 6, 'qty': 6}]>
So I made a two lists. One with the product_pk and other with qty. With the product_pk a got a list off the products with this query:
best_seller_products = products.filter(pk__in=list_best_seller_products_pk)
The problem i would like to solve is:
How sort this products list that i got with product_pk with the qty list.
I would mostly prefer to use query instead of creating a "sold" field into Product model.
I really apreciate any help or directions on how to do it!
Thank you very much
答案1
得分: 2
你可以按如下方式排序：
from django.db.models import Sum
Product.objects.filter(soldproduct__order__in=orders).annotate(
qty=Sum('soldproduct__quantity')
).order_by('-qty')[:6]
这将仅返回在orders列表中至少有一笔销售记录的Product。如果需要包括没有销售记录的Product，可以使用以下方式：
from django.db.models import Q, Sum
Product.objects.annotate(
qty=Sum('soldproduct__quantity', filter=Q(soldproduct__order__in=orders))
).order_by(F('qty').desc(nulls_last=True))[:6]
英文:
You can order by:
<pre><code>from django.db.models import Sum
Product.objecs.filter(soldproduct__order__in=orders).annotate(
qty=<b>Sum('soldproduct__quantity')</b>
).order_by('-qty')[:6]</code></pre>
This will only return Products for which there is at least one sale in the orders list. We can also include Products with no sales with:
<pre><code>from django.db.models import Q, Sum
Product.objecs.annotate(
qty=Sum('soldproduct__quantity', <b>filter=Q(soldproduct__order__in=orders)</b>)
).order_by(F('qty').desc(<b>nulls_last=True</b>))[:6]</code></pre>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论