Django Aggregation Count

Question

I'm trying to filter my model with an aggregate function.

I have a Model A and a Model B with a foreign key on the model A.

annotate_pool = queryset.annotate(nb_bets=Count('bets')).all()
for obj in annotate_pool:
    bets_obj = obj.bets.all()
    bets_length = len(bets_obj)
    print(obj.nb_bets, bets_length)

And the annotation doesn't give me the same result as the function length.

1 1
1 2
1 2
1 2
1 2
1 1
1 1
2 2

Here is my models:

class Pronostic(models.Model):
    cote_total = models.FloatField(default=0.0)
    trust = models.IntegerField()
    mise_ratio = models.IntegerField(default=10)
    safe = models.BooleanField(default=False)

class Bet(models.Model):
    name = models.CharField(max_length=255)
    match = models.ForeignKey('pronostics.Match', on_delete=models.CASCADE, related_name='bets')
    cote = models.FloatField()
    status = models.IntegerField(choices=STATUS, default=0)
    pronostic = models.ForeignKey('pronostics.Pronostic', related_name='bets', on_delete=models.CASCADE)

len(bets_obj) should give me the same result as Count('bets'). What is going on? Why does Count give me a wrong result?

Thank you in advance.

EDIT:

I'm using django-rest-framework, and try to add a custom filter. (see doc here). The result expected is: obj.nb_bets should be equal to bets_length. Since I want to filter my models like this:

queryset.annotate(nb_bets=Count('bets')).filter(nb_bets__gte=2)

or

queryset.annotate(nb_bets=Count('bets')).filter(nb_bets__lte=2)

Here is the SQL query contained in my queryset:

SELECT "pronostics_pronostic"."id",
       "pronostics_pronostic"."cote_total",
       "pronostics_pronostic"."trust",
       "pronostics_pronostic"."mise_ratio",
       "pronostics_pronostic"."safe"
FROM "pronostics_pronostic"
LEFT OUTER JOIN "pronostics_bet" 
       ON ("pronostics_pronostic"."id" = "pronostics_bet"."pronostic_id")
LEFT OUTER JOIN "pronostics_match" 
       ON ("pronostics_bet"."match_id" = "pronostics_match"."id")
WHERE ("pronostics_pronostic"."visible" = TRUE
       AND "pronostics_pronostic"."safe" = TRUE)
ORDER BY "pronostics_match"."date" DESC

If you need more information, let me know.

Answer 1

This is a trick question as long as you don't show the queryset for annotate_pool.

My guess is that you have fields from Bet in your SQL query for annotate_pool, resulting in duplicate Protostics that are only differentiated by the related Bet fields, which again leads to a lower bet count. Test:

Pronostic.objects.count() < len(annotate_pool)

True or False? My guess is True (provided that annotate_pool is unfiltered and contains all Pronostic objects). This means that you have duplicate Pronostics in annotate_pool. The number of Bets is spread out over those duplicates.

Answer 2

Based on the SQL you posted, your Pronostic objects are ordered by the field date in a related Match object. If you remove the ordering (using an empty order_by()), I would expect the numbers to match up. Test this:

annotate_pool = queryset.order_by().annotate(nb_bets=Count('bets')).all()