Django ORM: filtering on concatinated fields

Question

In my app, I have a document number which consists of several fields of Document model like:

{{doc_code}}{{doc_num}}-{{doc_year}}

doc_num is an integer in the model, but for the user, it is a five digits string, where empty spaces are filled by zero, like 00024, or 00573.

doc_year is a date field in the model, but in full document number, it is the two last digits of the year.

So for users, the document number is for example - TR123.00043-22.

I want to implement searching on the documents list page.

One approach is to autogenerate the full_number field from doc_code, doc_num and doc_year fields in the save method of Document model and filter on this full_number.

Anothe is to use Concat function before using of filter on query. First by concatinate full_code field

docs = Document.annotate(full_code=Concat('doc_code', 'doc_num', Value('-'), 'doc_year', output_field=CharField()))

and than filter by full_code field

docs = docs.filter(full_code__icontain=keyword)

But how to pass doc_num as five digits string and doc_year as two last digits of year to Concat function?

Or what could be a better solution for this task?

SamSparx · Answer 1 · 2022-05-24T22:55:44.227

Concat will only take field names and string values, so you don't really have many options there that I know of.

As you note, you can set an extra field on save. That's probably the best approach if you are going to be using it in multiple places.

The save function would look something ike

def save(self, *args, **kwargs):
    super().save()
    self.full_code = str(self.doc_code) + f"{doc_num:05d}") + '-' + time.strftime("%y", doc_year))
    self.save()

doc_num requires python>= 3.6, other methods for earlier pythons can be seen here

doc_year assumes it is a datetime type. If it is just a four digit int then something like str(doc_year)[-2:] should work instead.

Alternately, if you are only ever going to use it rarely you could loop through your recordset adding an additional field

docs=Document.objects.all() #or whatever filter is appropriate
for doc in docs:
    doc.full_code = f"{doc.doc_code}{doc.doc_num}-{time.strftime("%y", doc_year)}
     #or f"{doc.doc_code}{doc.doc_num}-{str(doc_year)[-2:]} if doc_year not datetime

and then convert it to a list so you don't make another DB call and lose your new field, and filter it via list comprehension.

filtered_docs = [x for x in list(docs) if search_term in x.full_code]

pass filtered_docs to your template and away you go.

Django ORM: filtering on concatinated fields

1 Answers1