Django enrich QuerySet

I have an Django Application that runs only locally on my machine.

I have two diffrent tabels, with the transaction and the price. Now I want to enrich my transaction with the price.

I wrote something similar already to calculate the average price:

for transaction in transactions:
    filter_date = transaction.timestamp.date()
    price = Price.objects.filter(fiat=cur_id, date=filter_date).first()
    if not price:
        price = PriceTextBackup.objects.filter(fiat=cur_id, date=filter_date.strftime("%d-%m-%Y")).first()
    if price:
        if transaction.amount > 0:
            sats_price=price.price*transaction.amount
            price_list.append(sats_price)
            transaction_counter += transaction.amount

I display the price in a popup on mouseover, the html page looks like this

            {% if transaction.amount >= 0 %}
                <td onmouseover="showPopup({{forloop.counter}}0000)" onmouseout="hidePopup()" class="text-success">{{transaction.fiat_CHF|floatformat:2 |intcomma }}</td>
            {% else %}
                <td class="text-danger">{{transaction.fiat_CHF|floatformat:2 |intcomma }}</td>
            {% endif %}

How can I do the same logic for the price in the queryset?

So you can use Django annotate() with Subquery to enrich your QuerySet efficiently instead of looping.

Here's how to do it:

from django.db.models import OuterRef, Subquery, Case, When, F
from django.db.models.functions import Cast
from django.db.models import DateField

# Create subqueries for price lookup
price_subquery = Price.objects.filter(
    fiat=cur_id,
    date__date=OuterRef('timestamp__date')
).values('price')[:1]

# Backup price subquery (convert string date to match)
backup_price_subquery = PriceTextBackup.objects.filter(
    fiat=cur_id,
    date=Cast(OuterRef('timestamp__date'), output_field=models.CharField())
).values('price')[:1]

# Enrich transactions with price data
transactions = Transactions.objects.annotate(
    current_price=Subquery(price_subquery),
    backup_price=Subquery(backup_price_subquery),
    # Use current_price if available, otherwise backup_price
    new_price=Case(
        When(current_price__isnull=False, then=F('current_price')),
        default=F('backup_price')
    ),
    # Calculate sats_price (only for positive amounts)
    sats_price=Case(
        When(amount__gt=0, then=F('new_price') * F('amount')),
        default=0
    )
)

# Now you can access the enriched data directly
for transaction in transactions:
    if transaction.new_price:
        print(f"Transaction: {transaction.amount}, Price: {transaction.new_price}")
        print(f"Sats price: {transaction.sats_price}")

Happy Building! Let me know if this works!

First I would advise to make the date of a the PriceTextBackup a DateTimeField or DateField. This will require a bit of work, but the main advantage is that it will make it easier to query, and probably reduce the size of the database, and thus make queries a (bit) faster.

We can do this by changing the type of the field to:

from datetime import datetime

class PriceTextBackup(models.Model):
    date = models.DateTimeField(default=datetime(1970, 1, 1))
    price = models.FloatField()
    fiat = models.ForeignKey(Currencies, on_delete=models.DO_NOTHING)

and run:

python manage.py makemigrations # don't migrate yet!

but do not yet migrate. Now we can change the migration file to something like:

# Generated by Django 5.2.0 on 2025-09-04 17:28

from django.db import migrations, models
from django.db.models import DateTimeField


def forwards_func(apps, schema_editor):
    PriceTextBackup = apps.get_model("app_name", "PriceTextBackup")
    to_update = []
    for item in PriceTextBackup.objects.iterator():
        to_update.append(item)
        item.date_as_date = datetime.strptime(item.date, '%d-%m-%Y')
        if len(to_update) > 100:
            PriceTextBackup.objects.bulk_update(to_update, fields=('date_as_date',))
            to_update = []

    PriceTextBackup.objects.bulk_update(to_update, fields=('date_as_date',))

class Migration(migrations.Migration):

    dependencies = [
        ('app_name', '1234_previous_migration_file'),
    ]

    operations = [
        migrations.AddField(
            model_name='pricetextbackup',
            name='date_as_date',
            field=models.DateTimeField(blank=True, null=True, verbose_name='Date'),
        ),
        migrations.RunPython(forwards_func),
        migrations.RemoveField(
            model_name='pricetextbackup',
            name='date',
        ),
        migrations.RenameField(
            model_name='currencyrates',
            old_name='date_as_date',
            new_name='date',
        ),
        migrations.AddField(
            model_name='pricetextbackup',
            name='date',
            field=models.DateTimeField(verbose_name='Date'),
        ),
    ]

I would strongly advise to test this migration on a copy of the database, to make sure it works correctly. If not all dates as string are formatted as %d-%m-%Y, probably parse them differently, or if the timestamp is really unusable, remove the record from the database (and perhaps keep it in a dump).

After that is done, you can enrich the queryset with:

cur_id = 1234

Transactions.objects.annotate(
  price=Coalesce(
    Subquery(
      Price.objects.filter(
        date__date=OuterRef('date__date')), fiat_id=cur_id
      ).values('price')[:1]
    ),
Subquery(
      PriceTextBackup.objects.filter(
        date__date=OuterRef('date__date')), fiat_id=cur_id
      ).values('price')[:1]
    ),
)

The Transaction objects that arise from this queryset will have an extra attribute .price that will be the result of a lookup in Price, and PriceTextBackup if it can not be found for that transaction.

Back to Top