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"""

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"""

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"""