Filtering inside subquery using outside column

[sacred garnet]

I have the following models:

class Foo:
  amount = DecimalField()
  identifier = CharField()

class FooMapping:
  # This is not id and can be used on other models aside from Foo
  identifier = CharField() 
  condition = CharField()
  default_value = CharField()
  condition_value = CharField()

I wanted to filter on the subquery like:

subquery = FooMapping.objects.filter(identifier=OuterRef('identifier')\
  .annotate(final_value=Case(
      When(
        condition='If positive',
        OuterRef('amount') > 0,
        then=F('condition_value')
      ),
      When(
        condition='If negative',
        OuterRef('amount') < 0,
        then=F('condition_value')
      ),
      default=F('default_value')
    )  
  )

Is this possible in some way? Currently I'm annotating and filtering on Foo for the condition values as a workaround.

[tranquil crescent]

Both your When objects return the same column, but your conditions aren't mutually exclusive. 🤔

Here are all the possible paths your case statement can take:

| amount | condition   | final_value     |
| ------ | ----------- | --------------- |
| 1      | If positive | condition_value |
| 1      | If negative | default_value   |
| 1      | ...         | default_value   |
| 0      | If positive | default_value   |
| 0      | If negative | default_value   |
| 0      | ...         | default_value   |
| -1     | If positive | default_value   |
| -1     | If negative | condition_value |
| -1     | ...         | default_value   |

I assume you want to annotate Foo with the results of your subquery?

Foo.objects.filter(final_value=Subquery(subquery.values("final_value").first()))

[sacred garnet]

Yep, that's just the nature of it and this is just a small subset of the functionality. Really can't change it

[sacred garnet]

Yep

[tranquil crescent]

Okay, you can only annotate with one value from your Subquery. Is FooMapping.identifier unique?

[sacred garnet]

Yep

[tranquil crescent]

Then I think this works as is 🙂

[tranquil crescent]

Have you tried this?

[sacred garnet]

I mean, that's not what I'm asking and I know that already

The question is if you notice is on usage of OuterRef on the snippet

[tranquil crescent]

Sorry, I'm not sure what you're looking for

[tranquil crescent]

I don't see any issue with that. Is there an error?

[sacred garnet]

I want on the filter lookup to use the column that is outside. Are you saying that is a valid syntax?

[tranquil crescent]

I don't see why not. Have you tried it?

Certainly weird syntax, and I wouldn't call it "filter".

[rigid crag]

It's not valid Python at the moment, the When statements have a keyword argument before a positional argument?

You probably want something like GreaterThan(OuterRef("amount"), 0)

[tranquil crescent]

What Ben said. And in one argument

When(
  Q(condition='If negative') &
  GreaterThan(OuterRef('amount'), 0),
  then=F('condition_value')
),

https://docs.djangoproject.com/en/5.1/ref/models/conditional-expressions/#when

[sacred garnet]

Okay, yep that's what I'm looking for. Didn't know django has a class for that.