Django query annotation with boolean field

Question

Let's say I have a Product model with products in a storefront, and a ProductImages table with images of the product, which can have zero or more images. Here's a simplified example:

class Product(models.Model):
  product_name = models.CharField(max_length=255)
  # ...

class ProductImage(models.Model):
  product = models.ForeignKey(Product, related_name='images')
  image_file = models.CharField(max_length=255)
  # ...

When displaying search results for products, I want to prioritize products which have images associated with them. I can easily get the number of images:

from django.db.models import Count
Product.objects.annotate(image_count=Count('images'))

But that's not actually what I want. I'd like to annotate it with a boolean field, have_images, indicating whether the product has one or more images, so that I can sort by that:

Product.objects.annotate(have_images=(?????)).order_by('-have_images', 'product_name')

How can I do that? Thanks!

Answer 1

I eventually found a way to do this using django 1.8's new conditional expressions:

from django.db.models import Case, When, Value, IntegerField
q = (
    Product.objects
           .filter(...)
           .annotate(image_count=Count('images'))
           .annotate(
               have_images=Case(
                   When(image_count__gt=0,
                        then=Value(1)),
                   default=Value(0),
                   output_field=IntegerField()))
           .order_by('-have_images')
)

And that's how I finally found incentive to upgrade to 1.8 from 1.7.

Answer 2

As from Django 1.11 it is possible to use Exists. Example below comes from Exists documentation:

>>> from django.db.models import Exists, OuterRef
>>> from datetime import timedelta
>>> from django.utils import timezone
>>> one_day_ago = timezone.now() - timedelta(days=1)
>>> recent_comments = Comment.objects.filter(
...     post=OuterRef('pk'),
...     created_at__gte=one_day_ago,
... )
>>> Post.objects.annotate(recent_comment=Exists(recent_comments))

Answer 3

Use conditional expressions and cast outputfield to BooleanField

Product.objects.annotate(image_count=Count('images')).annotate(has_image=Case(When(image_count=0, then=Value(False)), default=Value(True), output_field=BooleanField())).order_by('-has_image')