python - Django:查询集对象过滤器，针对另一个对象的时间范围

Question

我有 3 个模型，run、sensor_parameter 和 data。它们之间还有其他外键关系，但run 没有直接指向sensor_parameter 或data 的外键。

run 具有start_time 和end_time，并且与chamber 相关。

class Run(models.Model):
    start_time = models.DateTimeField(db_index=True)
    end_time = models.DateTimeField(db_index=True)
    chamber = models.ForeignKey(Chamber, on_delete=models.CASCADE)

chamber 与sensor 有关系，sensor 有一组sensor_parameter(s)

class SensorParameter(models.Model):
    sensor = models.ForeignKey(Sensor)
    parameter = models.ForeignKey(Parameter)

data 点最终“属于”sensor_parameter:

class Data(models.Model):
    time = models.DateTimeField(db_index=True)
    sensor_parameter = models.ForeignKey(SensorParameter, on_delete=models.CASCADE)
    parameter_value = models.FloatField()

我需要过滤属于run 的sensor_parameter(s) 列表，但它们之间的唯一联系是时间值。由于 data 有时间戳，而 run 有 start_time 和 end_time，我想我可以过滤一个列表data.sensor_parameter 在一个时间段范围内。

我不确定如何构建该查询集过滤器。

我已经导入了日期时间，并且可以访问 django_filter。

这是我目前在 views.py 中的内容

import datetime    
import django_filters

def get(self, request):

    # Get a list of run objects, that are passed through the request
    run_ids = request.GET.getlist('id')
    runs = Run.objects.filter(pk__in=run_ids)

    # Get a list of all chambers that own those runs
    chamber = Chamber.objects.filter(run__in=runs).distinct()

    # Get a list of all sensors that belong to those chambers
    sensor = Sensor.objects.filter(chamber=chamber)

    # Looking around, I saw these two DateTimeFilter expressions from django_filters
    time__gte = django_filters.DateTimeFilter(name="time", lookup_expr='gte')
    time__lte = django_filters.DateTimeFilter(name="time", lookup_expr='lte')

    # Here I would have to determine which run.start_time is lower
    # And which run.end_time is higher, to get a valid time range
    # This part is not finished yet
    time_start = run.start_time
    time_end = run.end_time

    # This is the filter I'm having trouble implementing
    sensor_parameters = SensorParameter.objects.filter(sensor=sensor, data__time__gte=time_start, data__time__lte=time_end)

    return render(request, self.template_name, {'run': run, 'sensor_parameters': sensor_parameters})

基本上，我想如果我从我的运行中提取一个开始时间和一个结束时间(它可以不止一次运行，我必须确定哪个 start_time 较低，end_time 较大)，然后我可以使用时间范围根据 data.time 过滤我的 sensor_parameter。

我不知道如何从这里开始。

此外，如果有任何不同，我们会使用 PostgreSQL。

如果您看到任何明显的错误或语法错误，请随时纠正和批评，我仍然是 Django 和 Python 的新手，但喜欢它的每一分钟。

Answer 1

我自己设法做到了，但我不确定我这样做的方式是否是最好的方式。我将保持上面的代码不变，以演示问题出在哪里，并提供与我实现的解决方案的比较点。

日期范围的想法是正确的，因为 data.time 是我到 run.start_time - run.end_time 的唯一链接。

所以我通过将所有 run.start_time 和 run.end_time 添加到几个列表来确定 range_period，然后我调用 <这些列表上的 strong>min() 和 ma​​x() 方法提取所需的时间段，然后将其放入列表中，这就是(包含的)__range 过滤器需要作为输入。

为了清楚起见，下面是带有注释的 views.py:

class ChartRunsView(generic.DetailView):
    model = Run
    template_name = 'chart_runs.html'

    def get(self, request):

        # Get a list of run objects, that are passed through the request
        run_ids = request.GET.getlist('id')
        runs = Run.objects.filter(pk__in=run_ids)

        # Get a list of all chambers that own those runs
        chamber = Chamber.objects.filter(run__in=runs).distinct()

        # Get a list of all sensors that belong to those chambers
        sensor = Sensor.objects.filter(chamber=chamber)

        # Initialize run_start and run_end lists
        run_start_list = []
        run_end_list = []

        # Append start_time(s) and end_time(s) for all runs
        for run in runs:
            run_start_list.append(run.start_time)
            run_end_list.append(run.end_time)

        # Determine the earliest and latest time stamps for all the extracted run times
        # Using Python's min() and max() list methods   
        runs_start = min(run_start_list)
        runs_end = max(run_end_list)

        # Build a new list with the earliest run_start and latest run_end
        range_period = [runs_start, runs_end]

        # Build query that will filter all SensorParameter objects against sensor, and data
        # Using the __range filter method, which takes a list as input.
        # This will return all SensorParameter objects in that time range, which could potentially
        # be thousands. Applying the .distinct() filter method on the resulting queryset gives us
        # only unique results
        sensor_parameters = SensorParameter.objects.filter(sensor__in=sensor, data__time__range=range_period).distinct()

        return render(request, self.template_name, {'runs': runs,
                                                    'sensor_parameters': sensor_parameters})