如何用创建后的天数注释查询集 [英] How to annotate a queryset with number of days since creation

问题描述

我正在尝试基于促销进行一些复杂的排序：自创建以来，文章每7天就会进行一次推广（文章在30天后过期）。

I'm trying to do some complex ordering based on promotions: an article is promoted every 7th day since creation (the articles expire after 30 days).

我的方法是用自创建以来的天数对查询集进行注释，但是在我的代码中，带注释字段（ days_since_creation ）的值始终为0。

My approach is to annotate the queryset with the number of days since it was created, but the value of the annotated field (days_since_creation), in my code, is always 0.

from datetime import timedelta
from django.test import TestCase
from django.db.models import ExpressionWrapper, F
from django.db.models.fields import IntegerField
from django.utils import timezone
from .models import Article
    # class Article(models.Model):
    #     title = models.CharField(max_length=150)
    #     creation_date = models.DateTimeField()
    # 
    #     def __str__(self):
    #         return self.title


class ArticleTestCase(TestCase):

    def test_days_since_creation(self):
        now = timezone.now()
        objects_data = [
            Article(
                title='Alrp',
                creation_date=(now - timedelta(days=5)) # 5 days ago
            ),
            Article(
                title='Bopp',
                creation_date=(now - timedelta(days=7)) # 7 days ago
            ),
            Article(
                title='Crkp',
                creation_date=(now - timedelta(days=14)) # 14 days ago
            ),
        ]
        Article.objects.bulk_create(objects_data)
        article_set = Article.objects\
            .annotate(
                days_since_creation=ExpressionWrapper(
                    now - F('creation_date'),
                    output_field=IntegerField()
                )
            )
        for article in article_set:
            print(article.days_since_creation)
        # all 3 objects print "0"

我希望每个对象的值分别为5、7、14。我什至尝试了 DurationField ，但是只打印了0:00:00。

I expected the value for each object to be 5, 7, 14 repectively. I even tried DurationField but that just printed 0:00:00.

之后，我将再次注释queryset如果 days_since_past 的值在 [0中，则其 order 字段的值为0 ，7、14、21、28，] 或其他1个，然后 order_by（'order'）。

After that I would annotate the queryset again with an order field that has the value 0 if the value of days_since_past is in [0, 7, 14, 21, 28,] or 1 otherwise, and then order_by('order').

推荐答案

有点黑，但可以。您需要将 now 转换为 DateTimeField 。结果将在 DurationField 中-timedelta的标准。对于订单，我们将使用 Case 来检查是否在选定的持续时间内，然后对其进行排序。

It's a little bit hacky, but works. You need to convert now to DateTimeField. Result will be in DurationField - standard for timedelta. For order we are going to use Case to check whether or not we are in selected duration and then we sort it.

我专门将 Articles 的初始顺序更改为测试顺序。

I specifically change initial order of Articles to test ordering.

from datetime import timedelta
from django.test import TestCase
from django.db.models import ExpressionWrapper, F, Value, Case, When
from django.db.models.fields import DateTimeField, DurationField, BooleanField
from django.utils import timezone
from .models import Article


class ArticleTestCase(TestCase):
    def test_days_since_creation(self):
        now = timezone.now()

        order_in_variants = [
            timedelta(days=0),
            timedelta(days=7),
            timedelta(days=14),
            timedelta(days=21),
            timedelta(days=28),
        ]

        objects_data = [
            Article(
                title='Crkp',
                creation_date=(now - timedelta(days=14))  # 14 days ago
            ),
            Article(
                title='Alrp',
                creation_date=(now - timedelta(days=5))  # 5 days ago
            ),
            Article(
                title='Bopp',
                creation_date=(now - timedelta(days=7))  # 7 days ago
            ),
        ]
        Article.objects.bulk_create(objects_data)
        article_set = Article.objects.all().annotate(
            days_since_creation=ExpressionWrapper(
                Value(now, DateTimeField()) - F('creation_date'),
                output_field=DurationField()
            )
        ).annotate(
            order=Case(
                When(days_since_creation__in=order_in_variants, then=Value(False)),
                default=Value(True),
                output_field=BooleanField(),
            )
        ).order_by('order')
        for article in article_set:
            print(article.days_since_creation.days, article.order)
        # 14 False
        # 7 False
        # 5 True

您还可能希望检查 days_since_creation 范围，而不是恰好相差7天。使其更加丑陋，但仍然：

You also might want to check that days_since_creation in range instead of being exactly 7 days difference. Makes it even uglier, but still:

order_in_variants = [
            (timedelta(days=0), timedelta(days=0, hours=23, minutes=59, seconds=59, microseconds=999999)),
            (timedelta(days=7), timedelta(days=7, hours=23, minutes=59, seconds=59, microseconds=999999)),
            (timedelta(days=14), timedelta(days=14, hours=23, minutes=59, seconds=59, microseconds=999999)),
            (timedelta(days=21), timedelta(days=21, hours=23, minutes=59, seconds=59, microseconds=999999)),
            (timedelta(days=28), timedelta(days=28, hours=23, minutes=59, seconds=59, microseconds=999999)),
        ]
# ...
order=Case(
                When(days_since_creation__range=order_in_variants[0], then=Value(False)),
                When(days_since_creation__range=order_in_variants[1], then=Value(False)),
                When(days_since_creation__range=order_in_variants[2], then=Value(False)),
                When(days_since_creation__range=order_in_variants[3], then=Value(False)),
                When(days_since_creation__range=order_in_variants[4], then=Value(False)),
                default=Value(True),
                output_field=BooleanField(),
            )

这篇关于如何用创建后的天数注释查询集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！