Django annotate/count filter only working with postgres not sqlite

Question

Following model relations:

I need a list of classA-records that have zero classB-records connected to the common classC-record.

In postgres both queries give me what I want, while in sqlite they both give me a empty result:

ClassA.objects.annotate(num_classb=Count('classc__classbs')).filter(num_classb=0)

ClassA.objects.filter(classc__classbs__isnull=True)

Where do I go wrong? I did not find something about differences in the handling of such filters/annotations between postgres and sqlite

justdavey · Accepted Answer · 2016-05-04 13:40:47Z

1

Have you tried appending .order_by()?

answered May 4, 2016 at 13:40

justdavey

2361 silver badge4 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

How would adding a order_by affect an empty queryset?

Adding an empty .order_by() will remove any ordering that may affect the result. A similar problem is described here: stackoverflow.com/questions/13816062/annotate-in-sqlite