通过OrderingFilter过滤平均和总和

huangapple go评论88阅读模式
英文:

Filtering avg sum via OrderingFilter

问题

需要通过OrderingFilter来筛选avg(非模型)字段。

我的models.py

  1. class Product(models.Model):
  2. store = models.ForeignKey(Store, on_delete=models.CASCADE, null=True, verbose_name="store")
  3. sku = models.CharField(max_length=255, blank=True, default="", verbose_name="SKU")
  4. name = models.CharField(max_length=255, blank=True, default="", verbose_name="name")
  5. class ProductInfo(models.Model):
  6. default_related_name = "productinfo"
  7. product = models.OneToOneField("Product", on_delete=models.CASCADE, null=True, verbose_name="product")
  8. barcode = models.CharField(max_length=255, null=True, verbose_name="barcode")
  9. price = models.DecimalField(max_digits=7, decimal_places=2, default=Decimal(0), verbose_name="price")
  10. cost_price = models.DecimalField(max_digits=7, decimal_places=2, default=Decimal(0), verbose_name="cost price")
  11. vat = models.DecimalField(max_digits=3, decimal_places=1, default=Decimal(0), verbose_name="% tax")

我通过OrderingFilter来筛选Product。

我的filters.py

  1. from products.models import Product
  2. from django_filters import OrderingFilter
  3. from django_filters.rest_framework import FilterSet
  4. class ProductFilter(FilterSet):
  5. order_by_field = "ordering"
  6. ordering = OrderingFilter(
  7. fields=(
  8. "name",
  9. (("productinfo__price"), ("price")),
  10. (("productinfo__barcode"), ("barcode")),
  11. )
  12. )
  13. class Meta:
  14. model = Product

我需要计算销售利润。这可以通过一个单独的函数或者使用注释来完成。
price-cost_price-taxes_sum=profit
但这个参数不是一个模型字段。我是否可以将它添加到过滤器中?目标是通过排序来获取利润更多的商品。

英文:

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

My models.py

  1. class Product(models.Model):
  2. store = models.ForeignKey(Store, on_delete=models.CASCADE, null=True, verbose_name="store")
  3. sku = models.CharField(max_length=255, blank=True, default="", verbose_name="SKU")
  4. name = models.CharField(max_length=255, blank=True, default="", verbose_name="name")
  5. class ProductInfo(models.Model):
  6. default_related_name = "productinfo"
  7. product = models.OneToOneField("Product", on_delete=models.CASCADE, null=True, verbose_name="product")
  8. barcode = models.CharField(max_length=255, null=True, verbose_name="barcode")
  9. price = models.DecimalField(max_digits=7, decimal_places=2, default=Decimal(0), verbose_name="price")
  10. cost_price = models.DecimalField(max_digits=7, decimal_places=2, default=Decimal(0), verbose_name="cost price")
  11. vat = models.DecimalField(max_digits=3, decimal_places=1, default=Decimal(0), verbose_name="% tax")

I filter Product via OrderingFilter

My filters.py

  1. from products.models import Product
  2. from django_filters import OrderingFilter
  3. from django_filters.rest_framework import FilterSet
  4. class ProductFilter(FilterSet):
  5. order_by_field = "ordering"
  6. ordering = OrderingFilter(
  7. fields=(
  8. "name",
  9. (("productinfo__price"), ("price")),
  10. (("productinfo__barcode"), ("barcode")),
  11. )
  12. )
  13. class Meta:
  14. 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.

答案1

得分: 2

你可以像这样向你的过滤器集添加字段:

  1. import django_filters
  2. class ProductFilter(FilterSet):
  3. profit = django_filters.CharFilter(method='profit_filter', label='profit')
  4. class Meta:
  5. model = Product
  6. fields = [
  7. 'profit'
  8. ]
  9. def profit(self, queryset, name, value):
  10. return queryset.annotate().filter()
英文:

you can add fields to your filterset like this :

  1. import django_filters
  2. class ProductFilter(FilterSet):
  3. profit = django_filters.CharFilter(method='profit_filter', label='profit')
  4. class Meta:
  5. model = Product
  6. fields = [
  7. 'profit'
  8. ]
  9. def profit(self, queryset, name, value):
  10. return queryset.annotate().filter()

答案2

得分: 0

你可以使用F expressions在QuerySet中进行<code>profit</code>的注释,然后将其简单地添加到你的<code>filter</code>中。

<h4>一个例子:</h4>

views.py

  1. from rest_framework.viewsets import ModelViewSet
  2. from django.db.models import F
  3. from myapp.models import Product
  4. from myapp.filters import ProductFilter
  5. from myapp.serializers import ProductSerializer
  6. from django_filters import rest_framework as filters
  7. class ProductViewSet(ModelViewSet):
  8. queryset = Product.objects.all()
  9. serializer_class = ProductSerializer
  10. filter_backends = [filters.DjangoFilterBackend]
  11. filterset_class = ProductFilter
  12. def get_queryset(self):
  13. qs = (
  14. super()
  15. .get_queryset()
  16. .annotate(profit= F('productinfo__price') - F('productinfo__cost_price') - F('productinfo__vat'))
  17. )
  18. return qs

filters.py

  1. from .models import Product
  2. from django_filters import OrderingFilter
  3. from django_filters.rest_framework import FilterSet
  4. class ProductFilter(FilterSet):
  5. order_by_field = "ordering"
  6. ordering = OrderingFilter(
  7. fields=(
  8. ("name", "name"),
  9. ("profit", "profit"),
  10. ("productinfo__price", "price"),
  11. ("productinfo__barcode", "barcode")
  12. )
  13. )
  14. class Meta:
  15. model = Product
  16. fields = '__all__'
英文:

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

<h4>An example:</h4>

views.py

  1. from rest_framework.viewsets import ModelViewSet
  2. from django.db.models import F
  3. from myapp.models import Product
  4. from myapp.filters import ProductFilter
  5. from myapp.serializers import ProductSerializer
  6. from django_filters import rest_framework as filters
  7. class ProductViewSet(ModelViewSet):
  8. queryset = Product.objects.all()
  9. serializer_class = ProductSerializer
  10. filter_backends = [filters.DjangoFilterBackend]
  11. filterset_class = ProductFilter
  12. def get_queryset(self):
  13. qs = (
  14. super()
  15. .get_queryset()
  16. .annotate(profit=
  17. F(&#39;productinfo__price&#39;) -
  18. F(&#39;productinfo__cost_price&#39;) -
  19. F(&#39;productinfo__vat&#39;)
  20. )
  21. )
  22. return qs

filters.py

  1. from .models import Product
  2. from django_filters import OrderingFilter
  3. from django_filters.rest_framework import FilterSet
  4. class ProductFilter(FilterSet):
  5. order_by_field = &quot;ordering&quot;
  6. ordering = OrderingFilter(
  7. fields=(
  8. (&quot;name&quot;, &quot;name&quot;),
  9. (&quot;profit&quot;, &quot;profit&quot;),
  10. (&quot;productinfo__price&quot;, &quot;price&quot;),
  11. (&quot;productinfo__barcode&quot;, &quot;barcode&quot;)
  12. )
  13. )
  14. class Meta:
  15. model = Product
  16. fields = &#39;__all__&#39;

huangapple
  • 本文由 发表于 2023年7月4日 20:31:20
  • 转载请务必保留本文链接:https://go.coder-hub.com/76612661.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定