CODEWITHSUNDEEP
pythondjangopostgresql
Insomniak
Insomniak

Reputation: 493

Django queryset : How to exclude objects with any related object satisfying a condition

I stumbled upon a weird behaviour of django querysets while making a difficult query and I'd like to know if somebody knew how to improve this query.

Basically I have a model like this:

class Product(models.Model):
    pass

class Stock(models.Model):
    product_id = models.ForeignKey(Product)
    date = models.DateField()
    initial_stock = models.SmallIntegerField()
    num_ordered = models.SmallIntegerField()

And I'd like to select all Product that are not available for any date (means that there are no stock object related to the product which have their initial_stock field greater than the num_ordered field). So at first, I did:

Product.objects.exclude(stock__initial_stock__gt=F('stock__num_ordered')).distinct()

but I checked and this query translates as:

SELECT DISTINCT *
FROM "product"
LEFT OUTER JOIN "stock"
ON ("product"."id" = "stock"."product_id")
WHERE NOT ("product"."id" IN (
    SELECT U1."product_id" AS Col1 
    FROM "product" U0 
    INNER JOIN "stock" U1 
    ON (U0."id" = U1."product_id") 
    WHERE (U1."initial_stock" > (U1."num_ordered") AND U1."id" = ("stock"."id"))
))

Which makes a left join on stocks and then filters out the lines where the initial_stock is greater than the num_ordered before sending me back the distinct lines as a result.

So as you can see, it doesn't work when I have a stock object that is out of stock and another stock object that is not out of stock.

After the filtering, I'm left out with a product that is actually available on another date.

After many tries, I figured that this was working out:

Product.objects.exclude(
    stock__initial_stock__gt=F('stock__num_ordered')
).exclude(
    stock__initial_stock__gt=F('stock__num_ordered')
).distinct()

because it translates as:

SELECT *
FROM "product"
LEFT OUTER JOIN "stock"
ON ("product"."id" = "stock"."product_id")
LEFT OUTER JOIN "stock" T3
ON ("product"."id" = T3."product_id")
WHERE NOT ("product"."id" IN (
    SELECT U1."product_id" AS Col1 
    FROM "product" U0 
    INNER JOIN "stock" U1 
    ON (U0."id" = U1."product_id") 
    WHERE (U1."initial_stock" > (U1."num_ordered") AND U1."id" = ("stock"."id"))    )
)) AND NOT ("product"."id" IN (
    SELECT U1."product_id" AS Col1 
    FROM "product" U0 
    INNER JOIN "stock" U1 
    ON (U0."id" = U1."product_id") 
    WHERE U1."initial_stock" > (U1."num_ordered"))
))

which 'works' but feels like a weird hack and not seem very efficient for something that simple.

Did any of you encountered the same issue and came up with something better?

Thanks

EDIT: Thanks for this answer @dirkgroten, for the sake of comparison, let me write down the sql query resulting:

SELECT *,
       EXISTS(
        SELECT *
          FROM "stock" U0
         WHERE (U0."product_id" = ("product"."id") AND U0."initial_stock" > (U0."num_ordered"))
       ) AS "has_stock"
  FROM "product"
 WHERE EXISTS(
        SELECT *
          FROM "stock" U0
         WHERE (U0."product_id" = ("product"."id") AND U0."initial_stock" > (U0."num_ordered"))
       ) = false

The 2 queries seem to have the same execution time even though yours looks better. Although I'm very confused why the following filter of your annotate does not use the column created by the annotate instead of doing the query again in the WHERE...

Still regarding my answer, what I don't understand is why in one case there is an extra filter on AND U1."id" = ("stock"."id")) and not in the other exclude. Could it be possible django has a weird behaviour in the queryset API?

Upvotes: 1

Views: 2534

Answers (1)

dirkgroten
dirkgroten

Reputation: 20682

You're better off using a Subquery for this:

from django.db.models import OuterRef, Exists

in_stock = Stock.objects.filter(
    product_id=OuterRef('pk'), 
    initial_stock__gt=F('num_ordered'))

out_of_stock_products = Product.objects.annotate(has_stock=Exists(in_stock))\
                                       .filter(has_stock=False)

Upvotes: 2

Related Questions