Django Subquery returns only the first element

Question

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

I think the way to do this is without the subquery as answered below by Daniel Roseman. Just for a little more info, [here's the django doc](https://docs.djangoproject.com/en/2.1/ref/models/expressions/#using-aggregates-within-a-subquery-expression) describing the _only_ way to do aggregation on a subquery. I think your approach is getting messed up because of the order of evaluation. — RishiG, Nov 06 '18 at 18:29

score 3 · Accepted Answer · answered Nov 06 '18 at 17:26

3

You don't need a subquery here.

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

answered Nov 06 '18 at 17:26

Daniel Roseman

588,541
66
880
895

There is a weird issue, as soon as I add another annotation except points_sum (ie, points_sum2), it doubles the value of points_sum for no apparent reason. Any ideas how to resolve that? – panosl Nov 07 '18 at 14:59

Django Subquery returns only the first element

1 Answers1