Filtering avg sum via OrderingFilter

Question

I need to filter avg (non-model) field via OrderingFilter.

My models.py

class Product(models.Model):
    store = models.ForeignKey(Store, on_delete=models.CASCADE, null=True, verbose_name="store")
    sku = models.CharField(max_length=255, blank=True, default="", verbose_name="SKU")
    name = models.CharField(max_length=255, blank=True, default="", verbose_name="name")

class ProductInfo(models.Model):
    default_related_name = "productinfo"

    product = models.OneToOneField("Product", on_delete=models.CASCADE, null=True, verbose_name="product")
    barcode = models.CharField(max_length=255, null=True, verbose_name="barcode")
    price = models.DecimalField(max_digits=7, decimal_places=2, default=Decimal(0), verbose_name="price")
    cost_price = models.DecimalField(max_digits=7, decimal_places=2, default=Decimal(0), verbose_name="cost price")
    vat = models.DecimalField(max_digits=3, decimal_places=1, default=Decimal(0), verbose_name="% tax")

I filter Product via OrderingFilter

My filters.py

from products.models import Product
from django_filters import OrderingFilter
from django_filters.rest_framework import FilterSet

class ProductFilter(FilterSet):
    order_by_field = "ordering"
    ordering = OrderingFilter(
        fields=(
            "name",
            (("productinfo__price"), ("price")),
            (("productinfo__barcode"), ("barcode")),
        )
    )

    class Meta:
        model = Product

I need to calculate the profit from the sales. This can be done in a separate function or I can use an annotation. price-cost_price-taxes_sum=profit But this parameter is not a model field. Can I somehow add it to the filter? The goal is to get goods that bring more profit by sorting.

Answer 1

you can add fields to your filterset like this :

import django_filters

class ProductFilter(FilterSet):
    profit = django_filters.CharFilter(method='profit_filter', label='profit')

    class Meta:
        model = Product
        fields = [
            'profit'
            ]

    def profit(self, queryset, name, value):
        return queryset.annotate().filter()

Answer 2

You can annotate the QuerySet with profit using F expressions and simply add it to your filter.

An example:

views.py

from rest_framework.viewsets import ModelViewSet

from django.db.models import F
from myapp.models import Product
from myapp.filters import ProductFilter
from myapp.serializers import ProductSerializer

from django_filters import rest_framework as filters


class ProductViewSet(ModelViewSet):
    queryset = Product.objects.all()
    serializer_class = ProductSerializer
    filter_backends = [filters.DjangoFilterBackend]
    filterset_class = ProductFilter

    def get_queryset(self):
        qs = ( 
            super()
            .get_queryset()
            .annotate(profit=
                      F('productinfo__price') -
                      F('productinfo__cost_price') - 
                      F('productinfo__vat')
            )
        )
        return qs

filters.py

from .models import Product
from django_filters import OrderingFilter
from django_filters.rest_framework import FilterSet


class ProductFilter(FilterSet):
    order_by_field = "ordering"
    ordering = OrderingFilter(
        fields=(
            ("name", "name"),
            ("profit", "profit"),
            ("productinfo__price", "price"),
            ("productinfo__barcode", "barcode")
        )
    )

    class Meta:
        model = Product
        fields = '__all__'