Django, Select related, Average, ManyToMany field

Question

Suppose i have clients in my model (model Client), and suppose each client has a shopping cart (model Cart). Each cart has many items (model CartItems), then to finish, each item has a relation with one product (model Product).

Now, here goes my problem. I want to have the average of all shopping carts, which is based in the sum of all items of each cart, for each client. So i'll try to demonstrate you with more details below.

Try to imagine the directions of the relations like this: Client->Cart->CartItems<-Product

Simplified description of each model:

Client (
  id #pk
)

Cart (
  id #pk
  client_id #fk that references to Client.id
)

CartItems (
  id #pk
  cart_id #fk that references to Cart.id
  product #fk that references to Product.id
)

ProductId (
  id #pk
  value # The price of the product
)

In pure SQL i've found the solution, and would be something like this query:

SELECT * FROM Client
INNER JOIN Cart ON Client.id = Cart.client_id
INNER JOIN 
(SELECT AVG(c.total) AS average, cart_id FROM
    (SELECT SUM(Product.price) AS total, CartItems.cart_id AS cart_id
    FROM CartItems
    INNER JOIN Product ON CartItems.product = Product.id
    GROUP BY CartItems.cart_id) AS c GROUP BY c.cart_id) AS d
ON d.cart_id = Cart.id;

Anyone has any idea about how to convert this query to Django's model's patterns?

The answer is don't bother. Just use the raw query. There is no reason why you shouldn't (except for the fact the if you iterate the queryset multiple times the query will be executed each time) — e4c5, Aug 05 '16 at 13:48
But doing this can i use the Paginator normally, exactly like if it was a queryset? — thiag0ms, Aug 06 '16 at 05:32
you are right, the default paginators cannot be used with rawqueryset — e4c5, Aug 06 '16 at 05:33

score 0 · Answer 1 · answered Aug 05 '16 at 19:47

0

You sould do something like:

Cart.objects.values('client_id').annotate(cant=Sum('cart_id__product__value')).aggregate(Avg('cant'))

Notice that the annotations does not return a queryset, you should return the values.

Further reading: Aggregations in Django

answered Aug 05 '16 at 19:47

Ozkar L. Garcell

1
1

Client.objects.select_related().order_by(order_by).annotate(total=Sum( F('cadcp__cpi__product__value') * F('cadcp__cpi__quantity'), output_field=models.DecimalField() )).extra(select={'n_cart':'COUNT(DISTINCT `cart`.`id`)'}). With this code i could retrieve the price total of each shopping cart and the number of shopping cart by client, so i can do total/n_cart and get average, but this way i can't sort by average. Any idea how to solve this? Thanks for you answer, it helped a lot. – thiag0ms Aug 06 '16 at 05:29

score 0 · Answer 2 · answered Jan 03 '22 at 14:07

My answer comes very late but I came accross this while searching for a solution to a similar issue.

In my case, I have a ManyToManyField relationship between the Cart and the CartItems, which made it fairly simple.

The models now look like this

Cart (
  id #pk
  client_id #fk that references to Client.id
  items #manytomany relationship to CartItems
)

CartItems (
  id #pk
  product #fk that references to Product.id
)

To get the average for each cart, the query would just look like this

Cart.objects.annotate(carts_avg=Avg('items__product__value'))

Django, Select related, Average, ManyToMany field

2 Answers2