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

Question

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

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.

Answer 2

As far as I can tell, you're not using the count value except to filter for those authors where it is greater than 1. If so, you can do this entirely in ORM code with a simple query:

Author.objects.filter(removed=False, image__height__gte=100)