How to sort queryset by another table's record count in Django

PPTing 0
Viewed 30

I have a Product table and order table the Product table record product info,and the Order table record customer's purchases records

Now I want to get the products queryset and sort by the store with the most purchases in a customer's order history

Product Model

id product_name store_id store_name price .....
1 iPhone 14 1 Amazon 100 .....
2 iPhone 14 2 Shopee 1 .....
3 iPhone 12 3 Taobao 100 .....
4 iPhone 13 1 Amazon 80 .....
5 iPhone 14 3 Taobao 100 .....

Order Model

id product_id customer_id customer_name
1 1 1 Mike
2 2 1 Mike
3 4 1 Mike
4 1 2 Jhon
5 3 3 Simon

in my case,I want to get the product's queryset and sort by the store with the most purchases in a customer's order history

For example:

when customer_id is 1（Mike）,the product queryset should be like below because Mike have spent the most times on Amazon, so the ordering of products should put Amazon's products first

id product_name store_id store_name price .....
1 iPhone 14 1 Amazon 100 .....
4 iPhone 13 1 Amazon 80 .....
2 iPhone 14 2 Shopee 1 .....
3 iPhone 12 3 Taobao 100 .....
5 iPhone 14 3 Taobao 100 .....

In the same case,when customer_id is 3(Simon),the product queryset should be like below,because Mike have spent the most times on Taobao

id product_name store_id store_name price .....
3 iPhone 12 3 Taobao 100 .....
5 iPhone 14 3 Taobao 100 .....
1 iPhone 14 1 Amazon 100 .....
2 iPhone 14 2 Shopee 1 .....
4 iPhone 13 1 Amazon 80 .....

I user count and filter in Django ,code below, but it executes the result is wrong customer_id = 1 # Mike

product_set = Product.objects.annotate(count=Count('order__store_id', filter=Q(order__customer_id=customer_id, order__store__id=F('store_id')))).order_by('-count')
django python
1 Answers

I think you what you want is, if apple is a product and store 1 sold it 15 times store 2 sold it 25 times

then product apple with store id 1 comes on top

so for this:

from django.db.models import Count
Product.objects.annotate(store_count=Count('store_id')).order_by('-store_count')

or

queryset.objects.annotate(store_count=Count('store_id')).order_by('-store_count')

this will create a temporary field store_count and Count('store_id') will count product occurrences with store_id and then order it.

Rajat Jog0
Related