Django中反向OneOnOneField的丢失记录列表 [英] List of missing records for reverse OneOnOneField in django

问题描述

我有两个具有相同主键的模型：

  class OperationalDevice（models.Model）：
 ip = models.GenericIPAddressField（primary_key = True）
 mac = models.CharField（）
 
 class AllowedDevice（models.Model）：
 ip = models.OneToOneField（OperationalDevice，primary_key =正确，on_delete = models.DO_NOTHING，db_constraint = False，db_column ='ip'）
 type = models.CharField（）
 owner = models.CharField（）
  

我想显示所有已关闭的AllowedDevices列表-类似于：

 从AllowedDevice 
中选择AllowedDevice.ip左外连接OperationalDevice ON 
 AllowedDevice.ip = OperationalDevice.ip 
 WHERE OperationalDevice.ip为NULL 
  

我尝试使用 AllowedDevice.objects.filter（ip __...），但会创建内部联接。我还尝试了 objects.exclude 和 objects.annotate ，它们还使用创建了一个查询。内部联接

也许我不应该使用OneToOneField？

关系走另一条路不是解决方案，因为我需要找到两种例外情况-位于一个表中但不在另一个表中的设备。

这与我之前的问题：

我有两个具有相同主键的表。

  ip mac 
 11.11.11.11 48-C0-09-1F-9B -54 
 33.33.33.33 4E-10-A3-BC-B8-9D 
 44.44.44.44 CD-00-60-08-56-2A 
 55.55.55.55 23-CE-D3 -B1-39-A6 
 
 ip类型所有者
 22.22.22.22笔记本电脑John Doe 
 33.33.33.33服务器XYZ部门
 44.44.44.44 VM Mary Smith 
 66.66.66.66打印机ZWV部门
  

第一个表每分钟自动刷新一次。我无法
更改数据库结构或填充数据库结构的脚本。

两个表都具有 ip 作为主键。

在一个视图中，我想显示一个这样的表：

  ip mac type owner警报
 11.11.11.11 48-C0-09-1F-9B-54未经授权
 55.55.55.55 23-CE-D3-B1-39-A6未经授权的
 22.22.22.22笔记本电脑John Doe Down 
 66.66.66.66打印机ZWV Department Down 
 33.33.33.33 4E-10-A3-BC-B8-9D服务器XYZ Department OK 
 44.44 .44.44 CD-00-60-08-56-2A VM Mary Smith OK 
  

如何建模这个？我应该将两个主键中的一个作为
外键吗？

一旦代码运行，将会有很多数据，所以我希望
确保其速度足够快。

最快的数据检索方法是什么？

解决方案

要使用左外部联接，应使用 .values（'one_to_one_field__sub_filed'）或 .annotate（sub_field = F（'one_to_one_field__sub_filed'））：

 从django.db.models导入F 
 AllowedDevice.objects.all（）。annotate（mac = F（'ip__mac'））
  

但是我认为您确实需要一个只能使用 QS.union（）。有关详细信息，请参见原始问题的答案的最后一部分（使用OneToOneField获得更好的SQL）。

I have two models with the same primary key:

class OperationalDevice(models.Model):
    ip = models.GenericIPAddressField(primary_key=True)
    mac = models.CharField()

class AllowedDevice(models.Model):
    ip = models.OneToOneField(OperationalDevice, primary_key=True, on_delete=models.DO_NOTHING, db_constraint=False, db_column='ip')
    type = models.CharField()
    owner = models.CharField()

I would like to display the list of all AllowedDevices that are down - kind of like:

SELECT AllowedDevice.ip from AllowedDevice
LEFT OUTER JOIN OperationalDevice ON
     AllowedDevice.ip = OperationalDevice.ip
WHERE OperationalDevice.ip is NULL

I tried using AllowedDevice.objects.filter(ip__...), but it creates inner join. I also tried objects.exclude and objects.annotate, and they also create a query with inner join

Maybe I should't be using OneToOneField?

Making the relationship go the other way is not a solution, because I need to find both kinds of exceptions - devices that are in one table but not the other.

This is related to my previous question:

I have two tables with the same primary key.

ip            mac
11.11.11.11   48-C0-09-1F-9B-54
33.33.33.33   4E-10-A3-BC-B8-9D
44.44.44.44   CD-00-60-08-56-2A
55.55.55.55   23-CE-D3-B1-39-A6

ip            type     owner
22.22.22.22   laptop   John Doe
33.33.33.33   server   XYZ Department
44.44.44.44   VM       Mary Smith
66.66.66.66   printer  ZWV Department

The first table is automatically refreshed every minute. I can't change the database structure or the script that populates it.

Both tables have ip as PRIMARY KEY.

In a view, I would like to display a table like this:

ip           mac               type    owner          Alert
11.11.11.11  48-C0-09-1F-9B-54                        Unauthorized
55.55.55.55  23-CE-D3-B1-39-A6                        Unauthorized
22.22.22.22                    laptop  John Doe       Down
66.66.66.66                    printer ZWV Department Down
33.33.33.33  4E-10-A3-BC-B8-9D server  XYZ Department OK
44.44.44.44  CD-00-60-08-56-2A VM      Mary Smith     OK

How can I model this? Should I make one of the two primary keys a foreign key into the other one?

Once the code is in operation, there will be lots of data, so I want to make sure it's fast enough.

What is the fastest way to retrieve the data?

解决方案

To use left outer join you should use .values('one_to_one_field__sub_filed') or .annotate(sub_field=F('one_to_one_field__sub_filed')):

from django.db.models import F
AllowedDevice.objects.all().annotate(mac=F('ip__mac'))

But I think that you really need a "FULL JOIN" that can only be simulated using QS.union(). See the last part (Using OneToOneField for better SQL) of answer to your original question for details.

这篇关于Django中反向OneOnOneField的丢失记录列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！