Django 用频率进行注释

发布于 2025-01-17 02:02:31 字数 1318 浏览 3 评论 0原文

（django 3.2.12，python 3.9.3，MySQL 8.0.28）

想象一下如下所示的模型：

class User(models.Model):
    email = models.EmailField(...)
    created_datetime = models.DatetimeField(...)

class UserLog(models.Model):
    created_datetime = models.DatetimeField(...)
    user = models.ForeignKey('user.User' ...)
    login = models.BooleanField('Log in' ..)

以及以下查询，注定要使用日志频率注释查询集中的每个用户（当 log.login= True）：

users = User.objects.filter(
    Q(...)
).annotate(
    login_count=Count('userlog', filter=Q(userlog__login=True)),
    login_duration_over=Now() - F('created_datetime'),
    login_frequency=ExpressionWrapper(
        F('login_duration_over') / F('login_count'),
        output_field=models.DurationField()
    ),
)

这会导致 SQL 错误：

(1064, "You have an error in your SQL syntax;)

生成的 SQL（login_Frequency 的片段）如下所示：

(
  INTERVAL TIMESTAMPDIFF(
    MICROSECOND,
    `user_user`.`created_datetime`,
    CURRENT_TIMESTAMP
  ) MICROSECOND / (
    COUNT(
        CASE WHEN `user_userlog`.`login` THEN `user_userlog`.`id` ELSE NULL END
    )
  )
) AS `login_frequency`,

MySQL 似乎不喜欢它。据我所知，类似的代码可以在 SQLlite 上运行，并且可以在 PG 上运行。

MySQL 上的ExpressionWrapper 有什么问题吗？

原文

(django 3.2.12, python 3.9.3, MySQL 8.0.28)

Imagine models like the following:

class User(models.Model):
    email = models.EmailField(...)
    created_datetime = models.DatetimeField(...)

class UserLog(models.Model):
    created_datetime = models.DatetimeField(...)
    user = models.ForeignKey('user.User' ...)
    login = models.BooleanField('Log in' ..)

And the following query, destined to annotate each user in the queryset with the frequency of their logs(when log.login=True):

users = User.objects.filter(
    Q(...)
).annotate(
    login_count=Count('userlog', filter=Q(userlog__login=True)),
    login_duration_over=Now() - F('created_datetime'),
    login_frequency=ExpressionWrapper(
        F('login_duration_over') / F('login_count'),
        output_field=models.DurationField()
    ),
)

This results in a SQL error:

(1064, "You have an error in your SQL syntax;)

The generated SQL (fragment for login_frequency) looks like this:

(
  INTERVAL TIMESTAMPDIFF(
    MICROSECOND,
    `user_user`.`created_datetime`,
    CURRENT_TIMESTAMP
  ) MICROSECOND / (
    COUNT(
        CASE WHEN `user_userlog`.`login` THEN `user_userlog`.`id` ELSE NULL END
    )
  )
) AS `login_frequency`,

and MySQL does not seem to like it. A similar code works on SQLlite and, I am told on PG.

What is wrong with the ExpressionWrapper on MySQL, any idea?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

拔了角的鹿 2025-01-24 02:02:31

找到了一个解决方法：

users = User.objects.filter(
    Q(...)
).annotate(
    login_count=Count('userlog', filter=Q(userlog__login=True)),
    login_duration_over=Now() - F('created_datetime'),
    login_frequency=Cast(
        ExpressionWrapper(
            Cast(F('login_duration_over'), output_field=models.BigIntegerField()) / F('login_count'),
            output_field=models.BigIntegerField()
        ),
        output_field=models.DurationField()
    )
)

这会强制 DIVIDE 操作在 bigint 上的数据库端执行，完成后将其转换回 timedelta。

MySQL 不再尖叫，结果是正确的。

尽管这有效，但这感觉很丑陋。难道就没有更好的办法吗？

Found a workaround:

users = User.objects.filter(
    Q(...)
).annotate(
    login_count=Count('userlog', filter=Q(userlog__login=True)),
    login_duration_over=Now() - F('created_datetime'),
    login_frequency=Cast(
        ExpressionWrapper(
            Cast(F('login_duration_over'), output_field=models.BigIntegerField()) / F('login_count'),
            output_field=models.BigIntegerField()
        ),
        output_field=models.DurationField()
    )
)

this forces the DIVIDE operation to be performed db-side on bigints and once that is done, cast it back to a timedelta.

MySQL stopped screaming and the results are correct.

Even though that work, this feels ugly. Could there not be a better way?

回复收藏 0 原文

~没有更多了~