Sort results by a part of a fields' string in django

user_3815432 0
Viewed 40

I have such Django models:

class SavedVariant(models.Model):
    family = models.ForeignKey('Family', on_delete=models.CASCADE)

class Family(models.Model):
    family_id = models.CharField(db_index=True, max_length=100)

family_id is of such a pattern: CCNNNNNN_CCC_CCCC where C is a character and N is an integer, e.g. SF0637658_WGS_HGSC. I want to order (django order_by?) my SavedVariant models first by preceding chars - CC - then by the number - NNNNNN - and finally by the last chars. Is it possible to do in Django? What would you recommend?

Update

I tried to extract integer from the field, but so far unsuccessful. Here is where I am at:

variant_query = variant_query.annotate(
    family_int=Cast(family__family_id__regex=r'\d+', output_field=IntegerField())
).order_by('family_int')

Obviously its just a part of the problem that I am trying to solve. Need to extract an integer part within Cast from family_id and not sure how.

django
1 Answers

I understand you want to ignore the _CCC_ part of the string. Then you could sort by the entire string.

The general idea would be to use annotate to create a virtual field at the database level (the SQL will return this computed field).

On that field you can store an extract of your original field, so that it contains the parts you want, and you can do this with a combination of the query functions Substr and Concat.

Then you can sort by that annotated field. Something like this:

from django.db.models.functions import Concat, Substr

sorted = Family.objects.annotate(sorting_field=Concat(
    Substr('family_id', 1, 8),
    Substr('family_id', 14, 4)  # Make sure I didn't count the position wrong
)).order_by('sorting_field')

# Check the output
import json
print(json.dumps(
    [item.sorting_field for item in sorted],
    indent=4
))

Now, if you want to have this behavior to be the default one (so you don't have to manually do it every time you query the contents) you will have to use a custom Manager (or a ModelAdmin if you are using that) and override its get_queryset() method to include the annotated field.

Then you can include the ordering attribute in the Meta class of your model. In this example I assume you are not using a ModelAdmin:

class FamilyManager(models.Manager):
    def get_queryset(self):
        return super().get_queryset().annotate(sorting_field=.....)  # as seen above

class Family(models.Model):
    family_id = models.CharField(db_index=True, max_length=100)
    objects = FamilyManager
    class Meta:
        ordering = ('sorting_field',)
user_44776590
Related