[Solved]-How can I make a Django update with a conditional case?

by

7👍

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.

👤Flimm

6👍

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)
)
👤whp

4👍

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"),))
👤Deepanshu Mehta

3👍

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))
👤Benjamin Toueg

2👍

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.

👤orokusaki

0👍

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.

👤camomilk

Leave a comment