@Arakkl_Abu answer is correct. I played around a bit and tried rebuilding your python method with database functions. It yould be something similar to this:
#admin.py
from django.db.models import Case, Value, When, IntegerField, FloatField, F
from django.db.models.functions import Substr, StrIndex, Cast, Replace, Length
class YelpCompanyAdmin(admin.ModelAdmin):
list_display = ('title','url','messaged','city','annual_revenue','not_available', 'show_annual_revenue1')
def get_queryset(self, request):
qs = YelpCompany.objects.filter(messaged=False,not_available=False).annotate(
field_case=Case(
When(annual_revenue__contains="less than", then=Value(1)),
When(annual_revenue__contains="million", then=Value(2)),
When(annual_revenue__iregex=r'^[a-zA-Z]+/$', then=Value(3)),
When(annual_revenue=None, then=Value(3)),
When(annual_revenue=r'^[/w]+/$', then=Value(3)),
default=Value(4),
output_field=IntegerField()
)
).annotate(
index_nr=Case(
When(field_case=1, then=StrIndex('annual_revenue', Value('less than')) + Length(Value('less than'))),
When(field_case=2, then=StrIndex('annual_revenue', Value('up to')) + Length(Value('up to'))),
When(field_case=3, then=Value(0)),
default=Value(-1),
output_field=IntegerField()
)
).annotate(
annual_revenue1=Case(
When(index_nr__gt=0, then=Cast(Replace(
Substr(F('annual_revenue'), F("index_nr")), Value('$'), Value('')),
output_field=FloatField())),
When(index_nr=0, then=Value(0)),
default=Cast(F('annual_revenue'), output_field=FloatField())
)
)
return qs
def show_annual_revenue1(self, inst):
return inst.annual_revenue1
show_annual_revenue1.admin_order_field = 'annual_revenue1'
The annotations create a new annotation annual_revenue1 containing the numeric value of annual_revenue only. This cn be used for ordering. The ModelAdmin above has a new column in the list display called show_annual_revenue1, which is used for default ordering.
A post dealing with using annotations for ordering is here.
A few words of explanation:
The first 'Case' annotation sorts the 'annual_revenue' entries into groups: 1. The field contains 'less than', 2. The field contains 'million', 3. The field contains letters, is None or empty, 4. If none of the above apply it is assumed the field contains a numeric value. You might need to adapt this to your special use-case if other cases apply.
In the second 'Case' annotation we find the index for exracting the substring, similar to your split() command. Fields which do not contain a valid numeric value as defined in the first Case annotation or which are numeric values are marked with index_nr '0' or '-1'.
In the third annotation block we extract the numeric substring or just return 0 (if the field does not hold a valid numeric value) or return the value of the field if it can be used as is. The returned values are cast to numeric values so we get the correct sorting.
Approach 2
The drawback of the approach above is that it is not very flexible and rather "longish". Another approach could be removing all the strings at the beginning:
#admin.py
from django.db.models import Case, Value, When, F, FloatField
from django.db.models.functions import Cast, Replace
#admin.py
from django.db.models import Case, Value, When, IntegerField, FloatField, F
from django.db.models.functions import Substr, StrIndex, Cast, Replace, Length
class YelpCompanyAdmin(admin.ModelAdmin):
list_display = ('title','url','messaged','city','annual_revenue','not_available', 'show_annual_revenue1')
def get_queryset(self, request):
replace_strings = ['million', 'milion', 'up to', '$', 'less than', 'False']
qs = YelpCompany.objects.filter(messaged=False,not_available=False)
qs = qs.annotate(
annual_revenue1 = F('annual_revenue')
)
for s in replace_strings:
qs = qs.annotate(
annual_revenue1 = Replace(F('annual_revenue1'), Value(s), Value(''))
)
qs = qs.annotate(
annual_revenue1 = Case(
When(annual_revenue1=None, then=Value(0.0)),
default=Cast(Trim(F('annual_revenue1')), FloatField())
)
)
return qs.order_by('annual_revenue1')
def show_annual_revenue1(self, inst):
return inst.annual_revenue1
show_annual_revenue1.admin_order_field = 'annual_revenue1'
This give the fleixbility to add whatever strings can appear to the list. You could event create your list of replace strings dynamically from you initial queryset if you like:
replace_strings2 = []
for q in qs.values_list('annual_revenue', flat=True):
if not q is None:
s = ''.join([x for x in q if not q.isdigit()])
replace_strings2.extend(s.split())
replace_strings = []
for s in replace_strings2:
try:
float(s)
except ValueError:
replace_strings.append(s)
