0

I am trying to get a field in a queryset which depends on the result of the annotations. I tried using extra on an annotated queryset, which doesn't seem to work.

Lets say my models looks like this:

class Foo(models.Model):
    name = models.CharField(...)

class FooData(models.Model):
    foo = models.ForeignKey(Foo)
    sales = models.PositiveIntegerField()
    items_sold = models.PositiveIntegerField()

I have a queryset of Foo objects

some_foos = Foo.objects.filter()

I have annotated it to get the sum on FooData.

some_foos.object.annotate(
    total_sales=Sum("foodata__sales"),
    total_items_sold=Sum("foodata__items_sold")
    )

Now I want to get the average price for which I am trying to use .extra. They don't seem to work. (I have looked the generated sql queries).

#Wont work, fields not defined yet.
some_foos.object.annotate(
    total_sales=Sum("foodata__sales"),
    total_items_sold=Sum("foodata__items_sold")
    ).extra(select={'price_avg': "total_sales/total_items_sold"}) 

#Wont work, this adds an extra clause in group by
price_avg = "SUM(appname_foodata.sales)/SUM(appname_foodata.items_sold)"
some_foos.object.annotate(
    total_sales=Sum("foodata__sales"),
    total_items_sold=Sum("foodata__items_sold")
    )
shabda
  • 1,668
  • 1
  • 18
  • 28
  • Would you be using MySQL? I've found this comment and i think it's related to your problem. [link](http://stackoverflow.com/questions/4348124/django-orm-filter-by-extra-attribute#comment4792398_4348728) – J. Ghyllebert Apr 16 '13 at 09:13
  • I have tested this with sqlite and mysql. Neither worked. – shabda Apr 16 '13 at 09:41
  • My guess is that if it doesn't work for MySQL, it won't work for sqlite either. – J. Ghyllebert Apr 16 '13 at 09:44

0 Answers0