Django QuerySet annotate with Subquery

Question

given the following model

class Pizza(Model):
  pass

class Topping(Model):
  on = ManyToMany(Pizza, related_name='on_pizza')

I'm trying to get, my Pizza and the number of toppings, along with the top 3 other pizzas (top in terms of the number of toppings) The final result should be: a list containing the current pizza, and the top 3 pizzas, along with the count of toppings for all.

So right now I have a pizza, and I have the top3 pizzas (top in terms of num of toppings)

But I have to iterate over the top pizzas to create a dict

for pizza in top_pizzas:
  data.append({'pizza':pizza, 'num':pizza.tcount})

<- this is what I'm trying to avoid, I don't want to do a query in a loop, I want to fetch the current pizza and its number of toppings, and also fetch the top3 pizzas and their number of toppings, all in one query.

to get the other top pizzas I'm doing:

top = Pizza.objects.all().exclude(pk=self.pk).annotate(tcount=Count('on_pizza')).order_by('-on_pizza')

this is in a class function (on the model)

What I want is the above query, including the current pizza, this is what I tried:

    def compared_with_top(self):
      top = Pizza.objects.all().exclude(pk=self.pk).annotate(tcount=Count('on_pizza')).order_by('-tcount')
      me_with_top = Pizza.objects\
          .filter(pk=self.pk)\
          .annotate(tcount=Count('on_pizza'))\
          .annotate(other_top_3=Subquery(top[:3]))
      return me_with_top

This gives me an error: FieldError: Cannot resolve expression type, unknown output_field

I've noticed all the example of subquery involve 2 separate models, and use OuterRef, my query doesn't have an outer ref (its all the same model) I just want to get 2 queries in one if that makes since.

The above error points to 'output_field' but I cant find any information about what that should be.

Edit: Someone suggested I do a union of the 2 queries, which sounded right, but then I get an error DatabaseError: ORDER BY not allowed in subqueries of compound statements.

EDIT#2: The above error only occurs on SQLite

Answer 1

If these were your pizzas:

Pizza.objects.values()
Out[15]: <QuerySet [
    {'id': 1, 'name': 'Hawaiian'},
    {'id': 2, 'name': 'Cheese'},
    {'id': 3, 'name': 'Veggie'},
    {'id': 4, 'name': 'Meat Lovers'},
    {'id': 5, 'name': 'Pineapple ONLY'}
]>

and these were your toppings:

Topping.objects.all()
Out[17]: <QuerySet [
    <Topping: Canadian Bacon>, <Topping: Pineapple>, <Topping: Cheese>,
    <Topping: Green peppers>, <Topping: Olives>, <Topping: Mushrooms>,
    <Topping: Onions>, <Topping: Tomatoes>, <Topping: Sausage>,
    <Topping: Pepperoni>, <Topping: Beef>
]>

and this was your model:

from django.db import models
from django.db.models import Q, Count

class Pizza(models.Model):
    name = models.CharField(max_length=50)
    toppings = models.ManyToManyField('Topping', related_name='on_pizza')

    def __str__(self):
        return self.name

    def compared_with_top(self):
        top = Pizza.objects.annotate(Count('toppings')).order_by(
            '-toppings__count').exclude(id=self.id)[:3]
        return Pizza.objects.filter(Q(id=self.id) | Q(id__in=top.values('id')))
        

class Topping(models.Model):
    name = models.CharField(max_length=50)

    def __str__(self):
        return self.name

What the count of toppings looks like per pizza:

In [32]: Pizza.objects.annotate(Count('toppings')).values()
Out[32]: <QuerySet [
{'id': 1, 'name': 'Hawaiian', 'toppings__count': 2},
{'id': 2, 'name': 'Cheese', 'toppings__count': 1},
{'id': 3, 'name': 'Veggie', 'toppings__count': 5},
{'id': 4, 'name': 'Meat Lovers', 'toppings__count': 6},
{'id': 5, 'name': 'Pineapple ONLY', 'toppings__count': 1}
]>

We make some pizzas:

hawaiian = Pizza.objects.get(name='Hawaiian')
cheese = Pizza.objects.get(name='Cheese')
veggie = Pizza.objects.get(name='Veggie')
meat = Pizza.objects.get(name='Meat Lovers')
pineapple = Pizza.objects.get(name='Pineapple ONLY')

Pizza.compared_with_top based on count results:

In [26]: hawaiian.compared_with_top()
Out[26]: <QuerySet [<Pizza: Hawaiian>, <Pizza: Cheese>, <Pizza: Veggie>, <Pizza: Meat Lovers>]>

In [27]: cheese.compared_with_top()
Out[27]: <QuerySet [<Pizza: Cheese>, <Pizza: Hawaiian>, <Pizza: Veggie>, <Pizza: Meat Lovers>]>

In [28]: veggie.compared_with_top()
Out[28]: <QuerySet [<Pizza: Veggie>, <Pizza: Hawaiian>, <Pizza: Cheese>, <Pizza: Meat Lovers>]>

In [29]: meat.compared_with_top()
Out[29]: <QuerySet [<Pizza: Meat Lovers>, <Pizza: Hawaiian>, <Pizza: Cheese>, <Pizza: Veggie>]>

In [30]: pineapple.compared_with_top()
Out[30]: <QuerySet [<Pizza: Pineapple ONLY>, <Pizza: Hawaiian>, <Pizza: Veggie>, <Pizza: Meat Lovers>]>

This gets you the right output with the first object in the queryset being the current pizza. The three that follow are not sorted from most to least though. It's backwards. I'm not sure why they are being sorted least to greatest but maybe someone else does know. I suspect it's because of using Q.

Answer 2

You can make a .union(…) [Django-doc] to construct the uion of the two querysets. This is thus:

def compared_with_top(self):
    top = Pizza.objects.exclude(pk=self.pk).annotate(
        tcount=Count('on_pizza')
    ).order_by('-tcount')
    pizzas = Pizza.objects.filter(pk=self.pk).annotate(
        tcount=Count('on_pizza')
    ).union(top[:3])

@ReedJones: tested this. If you run this on sqlite, then this will error. Probably for the other databases, this will run fine.