order_by does not work with DecimalField

[notanumber]

Because DecimalField is stored as a string when indexing, it does not produce the expected results when using order_by.

Instead of ordering by numerical value, it instead sorts the data alphabetically.

Related issue on the Xapian backend here: notanumber/xapian-haystack#84

[notanumber]

Perhaps the best way to (quickly) address this would be to update the DecimalField documentation to indicate that order_by is not supported.

In the longer term, a WARNING leve log message could be output when the order_by operation is attempted on a DecimalField. For performance reasons, the verification of the field type could only be done when the site is in DEBUG mode.

[JoeJasinski]

One work-around for this is to store the contents of a decimal field as an integer. For example, if I have a model called MyModel with a Decimal field called price, I could multiply it by the number of decimal places and store as an integer.

class MyIndex(SearchIndex):
    ...
    price = IntegerField(model_attr='price')

    def prepare_price(self, obj):
        return int(obj.price * Decimal('100'))

[zbyte64]

This seems to work for elasticsearch but not for solr.

[strogonoff]

The bug apparently applies to Whoosh as well.

[benjaminrigaud]

Range queries fail too on elasticsearch.

[aisayko]

+1

[mikecodona]

I've hit this same issue using the SolrBackend. We are sorting by a field which contains a number between 0.0 - 1.0 representing a complex scoring algorithm. When sorting by string that means elements with a very small score are sorted above those with larger scores e.g. 9e-08

Fairly, obvious work around is to use a float field instead (doesn't really matter for my purposes).

[qris]

This is pretty bad. If it's not going to be fixed, but just documented as "order_by doesn't work", then I think we should deprecate the field type or have a clear warning in the list of field types, because it would be easy to use it by mistake, and slow to repair if you have a large database.

[rongduan-zhu]

doesn't work for elasticsearch either

[SalahAdDin]

👍

[iuliuscaesar92]

@JoeJasinski thank you so much, your answer helped me

[tommasosansone91]

One work-around for this is to store the contents of a decimal field as an integer. For example, if I have a model called MyModel with a Decimal field called price, I could multiply it by the number of decimal places and store as an integer.

class MyIndex(SearchIndex):
    ...
    price = IntegerField(model_attr='price')

    def prepare_price(self, obj):
        return int(obj.price * Decimal('100'))

And then?
MyIndex.objects.all().order_by('prepare_price') ? Right?