Filtering on the concatenation of two model fields in django

Question

With the following Django model:

class Item(models.Model):
  name = CharField(max_len=256)
  description = TextField()

I need to formulate a filter method that takes a list of n words (word_list) and returns the queryset of Items where each word in word_list can be found, either in the name or the description.

To do this with a single field is straightforward enough. Using the reduce technique described here (this could also be done with a for loop), this looks like:

q = reduce(operator.and_, (Q(description__contains=word) for word in word_list))
Item.objects.filter(q)

I want to do the same thing but take into account that each word can appear either in the name or the description. I basically want to query the concatenation of the two fields, for each word. Can this be done?

I have read that there is a concatenation operator in Postgresql, || but I am not sure if this can be utilized somehow in django to achieve this end.

As a last resort, I can create a third column that contains the combination of the two fields and maintain it via post_save signal handlers and/or save method overrides, but I'm wondering whether I can do this on the fly without maintaining this type of "search index" type of column.

score 2 · Accepted Answer · answered Aug 19 '13 at 19:16

The most straightforward way would be to use Q to do an OR:

lookups = [Q(name__contains=word) | Q(description__contains=word) 
           for word in words]
Item.objects.filter(*lookups)  # the same as and'ing them together

I can't speak to the performance of this solution as compared to your other two options (raw SQL concatenation or denormalization), but it's definitely simpler.

Filtering on the concatenation of two model fields in django

1 Answers1