views:

57

answers:

2

I have a Coupon model that has some fields to define if it is active, and a custom manager which returns only live coupons. Coupon has an FK to Item.

In a query on Item, I'm trying to annotate the number of active coupons available. However, the Count aggregate seems to be counting all coupons, not just the active ones.

# models.py
class LiveCouponManager(models.Manager):
    """
    Returns only coupons which are active, and the current
    date is after the active_date (if specified) but before the valid_until
    date (if specified).
    """
    def get_query_set(self):
        today = datetime.date.today()
        passed_active_date = models.Q(active_date__lte=today) | models.Q(active_date=None)
        not_expired = models.Q(valid_until__gte=today) | models.Q(valid_until=None)
        return super(LiveCouponManager,self).get_query_set().filter(is_active=True).filter(passed_active_date, not_expired)

class Item(models.Model):
    # irrelevant fields

class Coupon(models.Model):
    item = models.ForeignKey(Item)
    is_active = models.BooleanField(default=True)
    active_date = models.DateField(blank=True, null=True)
    valid_until = models.DateField(blank=True, null=True)
    # more fields

    live = LiveCouponManager() # defined first, should be default manager

# views.py
# this is the part that isn't working right
data = Data.objects.filter(q).distinct().annotate(num_coupons=Count('coupon', distinct=True))

The .distinct() and distinct=True bits are there for other reasons - the query is such that it will return duplicates. That all works fine, just mentioning it here for completeness.

The problem is that Count is including inactive coupons that are filtered out by the custom manager.

Is there any way I can specify that Count should use the live manager?


EDIT

The following SQL query does exactly what I need:

SELECT data_item.title, COUNT(data_coupon.id) FROM data_item LEFT OUTER JOIN data_coupon ON (data_item.id=data_coupon.item_id)
WHERE (
    (is_active='1') AND 
    (active_date <= current_timestamp OR active_date IS NULL) AND
    (valid_until >= current_timestamp OR valid_until IS NULL)
)
GROUP BY data_item.title

At least on sqlite. Any SQL guru feedback would be greatly appreciated - I feel like I'm programming by accident here. Or, even better, a translation back to Django ORM syntax would be awesome.

+1  A: 

In case anyone else has the same problem, here's how I've gotten it to work:

Items = Item.objects.filter(q).distinct().extra(

            select={"num_coupons":
                     """
                     SELECT  COUNT(data_coupon.id) FROM  data_coupon
                     WHERE (
                         (data_coupon.is_active='1') AND 
                         (data_coupon.active_date <= current_timestamp OR data_coupon.active_date IS NULL) AND
                         (data_coupon.valid_until >= current_timestamp OR data_coupon.valid_until IS NULL) AND
                         (data_coupon.data_id = data_item.id)
                     )
                     """
                    },).order_by(order_by)

I don't know that I consider this a 'correct' answer - it completely duplicates my custom manager in a possibly non portable way (I'm not sure how portable current_timestamp is), but it does work.

Chris Lawlor
Just an FYI - it seems that once you use `extra`, you cannot add any other `annotate` calls, any further annotations must be done in SQL as well (just add them to the existing `select` dict).
Chris Lawlor
Hi Chris, did you ever figure out a more Django ORM-ish way to solve this problem or did you stick with the .extra() method above?
mitchf
No, this was part of a pretty short term project so I pretty much moved on to other things once I got this working. Never had a problem with it though, seemed to perform OK and passed all tests, etc.
Chris Lawlor
A: 

Are you sure your custom manager actually get's called? You set your manager as Model.live, but you query the normal manager at Model.objects.

Have you tried the following?

data = Data.live.filter(q)...
knutin
Data is another model, which I mistakenly represented as 'Item' in my example. It has an FK to Coupon. Only Coupon has a custom manager called 'live'
Chris Lawlor