How to filter queryset on calculated field provided by `extra` call in Django?

[dnedbaylo]

Hi all,

Here is my django models:

class Author (models.Model):
name = models.CharField(max_length=255)
removed = models.BooleanField(default=False)

class Image (models.Model):
author = models.ForeignKey(Author)
name = models.CharField(max_length=255)
height = models.PositiveIntegerField()
width = models.PositiveIntegerField()

Basically, i need to select each author who is not removed and who has
5 or less images with height equal to 100.

I use MySQL, and here is version info:

mysql Ver 14.12 Distrib 5.0.67

Naturally, it would look like this:

Author.objects.filter(removed=False).extra(select={
'imgcount': """SELECT COUNT(*)
FROM ormtest_image
WHERE height=100 AND
ormtest_image.author_id=ormtest_author.id"""
}).filter(imgcount__lte=5)

It does not work: "FieldError: Cannot resolve keyword 'imgcount' into
field. Choices are: id, image, name, removed"

OK, let's try where argument of extra method:

Author.objects.filter(removed=False).extra(select={
'imgcount': """SELECT COUNT(*)
FROM ormtest_image
WHERE height=100 AND
ormtest_image.author_id=ormtest_author.id"""
}, where=['imgcount <= 5'])

It does not work as well: "OperationalError: (1054, "Unknown column
'imgcount' in 'where clause'")", since to filter data on calculated
field in MySQL you have to use HAVING clause.

Any ideas?

I tested this with Django 1.1 and latest version from trunk.

So far, i use this hack:

Author.objects.filter(removed=False).extra(select={
'imgcount': """SELECT COUNT(*)
FROM ormtest_image
WHERE height=100 AND
ormtest_image.author_id=ormtest_author.id"""
}, where=['1 HAVING imgcount <=5'])