17

Consider a table called DataTable. It has two fields: A and B.

I want to return all rows from this table, and annotating a field called C which is a concatenation of A and B fields.

Here is what I have tried:

from django.db.models import CharField, Value
from .models import DataTable

def Test(request):
    query = DataTable.objects.all().annotate(C=Value('A' + '-' + 'B', 
          output_field=CharField()))
    # the rest of the function...

The problem here is that C is literally just the string literal "A - B" for every returned row.

I want to concatenate the values of A and B fields.

ivanleoncz
  • 9,070
  • 7
  • 57
  • 49
yoyoyoyo123
  • 2,362
  • 2
  • 22
  • 36

3 Answers3

25

looks like you need concat:

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

query = DataTable.objects.annotate(C=Concat('A', 
          Value('-'), 'B', output_field=CharField()))
Andy K
  • 4,944
  • 10
  • 53
  • 82
Brown Bear
  • 19,655
  • 10
  • 58
  • 76
  • Perfect...now how do add a hyphen in between A and B – yoyoyoyo123 Jun 04 '18 at 14:23
  • Since you can't use a `CONCAT` function in an index because its behavior changes based on settings, how would you concat in Django using the `||` operator? – steel May 11 '23 at 18:29
8

Basically there are two approaches to achieve it.

First one:
First of all, you need to import the following expressions.

from django.db.models import F, Value, Func

then, concatenate the fields as below:

ann_concat = Customer.objects.annotate(
            fullName = Func(F('first_name'), Value(' '),F('last_name'), function= 'CONCAT'))

In above example, I have concatenated first name and last name into a new field called fullName.

Second one: This approach is way shorter than the first one you only need to import Concat() and Value() for making the spaces functions.

from django.db.models.functions import Concat


  short_syntax = Customer.objects.annotate(full_name = Concat('first_name',Value(' '),'last_name'))
Abdu4
  • 1,269
  • 2
  • 11
  • 19
1

You can do so with Value() and of course Concat django

Value is to set the white space or -, try the following:

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


def Test(request):
     query = DataTable.objects.annotate(C=Concat('A',Value('-') ,'B',
                 output_field=CharField())
      # the rest of the function...
Lemayzeur
  • 8,297
  • 3
  • 23
  • 50