CODEWITHSUNDEEP
djangodjango-modelsdjango-select-related
thiag0ms
thiag0ms

Reputation: 17

Django, Select related, Average, ManyToMany field

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?

Upvotes: 0

Views: 1413

Answers (2)

Gers
Gers

Reputation: 672

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

Upvotes: 0

Ozkar L. Garcell
Ozkar L. Garcell

Reputation: 1

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

Upvotes: 0

Related Questions