How to use count() to get items between specific dates

With this code I am able to get all orders items that is related to Article but here is the problem, I want to get the number of orders that are between two dates that are between the assigned timestamp.How can I do it?


class Order(models.Model):

    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    author = models.ForeignKey(User,  on_delete=models.CASCADE,related_name='ordersauthor')
    article = models.ForeignKey(Article, null=True, blank=True, on_delete=models.CASCADE,related_name='ordersarticle')
    timestamp = models.DateTimeField(auto_now_add=True)
    total_price = models.FloatField(default='0', validators=[MaxValueValidator(99999999999999999999)])

class ArticleSerializer(serializers.ModelSerializer):
    orders = serializers.SerializerMethodField(read_only=True)


    class Meta:
        model = Article
        fields = '__all__'

    def get_orders(self, language):
        return language.ordersarticle.count()

Initial Output:

{
            .....,
             "orders": 4, // All orders
        },

Expected Output:

{
            .....,
             "orders": 1, // Orders between 4th and 11th of march (Example)
        },

Update:

class ArticleViewSet(viewsets.ModelViewSet):
    permission_classes = (IsAuthenticated,)
    queryset = Article.objects.all().order_by('-timestamp')
    serializer_class = ArticleSerializer
    filter_backends = [filters.SearchFilter,DjangoFilterBackend]
    pagination_class = StandardResultsSetPagination
    search_fields = ['^articlechoices__item','^caption','^name']
    filterset_fields = ['author','price','location','category']

You can filter with:

from datetime import date


class ArticleSerializer(serializers.ModelSerializer):
    orders = serializers.SerializerMethodField(read_only=True)

    class Meta:
        model = Article
        fields = '__all__'

    def get_orders(self, language):
        return language.ordersarticle.filter(
            timestamp__range=(date(2024, 3, 4), date(2024, 3, 7))
        ).count()

but this is not a good idea: if you have to serialize for example multiple such Articles, it will make a query per item.

We can calculate these in advance with:

from django.db.models import Count, Q


class ArticleViewSet(viewsets.ModelViewSet):
    permission_classes = (IsAuthenticated,)
    queryset = Article.objects.all().order_by('-timestamp')
    serializer_class = ArticleSerializer
    filter_backends = [filters.SearchFilter, DjangoFilterBackend]
    pagination_class = StandardResultsSetPagination
    search_fields = ['^articlechoices__item', '^caption', '^name']
    filterset_fields = ['author', 'price', 'location', 'category']

    def get_queryset(self, *args, **kwargs):
        return (
            super()
            .get_queryset(*args, **kwargs)
            .annotate(
                num_orders=Count(
                    'ordersarticle',
                    filter=Q(
                        ordersarticle__timestamp__range=(date(2024, 3, 4), date(2024, 3, 7))
                    ),
                )
            )
        )

and serialize with:

class ArticleSerializer(serializers.ModelSerializer):
    orders = serializers.IntegerField(read_only=True, source='num_orders')

    class Meta:
        model = Article
        fields = '__all__'

Note: It is normally better to make use of the settings.AUTH_USER_MODEL [Django-doc] to refer to the user model, than to use the User model [Django-doc] directly. For more information you can see the referencing the User model section of the documentation.

Note: The related_name=… parameter [Django-doc] is the name of the relation in reverse, so from the User model to the Order model in this case. Therefore it (often) makes not much sense to name it the same as the forward relation. You thus might want to consider renaming the ordersauthor relation to orders.

Back to Top