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 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

Solution

You can order by:

from django.db.models import Sum

Product.objecs.filter(soldproduct__order__in=orders).annotate(
    qty=Sum('soldproduct__quantity')
).order_by('-qty')[:6]

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:

from django.db.models import Q, Sum

Product.objecs.annotate(
    qty=Sum('soldproduct__quantity', filter=Q(soldproduct__order__in=orders))
).order_by(F('qty').desc(nulls_last=True))[:6]