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

It is still not entirely clear to me what you want. Can you provide some sample scenario and what the expected result should be? — willeM_ Van Onsem
– willeM_ Van Onsem, Commented Feb 18, 2021 at 17:59
I added some explanation, basically from a model instance, I want to get a queryset that contains the current pizza its num of toppings, along with the top3 other pizzas and their num of toppings — Reed Jones
– Reed Jones, Commented Feb 18, 2021 at 18:25
but you order the pizzas with the number of toppings? Just to make sure, since I find it a bit an odd ordering. — willeM_ Van Onsem
– willeM_ Van Onsem, Commented Feb 18, 2021 at 18:27
yes the top 3 pizzas are the first 3 pizzas ordered by the number of toppings — Reed Jones
– Reed Jones, Commented Feb 18, 2021 at 18:28
Imagine a question, with many comments, I want the current question, its number of comments, compared with the top 3 questions with the most comments, all in 1 query — Reed Jones
– Reed Jones, Commented Feb 18, 2021 at 18:29

Jarad · Accepted Answer · 2021-02-18 21:52:30Z

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.

I ended up using a similar solution as above by combing the querysets with "|"

willeM_ Van Onsem · Accepted Answer · 2021-02-19 17:39:30Z

1

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.

edited Feb 19, 2021 at 17:39

answered Feb 18, 2021 at 18:28

willeM_ Van Onsem

482k33 gold badges483 silver badges624 bronze badges

2 Comments

Reed Jones Over a year ago

That seems right but This gives an error: ORDER BY not allowed in subqueries of compound statements.

Reed Jones Over a year ago

Note: the error above only occurs on sqlite

Collectives™ on Stack Overflow

Django QuerySet annotate with Subquery

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related