Django select where field is the concatenation of a string and column value

Question

I'm trying to select a row where a field is the concatenation of a string and column value. This was the closest I could get:

qs = Report.objects.filter(job=self, persona__identity="u" + F("user__id"))

Report has fields persona and user. A persona object contains an identity field that is the concatenation of an identifier and an id. I'm trying to get all reports where the persona's identity string matches the user referenced by the report. (Note these are not my actual table names...everything changed to protect the innocent.)

This produces a query like,

WHERE (`persona`.`identity` =  u + `report`.`user_id` AND ...

but what I really need is something like

WHERE (`persona`.`identity` =  CONCAT("u", `report`.`user_id`) AND ...

Is there a way to do this with the ORM?

Answer 1

You could use extra to define the lookup in raw SQL. Something like:

Report.objects.extra({
  where=['(`persona`.`identity` = CONCAT("u", `report`.`user_id`)']
})

There are some downsides to using raw SQL, such as the code not being portable across different DB engines, but for complex queries, it is far more expressive than trying to shoehorn a solution into the ORM.

Answer 2

The alternative is available in Django 1.8:

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

Report.objects.filter(persona__identity=Concat(Value('u'), 'user__id'))