django - 如何过滤外键项？(How to filter foreign key item?)

Question

I am not sure if I put correct title for what I need but here is the thing.

(我不确定我是否需要正确的标题，但这就是问题。)

I have two model.

(我有两种模式。)

class Device(models.Model):
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    device_name = models.CharField(max_length=200, null=True, blank=True )
    created_at = models.DateTimeField(auto_now_add=True)
    def __str__(self):
        return str(self.device_name)

class StatusActivity(models.Model):
    OFFLINE = 1
    ONLINE = 2
    STATUS = (
        (OFFLINE, ('Offline')),
        (ONLINE, ('Online')),
    )
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    device_id = models.ForeignKey(Device, related_name='status_activity', on_delete=models.CASCADE)
    status = models.PositiveSmallIntegerField(choices=STATUS)
    modified_at = models.DateTimeField(auto_now_add=True)

    def __str__(self):
        return str(self.device_id) 

I can get a list of StatusActivity and Device seperately.

(我可以分别获取StatusActivity和设备的列表。)

But what I need is latest status for every device.

(但是我需要的是每台设备的最新状态。)

Devices:

(设备：)

Id                                  Created_At                  Device_name
5b9bbd0f02f8428ca69a582a491f0751    2019-11-30 15:38:37.076440  Temperature Device
17c1ac3ed05844cd879c218effaba15e    2019-11-30 15:39:14.716443  Humidity Device
a0ca21a555b246b99be965db4493276f    2019-11-30 15:39:27.822114  Motion Device

StatusActivities:

(状态活动：)

Id                               Status Modified_at                 Device_id_id
350fed049ba04541a7b6d81932d5824b    2   2019-11-30 15:39:39.849491  5b9bbd0f02f8428ca69a582a491f0751
e4f317b56bbb46eb9309ba77da692af0    1   2019-11-30 15:39:47.912013  5b9bbd0f02f8428ca69a582a491f0751
e0a021dd53734d3b9a15e53ad93bbb28    2   2019-11-30 15:41:04.891823  5b9bbd0f02f8428ca69a582a491f0751
1c12c64708234df6992883b377d3102f    2   2019-11-30 17:12:58.336177  17c1ac3ed05844cd879c218effaba15e
8d280d522baa449588b9a9ab67ed7bae    1   2019-11-30 17:13:05.987487  a0ca21a555b246b99be965db4493276f

Example result should be this:

(示例结果应为：)

ID                                  device_name         Status      Modified_at
5b9bbd0f02f8428ca69a582a491f0751    Temperature Device      2       2019-11-30 15:39:39.849491
17c1ac3ed05844cd879c218effaba15e    Humidity Device         2       2019-11-30 17:12:58.336177  
a0ca21a555b246b99be965db4493276f    Motion Device           1       2019-11-30 17:13:05.987487

  ask by erondem translate from so

Answer 1

If you're using Postgres, you can use DISTINCT ON ( distinct ) on device_id :

(如果您使用的是Postgres，则可以在device_id上使用DISTINCT ON （ distinct ）：)

from django.db.models import F

StatusActivity.objects.order_by('device_id', '-modified_at').distinct('device_id').annotate(
    ID=F('device_id__id'),
    device_name=F('device_id__device_name'),
).values('ID', 'device_name', 'status', 'modified_at')

If you don't have Postgres, you can use subqueries for annotating the data from StatusActivity :

(如果您没有Postgres，则可以使用子查询来注释StatusActivity的数据：)

from django.db.models import Subquery, OuterRef

Device.objects.annotate(
    status=Subquery(
        StatusActivity.objects.filter(
            device_id=OuterRef('pk')
        ).order_by('-modified_at').values('status')[:1]
    ),
    modified_at=Subquery(
        StatusActivity.objects.filter(
            device_id=OuterRef('pk')
        ).order_by('-modified_at').values('modified_at')[:1]
    ),
).values('id', 'device_name', 'status', 'modified_at')

Caveats:

(注意事项：)

• Subquery requires only one row and column so that the value can be used in the annotation.

  (Subquery仅需要一行和一列，以便可以在注释中使用该值。)

  This means if we want to have multiple columns from a subquery, we need to use two annotations so there would be two joins like above so there would be a performance hit.

  (这意味着，如果我们想从一个子查询中获得多个列，则需要使用两个注释，以便像上面那样有两个联接，这样会影响性能。)

• If you want, you can limit your annotations to only one field eg status but if you want both of the fields I would recommend the first approach (assuming you're using a supported DB (Postgres)).

  (如果需要，可以将注释限制为仅一个字段，例如status但是如果要两个字段，我建议采用第一种方法（假设您使用的是受支持的数据库（Postgres））。)

---

Also note that Django automatically appends _id to the FK fields, so it's preferable to name them without any _id suffix.

(还要注意，Django会自动将_id附加到FK字段，因此最好将它们命名为不带任何_id后缀。)

In your case, use device instead of device_id .

(在您的情况下，请使用device而不是device_id 。)