Using F object in Django filteredRelation condition-CodePudding

I'm trying to use Django (2.2) filteredRelation with something like that:

produces = Produce.objects.annotate(
    valid_stocks=FilteredRelation(
        'stocks', condition=(
             Q(stocks__customer_nb_ordered__lt=F('stocks__nb_max_available'))
        )
    )
).filter(valid_stocks__isnull=False)

Seems like Django can't evaluate this query and I'm getting the following error:

ProgrammingError: missing FROM-clause entry for table "t3" LINE 1: ...ce_produce"."id" = valid_stocks."produce_id" AND (T3."custom...

The query generated:

  SELECT *
  FROM "produce_produce"
 INNER JOIN "produce_stock" valid_stocks
    ON ("produce_produce"."id" = valid_stocks."produce_id" AND (T3."customer_nb_ordered" < (T3."nb_max_available")))
 WHERE valid_stocks."id" IS NOT NULL
 LIMIT 21

Note that it works like expected by replacing the F object by some integer.

Q(stocks__nb_ordered__lt=10

This is weird because this limitation is not documented, see https://docs.djangoproject.com/en/2.2/ref/models/querysets/#filteredrelation-objects

Does anyone have an idea about how to solve this problem ?

CodePudding user response：

You need to use the name of the annotated filtered relation in your F object instead of the original name - F('valid_stocks__nb_max_available')

produces = Produce.objects.annotate(
    valid_stocks=FilteredRelation(
        'stocks', condition=(
             Q(stocks__customer_nb_ordered__lt=F('valid_stocks__nb_max_available'))
        )
    )
).filter(valid_stocks__isnull=False)