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

Question

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'])

P.S. YAML fixture:

---
- model: ormtest.author
  pk: 1
  fields:
      name: 'Author #1'
      removed: 0
- model: ormtest.author
  pk: 2
  fields:
      name: 'Author #2'
      removed: 0
- model: ormtest.author
  pk: 3
  fields:
      name: 'Author #3'
      removed: 1
- model: ormtest.image
  pk: 1
  fields:
      author: 1
      name: 'Image #1'
      height: 100
      width: 100
- model: ormtest.image
  pk: 2
  fields:
      author: 1
      name: 'Image #2'
      height: 150
      width: 150
- model: ormtest.image
  pk: 3
  fields:
      author: 2
      name: 'Image #3'
      height: 150
      width: 100
- model: ormtest.image
  pk: 4
  fields:
      author: 2
      name: 'Image #4'
      height: 150
      width: 150

Answer 1

OK, untested because I don't have your data - how about this:

Author.objects.filter(removed=False).select_related('image').filter(image__height=100).annotate(count_of_images=Count('image')).filter(count_of_images__lte=5)

Edit:

That took you almost there. The problem is related to the outer join... I think this is the final version that should do it for you:

Author.objects.filter(removed=False).select_related('image').filter(Q(image__height=100) | Q(image__height__isnull=True)).annotate(count_of_images=Count('image')).filter(count_of_images__lte=5)

That Q(image__height=100) | Q(image__height__isnull=True) in there is the trick. It will get either Authors that have images with a height of 100 OR authors that have an image height of null (meaning that they don't have images associated).

PS. Thanks for the question... It was actually more challenging than I originally thought and I learned some cool tricks in the process of trying to test it!

---

Ouch... I did test my last solution with sqlite3. I don't have a MySQL instance to use for testing... :-(

Let me ponder an alternative.

But - yes - if it works in sqlite it should work in MySQL; I would report it as a bug.