我们可以有两种方法:
- 这是我的解决方案,有查询集,没有任何循环.为了找到
start
次和end
次之间的交集,我将查询集分为4类:
- start1->;开始2->;end1->;end2(前面的q_幻灯片)(交叉点=end1-start2)
- start1->;开始2->;end2->;end1(q_包含)(交叉点=end2-起点2)
- 开始2->;start1->;end1->;end2(包含q_)(交叉点=end1-start1)
- 开始2->;start1->;end2->;end1(q_幻灯片后)(交叉点=end2-start1)
from django.db.models import OuterRef, ExpressionWrapper,\
F, Q, functions, DurationField, FloatField
# Use this function instead of "annotate_delta" if your db supports DurationField (postgres supports this but sqlite does not)
def annotate_delta_with_duration_support(qs, start, end):
duration_exp = ExpressionWrapper(end - start, output_field=DurationField())
return qs.annotate(delta=functions.ExtractMinute(duration_exp))
def annotate_delta(qs, start, end):
duration_exp = ExpressionWrapper((end - start) / (60 * 10**6),
output_field=FloatField())
return qs.annotate(delta=duration_exp)
x = 'mycar'
m = 20
q_is_contained = Q(start__gte=OuterRef('start')) & Q(end__lte=OuterRef('end'))
qs = annotate_delta(Travel.objects, F('start'), F('end'))
qs = qs.filter(q_is_contained, delta__gte=m, car=x, road=OuterRef('road'))
res1 = Travel.objects.exclude(car=x).annotate(ex=Exists(qs)).filter(ex=True)
q_contains = Q(start__lte=OuterRef('start')) & Q(end__gte=OuterRef('end'))
qs = annotate_delta(Travel.objects, OuterRef('start'), OuterRef('end'))
qs = qs.filter(q_contains, delta__gte=m, car=x, road=OuterRef('road'))
res2 = Travel.objects.exclude(car=x).annotate(ex=Exists(qs)).filter(ex=True)
q_slides_before = Q(start__lte=OuterRef('start')) & \
Q(end__lte=OuterRef('end')) & \
Q(end__gte=OuterRef('start'))
qs = annotate_delta(Travel.objects, OuterRef('start'), F('end'))
qs = qs.filter(q_slides_before, delta__gte=m, car=x, road=OuterRef('road'))
res3 = Travel.objects.exclude(car=x).annotate(ex=Exists(qs)).filter(ex=True)
q_slides_after = Q(start__gte=OuterRef('start')) & \
Q(end__gte=OuterRef('end')) & \
Q(start__lte=OuterRef('end'))
qs = annotate_delta(Travel.objects, F('start'), OuterRef('end'))
qs = qs.filter(q_slides_after, delta__gte=m, car=x, road=OuterRef('road'))
res4 = Travel.objects.exclude(car=x).annotate(ex=Exists(qs)).filter(ex=True)
res = res1 | res2 | res3 | res4
- 感谢@SUTerliakov,我发现还有一个更简洁的解决方案:
from django.db.models import OuterRef, ExpressionWrapper,\
F, functions, DurationField, FloatField
# Use this function instead of "annotate_delta" if your db supports DurationField (postgres supports this but sqlite does not)
def annotate_delta_with_duration_support(qs, start, end):
duration_exp = ExpressionWrapper(end - start, output_field=DurationField())
return qs.annotate(delta=functions.ExtractMinute(duration_exp))
def annotate_delta(qs, start, end):
duration_exp = ExpressionWrapper((end - start) / (60 * 10**6),
output_field=FloatField())
return qs.annotate(delta=duration_exp)
x = 'mycar'
m = 20
qs = annotate_delta(Travel.objects, functions.Greatest(F('start'), OuterRef('start')),
functions.Least(F('end'), OuterRef('end')))
qs = qs.filter(q_slides_after, delta__gte=m, car=x, road=OuterRef('road'))
res = Travel.objects.exclude(car=x).annotate(ex=Exists(qs)).filter(ex=True)