Query based on a composition of columns in Django

Question

I'd like to figure out how to query based on a composition of columns, eg the fullname of a user, without any custom SQL. Is this possible?

Imagine something like User.objects.filter(firstname_concat_lastname__startswith="Barack Ob")

I know that in this particular example, it'd be easy enough to split "Barack Ob" by whitspace and modify the query, but is it possible to do a query on a composition of columns like this?

Answer 1

With the current Django ORM, no, it doesn't exist. You do have access to the Q object query handler.

User.objects.filter(Q(firstname__startswith=name.split(" ", 1)[0]), Q(lastname__startswith=name.split(" ", 1)[1]))

Answer 2

A possible solution to your question can be worked out if your database supports full text search. I use Postgresql (see documentation) and was able to make the following query work:

print User.objects.all().extra(where = 
     ["to_tsvector(coalesce(first_name) || coalesce(last_name)) 
      @@ to_tsquery('BarackObama')"])

You will note that I used the full name in the query. My knowledge of text search is limited and I don't know if there is a way to do the equivalent of __startswith (implemented using LIKE).

I suspect that this would be an overkill for your needs. You might be better off adding a custom field or custom method or a combination of the two to implement this.