So I’ve been strungling a couple days trying to make this out. I’ve read several times now the Aggregation and Complex Queryset help pages in Django docs and gone through several stackoverflow topics, but none of them pointed to my exact issue.
I’m trying to annotate a query using a previous annotation on the same query. Is this doable?
Let me place an example. I have these models:
class Author(models.Model): id = models.AutoField(primary_key=True) name = models.CharField(max_length=50) class Book(models.Model): id = models.AutoField(primary_key=True) author = models.ForeignKey(Author, on_delete=models.CASCADE) class Page(models.Model): book = models.ForeignKey(Book, on_delete=models.CASCADE) words = models.FloatField()
I want to get all the books that belong to each author that have pages with less than 100 words, so I prepared the following query:
This sentence looks perfectly fine when executed from Django, but MariaDB will complain saying:
django.db.utils.ProgrammingError: (1111, 'Invalid use of group function')
And SQLite will complain as well:
django.db.utils.OperationalError: misuse of aggregate function MIN()
I think this is because the use of Count() and Min(), if I look into the generated SQL, there is something like this:
COUNT(CASE WHEN MIN(`page`.`words`) < 100.0 THEN `book`.`id` ELSE NULL END) AS `short_books`
On the other side, I have been able to achieve what I want using Subquery, but not only it’s completely ugly, it’s also blatanly slow, taking more than 6 seconds for a few hundred thousand records, so I’m sure there must be another way.
Does anyone have any better idea?