CODEWITHSUNDEEP
djangodjango-aggregationdjango-annotate
panosl
panosl

Reputation: 1789

Django Subquery returns only the first element

This is a simplified version of the models:

class Toy(models.Model):
    #generic fields


class Order(models.Model):
    customer = models.ForeignKey(Customer)


class OrderItem(models.Model):
    order = models.ForeignKey(Order)
    toy = models.ForeignKey(Toy)
    points = models.PositiveSmallIntegerField(default=3)

A customer can make multiple orders, to it increases the number of points per toy.

This subquery, only returns the first row of OrderItem:

class Customer(models.Model):

    def get_toys_with_points():
        order_items = OrderItem(toy=models.OuterRef('pk'), order__customer=self)

        toys = Toy.objects.annotate(
            points_sum = models.Sum(Subquery(order_items.values('points')))
        )

        return toys

So, when I pull that into my template:

{% for toy in customer.get_toys_with_points %}
    {{ toy.points_sum }}
{% endfor %}

I am always getting the value of the first row (even if there are more purchases that would sum up to 25 for example).

Upvotes: 0

Views: 700

Answers (1)

Daniel Roseman
Daniel Roseman

Reputation: 600059

You don't need a subquery here.

toys = Toy.objects.filter(orderitem__order__customer=self).annotate(
    points_sum=models.Sum('orderitem__points')
)

Upvotes: 3

Related Questions