How to run a query with an inner query in it in Django?

Question

I'm trying to run a query with an inner query in it as shown below:

                                    # Inner query
Product.objects.filter(category__in=Category.objects.get(pk=1))

But, I got the error below:

TypeError: 'Category' object is not iterable

Also, I'm trying to run a query with an inner query in it as shown below:

                                # Inner query
Product.objects.filter(category=Category.objects.filter(pk=1))

But, I got the error below:

ValueError: The QuerySet value for an exact lookup must be limited to one result using slicing.

So, how can I run these queries above with inner queries in them?

Answer 1

To category__in, you need to assign the queryset made by filter() instead of the object made by get() as shown below:

                                                    # Here
Product.objects.filter(category__in=Category.objects.filter(pk=1))

And, the code below is equivalent to the code above:

qs = Category.objects.filter(pk=1)
Product.objects.filter(category__in=qs)

And, one SELECT query with WHERE IN below is run according to the PostgreSQL query logs below. *You can see my answer explaining about how to log queries in PostgreSQL:

Also to category, you need to assign the object made by get() instead of the queryset made by filter() as shown below:

                                                # Here
Product.objects.filter(category=Category.objects.get(pk=1))

And, the code below is equivalent to the code above:

obj = Category.objects.get(pk=1)
Product.objects.filter(category=obj)

And, two SELECT queries with WHERE below are run according to the PostgreSQL query logs below:

Answer 2

Eaiser is to make a simple JOIN:

Product.objects.filter(category_id=1)

For some databases it will not matter, but MySQL is notably not very good with subqueries, so the JOIN version will outperform the subquery one.