Django数据库：按相关数据库中最新对象的字段排序

问题描述：

我在Django中有两个数据库表：设备表用于存储实际设备，设备测试表用于存储每个设备的测试检查（对一个设备进行多次测试）。

I have two db tables in django: "Device" table to store actual devices and "DeviceTest" table to store test checks for each device (Many Tests to one Device).

class Device(models.Model):
    created_at = models.DateTimeField(auto_now_add=True)
    name = models.CharField(max_length=20)
    serial_number = models.CharField(max_length=20)

    def __unicode__(self):
        return self.name + " - " + self.serial_number

class DeviceTest(models.Model):
    device = models.ForeignKey(Device)
    created_at = models.DateTimeField(auto_now_add=True)

    TEST_OK = '+'
    TEST_ERROR = '-'
    TEST_RESULT_CHOICES = (
        (TEST_OK, 'Test Success'),
        (TEST_ERROR, 'Test Error'),
    )
    status = models.CharField(max_length=1, choices=TEST_RESULT_CHOICES, default=TEST_ERROR)

    comment = models.TextField(blank=True, default="")
    tester = models.CharField(max_length=255)
    action = models.CharField(max_length=255)

    def __unicode__(self):
        return  self.device.name + " - " + \
                self.device.serial_number + " - " + \
                unicode(self.created_at) + " - " + \
                u"Result (" + self.status + ")"

我制作了包含所有设备表的HTML页面，格式为：

I've made HTML page with table of all devices in format:

name | serial number | created_at | last_test_time | last_test_status | hyperlink to test table page

现在，我想为用户提供所有排序的可能性。我已经通过名称，序列号和 created_at字段的GET参数完成了此操作。现在我受困于 last_test_time和 last_test_status字段。

Now I want to give user all sorting possibilities. I've done that through GET parameters for "name", "serial number" and "created_at" fields. And now I'm stuck with "last_test_time" and "last_test_status" fields.

可以使用某些标准Django函数解决此问题吗？类似于 Device.objects.all（）。filter（???）。order_by（???）

Can this problem be solved with some standard Django functions? Something like Device.objects.all().filter(???).order_by(???)

答

您可以使用最新的 created_at 时间对查询集进行注释。

You can annotate the queryset with the latest created_at time.

from django.db.models import Max

queryset = Device.objects.annotate(last_test_time=Max('devicetest__created_at'))

您可以为每个对象访问 last_test_time 。

You can access last_test_time for each object.

for obj in queryset
    print(obj.last_test_time)

您可以通过此字段排序查询集

And you can order the queryset by this field

queryset = queryset.order_by('last_test_time')

但是，这只是部分答案，因为它不能帮助您获得该测试的状态。

However, this is only a partial answer, because it does not help you get the status of that test.

您可以分别为每个设备进行查找。

You could do a lookup for each device individually.

for device in queryset:
    device.latest_status = device.device_test.latest('created_at').status

如果设备数量少，则可以，但是这意味着对每个设备进行一次查询，

This is ok if the number of devices is small, but it means one query for each device, which isn't ideal as the number of devices increases.

您可以使用 prefetch_related 可以在一个查询中获取所有相关的设备测试。（下面的代码未经测试，我的语法可能有些错误）。

You can use prefetch_related to fetch all the related device tests in one query. (the code below is untested, I might have the syntax slightly wrong).

queryset = queryset.prefetch_related(Prefetch('devicetest', DeviceTest.objects.order_by('-created_at'))

您可以通过以下方式获取状态

You can then get the status via the first related device test.

for obj in queryset:
    print obj.devicetest.all()[0].status

请注意，这是低效的，因为您已加载了所有相关项目，而不仅仅是最新的

Note this is inefficient, because you have loaded all related items, not just the most recent one.

Django数据库：按相关数据库中最新对象的字段排序

相关推荐