Django - Count a subset of related models - Need to annotate count of active Coupons for each Item

Question

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 = Item.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.

Answer 1

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.

Answer 2

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