Can I use Django F() objects with string concatenation?

Question

I want to run a django update through the ORM that looks something like this:

MyModel.objects.filter(**kwargs).update(my_field=F('my_other_field')+'a string')

This causes MySQL to throw an exception. Is there anyway to do this without writing raw SQL?

I think what you need is described here https://stackoverflow.com/questions/8143362/django-using-an-f-expression-for-a-text-field-in-an-update-call/25077907#25077907 — Martin Faucheux, Sep 22 '20 at 01:50

score 157 · Answer 1 · edited Dec 24 '22 at 09:16

157

I had a similar issue; basically I wanted to concatenate two fields to the get the full name of a user. I got it solved this way(but must say that I was using Postgres):

from django.db.models.functions import Concat
from django.db.models import F, Value, CharField

AnyModel.objects.filter(**kwargs).annotate(full_name=Concat(F('model__user_first_name'), Value(' '), F('model__user_last_name'), output_field=CharField()))

where, F('...') evaluates its argument as a query, so you can query a field of the model itself, or span across models as you would do in filter/get, while Value('...') evaluates its argument literally(in my case I needed a space to be placed in between first_name and last_name), and output_field=... specifies the Type of the annotated field(I wanted to be a CharField). For more info, you can read Django docs about Concat

edited Dec 24 '22 at 09:16

starball

20,030
7
43
238

answered Aug 30 '16 at 08:57

tombishop83

1,837
2
12
12

2

Exactly my use case! – Ken4scholars Nov 06 '18 at 12:03
This should be the accepted answer as it provides the solution the author is asking for – Humphrey Jan 21 '19 at 22:50
You don't actually need to use `F()` here, just the name of the field as a string suffices. That's why you need to use `Value()` to include a literal string. – Ryan Apr 12 '23 at 14:32

score 45 · Accepted Answer · answered Jul 21 '10 at 17:02

What's happening is that Django is passing the '+' through to SQL - but SQL doesn't allow the use of '+' for concatenation, so it tries to add numerically. If you use an integer in place of 'a string', it does work in the sense that it adds the integer value of my_other_field to your variable.

It's debatable whether this is a bug. The documentation for F() objects in lookup queries states:

Django supports the use of addition, subtraction, multiplication, division and modulo arithmetic with F() objects

so it could be argued that you shouldn't be trying to use it to update with strings. But that's certainly not documented, and the error message 'Incorrect DOUBLE value' is not very helpful. I'll open a ticket.

In SQLite it's even less helpful: it just sets the text to "0"! — boxed, Feb 05 '15 at 13:15
This is a great answer but it doesn't answer the question. Is using `Concat` and `Value` the best way to achieve what is asked? — Cyrlop, Aug 22 '18 at 15:25

score 42 · Answer 3 · answered Jul 15 '16 at 13:20

42

You can use Concat function https://docs.djangoproject.com/en/1.9/ref/models/database-functions/#concat

from django.db.models.functions import Concat
from django.db.models import Value

MyModel.objects.filter(**kwargs).update(my_field=Concat('my_other_field', Value('a string'))

answered Jul 15 '16 at 13:20

Alexey Ruzin

967
8
17

This is the correct answer as it specifically addresses the `update` from the original question. – rrauenza Mar 22 '19 at 23:47

Can I use Django F() objects with string concatenation?

3 Answers3

Linked