CODEWITHSUNDEEP
django
camomilk
camomilk

Reputation: 763

How can I make a Django update with a conditional case?

I would like to use Django to update a field to a different value depending on its current value, but I haven't figured out how to do it without doing 2 separate update statements.

Here's an example of what I'd like to do:

now = timezone.now()
data = MyData.objects.get(pk=dataID)
if data.targetTime < now:
    data.targetTime = now + timedelta(days=XX)
else:
    data.targetTime = data.targetTime + timedelta(days=XX)
data.save()

Now, I'd like to use an update() statement to avoid overwriting other fields on my data, but I don't know how to do it in a single update(). I tried some code like this, but the second update didn't use the up to date time (I ended up with a field equal to the current time) :

# Update the time to the current time
now = timezone.now()
MyData.objects.filter(pk=dataID).filter(targetTime__lt=now).update(targetTime=now)
# Then add the additional time
MyData.objects.filter(pk=dataID).update(targetTime=F('targetTime') + timedelta(days=XX))

Is there a way I can reduce this to a single update() statement? Something similar to the SQL CASE statement?

Upvotes: 11

Views: 14961

Answers (6)

Deepanshu Mehta
Deepanshu Mehta

Reputation: 1770

Simple Example for Django 3 and above:

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

MyModel.objects.filter(abc__id=abc_id_list)\
                .update(status=Case(
                    When(xyz__isnull=False, then=Value("this_value")),
                    default=Value("default_value"),))

Upvotes: 5

whp
whp

Reputation: 1514

Django 1.9 added the Greatest and Least database functions. This is an adaptation of Benjamin Toueg's answer:

from django.db.models import F
from django.db.models.functions import Greatest


MyData.objects.filter(pk=dataID).update(
    targetTime=Greatest(F('targetTime'), timezone.now()) + timedelta(days=XX)
)

Upvotes: 6

Flimm
Flimm

Reputation: 150853

You need to use conditional expressions, like this

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

object = MyData.objects.get(pk=dataID)
now = timezone.now()
object.targetTime = Case(
    When(targetTime__lt=now, then=now + timedelta(days=XX)),
    default=F('targetTime') + timedelta(days=XX)
)
object.save(update_fields=['targetTime'])

For debugging, try running this right after save to see what SQL queries have just run:

import pprint
from django.db import connection
pprint.pprint(["queries", connection.queries])

I've tested this with integers and it works in Django 1.8, I haven't tried dates yet so it might need some tweaking.

Upvotes: 7

Benjamin Toueg
Benjamin Toueg

Reputation: 10867

If I understand correctly, you take the maximum time between now and the value in database.

If that is so, you can do it in one line with the max function:

from django.db.models import F
MyData.objects.filter(pk=dataID).update(targetTime=max(F('targetTime'),timezone.now()) + timedelta(days=XX))

Upvotes: 3

camomilk
camomilk

Reputation: 763

I have figured out how to do it with a raw SQL statement:

cursor = connection.cursor()
cursor.execute("UPDATE `mydatabase_name` SET `targetTime` = CASE WHEN `targetTime` < %s THEN %s ELSE (`targetTime` + %s) END WHERE `dataID` = %s", [timezone.now(), timezone.now() + timedelta(days=XX), timedelta(days=XX), dataID])
transaction.commit_unless_managed()

I'm using this for now and it seems to be accomplishing what I want.

Upvotes: 0

orokusaki
orokusaki

Reputation: 57138

Instead of using queryset.update(...), use obj.save(update_fields=['field_one', 'field_two']) (see https://docs.djangoproject.com/en/dev/ref/models/instances/#specifying-which-fields-to-save), which won't overwrite your existing fields.

It's not possible to do this without a select query first (get), because you're doing two different things based on a conditional (i.e., you can't pass that kind of logic to the database with Django - there are limits to what can be achieved with F), but at least this gets you a single insert/update.

Upvotes: 2

Related Questions