django - 如何查询重叠的日期范围？

Question

我的成员在有限的时间内被分配到一个团队。可以将成员分配到多个团队。成员每天创建工作日志。工作日志分配给一个成员，该成员分配给一个团队。中间表 Membership 存储团队成员的分配日期。

我需要查询与特定团队相关的所有工作日志，例如2019 年第一季度。团队成员可以在该季度加入和离开，只应考虑他们团队分配期间的工作日志。

class Teamuser(models.Model):
    key = models.CharField(max_length=200, primary_key=True)
    fullname = models.CharField(max_length=200)

class Team(models.Model):
    id = models.IntegerField(primary_key=True)
    name = models.CharField(max_length=200)
    members = models.ManyToManyField(Teamuser, through='Membership')

class Membership(models.Model):
    id = models.IntegerField(primary_key=True)
    memberid = models.IntegerField(null=False, blank=False)
    teamuser = models.ForeignKey(Teamuser, on_delete=models.CASCADE)
    team = models.ForeignKey(Team, on_delete=models.CASCADE)
    dateFrom = models.DateField(null=True, blank=True, default=None)
    dateTo = models.DateField(null=True, blank=True, default=None)

class Worklog(models.Model):
    worker = models.ForeignKey(Teamuser, on_delete=models.PROTECT)
    day = models.DateField(null=True, blank=True, default=None)
    effort = models.IntegerField()
    comment = models.TextField(null=True)

我试图首先获取在该时间范围内分配的所有用户的列表。

start = date(2019,1,10) 
end = date(2019,1,20) 
Teamuser.objects.filter(Q(membership__team_id=305), Q(membership__dateFrom__range=[start, end]) | Q(membership__dateTo__range=[start, end]) | Q(membership__dateFrom=None) | Q(membership__dateTo=None))  

但是，这不包括在查询日期之前加入团队并在之后离开的人员。没有 dateFrom 和 dateTo 的人总是在团队中。

目前我最大的挑战是找出两个日期范围(查询 Q1，团队用户的成员资格)重叠的日期

我希望输出一个团队用户在分配给查询团队(例如仅 2019 年 1 月)期间(2019 年第一季度)期间创建的所有工作日志。

Answer 1

这会为您提供在所考虑的时间段内某个时间点作为团队成员的用户:

teamusers = (Teamuser.objects
    .filter(
        Q(membership__dateTo__gte=start) | Q(membership__dateTo=None),
        Q(membership__dateFrom__lte=end) | Q(membership__dateFrom=None),
        membership__team_id=305, 
    )
    .distinct()
)

此过滤器过滤为空、大于或等于期间开始日期的结束日期，以及为空、小于或等于结束日期的开始日期。

但这最多对预过滤工作日志有用，因为您不能仅在考虑的时间段内获取列表中所有成员的日志并完成它。您可能拥有用户在其成员(member)资格日期之外发布的工作日志。

因此，您必须从日志表开始，针对所讨论的时间段和可能针对上面计算的团队成员列表对其进行预过滤(如果您确定它可以提高性能)。

worklogs = Worklog.objects.filter(day__range=[start, end], worker__in=teamusers)

然后您使用子查询检查每条记录，在该特定日期发布它的用户是否是团队成员:

from django.db.models import Q, OuterRef, Exists

membership_subquery = Teamuser.objects.filter(
    Q(membership__dateTo__gte=OuterRef('day')) | Q(membership__dateTo=None),
    Q(membership__dateFrom__lte=OuterRef('day')) | Q(membership__dateFrom=None),
    membership__team_id=305,
    teamuser=OuterRef('worker'), 
)

worklogs = (worklogs
    .annotate(in_team=Exists(membership_subquery))
    .filter(in_team=True)
)

现在您有了所有有效工作日志的列表，您可以附加进一步的处理，例如注释聚合等。