The weight for our objects is composed of 2 fields. One field is the weight unit (kilogram, tonne and pound) and the other field is the weight (number). I have tried making a queryset which annotates all the weights into one universal unit field. The problem is that once I order_by that annotated (unit) field, it still orders by largest number and not by largest weight.

For example 100kg is less than 50t, but the ordering system just sorts by the largest number.

This is the code inside the filters.py:

`

class AircraftFilter(FilterSet):
    tail_number = CharFilter(field_name="tail_number", lookup_expr="startswith")
    by_weight = CharFilter(method="filter_by_weight")

    class Meta:
        model = Aircraft
        fields = ("tail_number", "by_weight")

    def filter_by_weight(self, qs: QuerySet, value, *args, **kwargs):
        if value != None:
            qs = (
                qs.filter(max_takeoff_weight_unit=2).annotate(
                    mtw_lb=F("max_takeoff_weight") * 2200
                )
                | qs.filter(max_takeoff_weight_unit=1).annotate(
                    mtw_lb=F("max_takeoff_weight") * 2.2
                )
                | qs.filter(max_takeoff_weight_unit=3).annotate(
                    mtw_lb=F("max_takeoff_weight")
                )
            )
            qs = qs.order_by("mtw_lb")
        return qs

`

The query:

`qs = (Aircraft.objects.all().filter(max_takeoff_weight_unit=2).annotate(mtw_lb=F("max_takeoff_weight")*2200) | Aircraft.objects.all().filter(max_takeoff_weight_unit=1).annotate(mtw_lb=F("max_takeoff_weight") * 2.2) | Aircraft.objects.all().filter(max_takeoff_weight_unit=3).annotate(mtw_lb=F("max_takeoff_weight"))).order_by("mtw_lb")`

and the output:

`<IsActiveModelQuerySet [ID: 5 | weight: 0.05 - (t) , ID: 4 | weight: 0.20 - (t) , ID: 8 | weight: 2.00 - (t) , ID: 7 | weight: 50.00 - (lbs) , ID: 6 | weight: 100.00 - (kg) ]>`

CodePudding user response：

You can filter with a conditional expression [Django-doc], so:

from django.db.models import Case, F, When


class AircraftFilter(FilterSet):
    tail_number = CharFilter(field_name='tail_number', lookup_expr='startswith')
    by_weight = CharFilter(method='filter_by_weight')

    class Meta:
        model = Aircraft
        fields = ('tail_number', 'by_weight')

    def filter_by_weight(self, qs: QuerySet, value, *args, **kwargs):
        if value is not None:
            qs = (
                qs.annotate(
                    mtw_lb=Case(
                        When(
                            max_takeoff_weight_unit=2,
                            then=F('max_takeoff_weight') * 2200,
                        ),
                        When(
                            max_takeoff_weight_unit=1,
                            then=F('max_takeoff_weight') * 2.2,
                        ),
                        When(
                            max_takeoff_weight_unit=3,
                            then=F('max_takeoff_weight'),
                        ),
                    )
                )
            ).order_by('mtw_lb')
        return qs

or simpler:

from django.db.models import Case, F, When


class AircraftFilter(FilterSet):
    tail_number = CharFilter(field_name='tail_number', lookup_expr='startswith')
    by_weight = CharFilter(method='filter_by_weight')

    class Meta:
        model = Aircraft
        fields = ('tail_number', 'by_weight')

    def filter_by_weight(self, qs: QuerySet, value, *args, **kwargs):
        if value is not None:
            qs = (
                qs.annotate(
                    mtw_lb=F('max_takeoff_weight')
                    * Case(
                        When(
                            max_takeoff_weight_unit=2,
                            then=Value(2200),
                        ),
                        When(
                            max_takeoff_weight_unit=1,
                            then=Value(2.2),
                        ),
                        When(
                            max_takeoff_weight_unit=3,
                            then=Value(1),
                        ),
                    )
                )
            ).order_by('mtw_lb')
        return qs