Django "extra" query

Question

I have the following model structure in my models.py, with Items and Offers:

class Item(models.Model):

    STATUS_CHOICES = (
        ('A', 'Active'),
        ('C', 'Cancelled'),
    ) 

    status = models.CharField(max_length=11, choices=STATUS_CHOICES)
    target_amount = models.PositiveIntegerField()
    ...

class Offer(models.Model)

    STATUS_CHOICES = (
        ('A', 'Active'),
        ('C', 'Cancelled'),
    ) 

    status = models.CharField(max_length=11, choices=STATUS_CHOICES)
    amount = models.PositiveIntegerField()
    item = models.ForeignKey(Item)
    ...

I want to write a query in my views.py to get the following for each Item:

The total of the current (active) offers for this item
The percentage of the target that has been met by offers for this item (i.e. [the total from (1)]/target_amount * 100)

I have been trying to achieve this with "extra" as follows (using postgres):

items = Item.objects.filter(status='A').extra(
    select={
        'total_amount' : 'select coalesce(sum(amount),0) from myapp_offer where myapp_offer.item_id = myapp_item.id and myapp_offer.status = \'A\'',
        'percentage_met' : '(((select coalesce(sum(amount),0) from myapp_offer where myapp_offer.item_id = myapp_item.id and myapp_offer.status = \'A\') / target_amount) * 100)'
    }
)

Now the first select (total_amount) is working correctly and returns what I expect, but the percentage_met is always 0. Can anyone help me out as to why?

Edit: Should have mentioned that I want to be able to order_by the percentage_met

Chris Pratt · Accepted Answer · 2012-06-05T19:49:20.413

1

You don't need extra for this. Just use aggregation:

from django.db.models import Sum

items = Item.objects.filter(status='A').annotate(total_amount=Sum('offer__amount'))

percentage_met is per object, anyways, so that can just be a method on your model:

class Item(models.Model):
    ...
    @property
    def percentage_met(self):
        if hasattr(self, 'total_amount'):
            return (self.total_amount / float(self.target_amount)) * 100
        return None

edited Jun 05 '12 at 19:49

answered Jun 05 '12 at 19:37

Chris Pratt

232,153
36
385
444

This is true, but the trouble is I want to order_by percentage_met.. just tried this and I don't seem to be able to order_by a property :/ – JimJay Jun 05 '12 at 20:06
Ah ok, not to worry.. have worked out how to sort this using sorted() as per http://stackoverflow.com/questions/981375/using-a-django-custom-model-method-property-in-order-by – JimJay Jun 05 '12 at 20:27

score 1 · Answer 2 · answered Jun 05 '12 at 19:38

I don't think the code above is the cleanest way to get the value you want, but, assuming for a moment that it is-- I suspect the issue you are seeing is actually a problem with the division in SQL-- an int/int getting floored to the nearest int, which in this case is zero. Sum is certainly an int; what is target_amount, and where is it coming from? Is it an int?

In Postgres:

select 2/100; 
?column? 
----------
        0
(1 row)

If so, cast one of the values:

select 2::numeric/100;
?column?        
------------------------
 0.02000000000000000000
(1 row)

This was exactly the problem, thanks. Looking at this again though I would agree that the way i'm trying to do this is not clean and there must be an easier way... — JimJay, Jun 05 '12 at 20:21

Django "extra" query

2 Answers2