Django get a value for annotated field based on condition?-CodePudding

I have a couple simple models and want to annotate query with field which value based on condition.

    class Book(models.Model):
        price = models.DecimalField('Price', null=True, default=None, max_digits=32, decimal_places=2)
        ...

    class Config(models.Model):
        city_prices = models.JSONField(default={"Paris": 10, "London": 15}, null=True)
        ...

I've try to query this model like that:

from django.db.models import F, When, Case, Subquery

config = Config.objects.first()

Book.objects.annotate(custom_price=Case(
    When(price__isnull=False, then=F('price')),
    When(price__isnull=True, then=Subquery(config.city_prices.get(F('city'))))
)

I've tried to use OuterRef instead of F() but it didn't successful either.The first "When" works good but the second one gets me an error in both cases use F() or OuterRef()

When(price__isnull=True, then=Subquery(db_conf.get(OuterRef('city'))))

AttributeError: 'NoneType' object has no attribute 'all'

I've tried to get rid of "F()" on second "When" and hardcore city name, but gets another error.

When(price__isnull=True, then=Subquery(config.city_prices.get('London')))

AttributeError: 'int' object has no attribute 'all'

This error shows that I get a value of "London" but Subquery try to query it. So I made a conclusion that when in previous query I've tried to use "F('city')" it got back None, and I think this because of F('city') refer to Config model rather than the Book.

I've tried different approach but it's unsuccessful either.

>>>from django.db.models.expressions import RawSQL
>>>Books.objects.annotate(custom_price=RawSQL('SELECT d.city_prices ->> %s FROM CONFIG_CONFIG d WHERE d.id = 1', (F('price'),)))

ProgrammingError: can't adapt type 'F'

I read somewhere here that F() can't collaborate with RawSQL. Think the problem is that I get dict at the end of query and want to extract value from it. So how can I achieve my goal to get a value for annotated field based on condition?

CodePudding user response：

First approach: calculate custom_price on application. It's simpler but you cannot order Books by custom_price

from django.db.models import F, When, Case, Subquery

config = Config.objects.first()


books = list(Book.objects.all())

for book in books:
    if book.price is not None:
        book.custom_price = book.price
    else:
         book.custom_price = config.city_prices.get(book.city)

Second approach: calculate Case-When expression for config.city_prices and annotate it:

from django.db.models import F, When, Case, Subquery, Value

config = Config.objects.first()

whens = []

for city, price in config.city_prices.items():
    whens.append(When(city=city, then=Value(price)) 

Book.objects.annotate(custom_price=Case(*whens))