More strict query yields more results?

Question

I've got a query that looks like this

affiliates = User.objects.annotate(referral_count=Count('referrals'))
                         .filter(referral_count__gt=0)

But when I restrict it more by adding referrals__user__date_joined__gt=start_date to the filter, I get even higher referral counts.

I'm trying to count the number of people a user has referred within a time period.

The relevant model looks like this:

class Profile(models.Model):
    user = models.ForeignKey(User, unique=True, related_name='profile')
    referred_by = models.ForeignKey(User, null=True, blank=True,
        related_name='referrals')

In contrast, when I try it like this:

affiliates = User.objects \
    .exclude(referrals__user__date_joined__lte=sd, \
        referrals__user__date_joined__gt=ed) \
    .annotate(referral_count=Count('referrals')) \
    .filter(referral_count__gt=0) 

The exclude statement seems to do nothing... the counts don't change (should be 0 if no referred users have joined within that time period, but it isn't).

Answer 1

Hello, I am not completely sure, but maybe you could try replacing referrals by referred_by in your queries. When I try to execute a query similar to yours, it even throws an error. referrals is a manager, while referred_by is the name of a field, and is therefore more logical to have in a query.

Answer 2

This seems to work:

new_users = User.objects.filter(date_joined__gt=sd, date_joined__lte=ed)
new_referrals = User.objects.filter(referrals__user__in=new_users).annotate(referral_count=Count('referrals')).select_related('profile')   

The greater than 0 check isn't necessary because it already won't return any users without referrals.