Django Annotation F() value

Question

I have a question regarding Django Annotations:

tickers = {
    "BCO": 10.0,
    "AIR": 50.0,
}

assets = Asset.objects.annotate(
    price=(tickers[F("fk_security__ticker_symbol")]))

I would like to annotate the value from the dictionary to the asset but always getting a key error: F(fk_security__ticker_symbol)

What am I doing wrong here? Is it even possible to do this? Thanks a lot in advance!

If it helps, here is my model:

class Asset(models.Model):
    fk_security = models.ForeignKey(Security, on_delete=models.CASCADE, blank=False, verbose_name="Security",related_name="security",
    )

class Security(models.Model):
    ticker_symbol = models.CharField(
        max_length=5,
        verbose_name="Ticker Symbol",
        default="",
    )

Answer 1

If you don't need to filter, aggregate or whatever on the price, I would advise not to do this in the ORM, since JOINing with a dictionary will be slow, and complicated.

So we can do this with:

tickers = {
    'BCO': 10.0,
    'AIR': 50.0,
}

assets = Asset.objects.select_related('fk_security')
for asset in assets:
    asset.price = tickers.get(asset.fk_security.ticker_symbol)

otherwise we can use:

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

Asset.objects.annotate(
    price=Case(
        *[
            When(fk_security__ticker_symbol=k, then=Value(v))
            for k, v in tickers.items()
        ]
    )
)

But both are not a good idea. The best way is to work with a model that stores prices, and making JOINs.

---

Note: Normally one does not add a prefix fk_… to a ForeignKey field, since Django will automatically add a "twin" field with an …_id suffix. Therefore it should be security, instead of fk_security. You can use the db_column=… parameter ^[Django-doc] to specify the name of the column in the database if you want to name this differently from the field name.