CODEWITHSUNDEEP
mysqlsqldjangoorm
Eric Conner
Eric Conner

Reputation: 10772

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

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?

Upvotes: 4

Views: 3493

Answers (2)

andrea.ge
andrea.ge

Reputation: 1987

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'))

Upvotes: 4

Gary Chambers
Gary Chambers

Reputation: 25868

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.

Upvotes: 4

Related Questions