问题描述
我有两个具有相同主键的不相关表。
I have two unrelated 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.
两个表都具有 ip 作为主键
在一个视图中，我想显示一个这样的表：
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?
更新：
我尝试使用 OneToOneField
这有助于我获取两个表中的记录以及未授权设备的记录（第二个表中缺少IP）：
This helps me get records that are in both tables, and the records for unauthorized devices (IPs missing in second table):
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
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
但是我无法关闭掉的设备（第一张表中缺少IP ）：
but I can't get the devices that are down (IP's missing in first table):
22.22.22.22 laptop John Doe Down
66.66.66.66 printer ZWV Department Down
我在寻求帮助，但是似乎无法使用 OneToOneField
I asked for help here, but it seems it can't be done with OneToOneField
推荐答案
您可以使用：
- 创建2个模型，它们之间没有任何关系。不要忘记使用
类元：managed = False - 从第一个模型中选择，用子查询注释，并用第二个联合：
from django.db import models
from django.db.models import F, OuterRef, Subquery, Value
from django.db.models.functions import Coalesce
# OperationalDevice fields: ip, mac
# AllowedDevice fields: ip, type, owner
USE_EMPTY_STR_AS_DEFAULT = True
null_char_field = models.CharField(null=True)
if USE_EMPTY_STR_AS_DEFAULT:
default_value = ''
else:
default_value = None
# By default Expressions treat strings as "field_name" so if you want to use
# empty string as a second argument for Coalesce, then you should wrap it in
# `Value()`.
# `None` can be used there without wrapping in `Value()`, but in
# `.annotate(type=NoneValue)` it still should be wrapped, so it's easier to
# just "always wrap".
default_value = Value(default_value, output_field=null_char_field)
operational_devices_subquery = OperationalDevice.objects.filter(ip=OuterRef('ip'))
qs1 = (
AllowedDevice.objects
.all()
.annotate(
mac=Coalesce(
Subquery(operational_devices_subquery.values('mac')[:1]),
default_value,
output_field=null_char_field,
),
)
)
qs2 = (
OperationalDevice.objects
.exclude(
ip__in=qs1.values('ip'),
)
.annotate(
type=default_value,
owner=default_value,
)
)
final_qs = qs1.union(qs2)
用于多个字段的通用方法
更复杂但通用的方法可以使用。对于第二模型具有多于1个额外字段的情况（不仅 ip，mac ），使用起来会更容易。示例代码（未经测试，但能给人以一般印象）：
Generic approach for multiple fields
A more complex but "universal" approach may use Model._meta.get_fields(). It will be easier to use for cases where "second" model have more that 1 extra field (not only ip,mac). Example code (not tested, but gives general impression):
# One more import:
from django.db.models.fields import NOT_PROVIDED
common_field_name = 'ip'
# OperationalDevice fields: ip, mac, some_more_fields ...
# AllowedDevice fields: ip, type, owner
operational_device_fields = OperationalDevice._meta.get_fields()
operational_device_fields_names = {_f.name for _f in operational_device_fields} # or set((_f.name for ...))
allowed_device_fields = AllowedDevice._meta.get_fields()
allowed_device_fields_names = {_f.name for _f in allowed_device_fields} # or set((_f.name for ...))
operational_devices_subquery = OperationalDevice.objects.filter(ip=OuterRef(common_field_name))
left_joined_qs = ( # "Kind-of". Assuming AllowedDevice to be "left" and OperationalDevice to be "right"
AllowedDevice.objects
.all()
.annotate(
**{
_f.name: Coalesce(
Subquery(operational_devices_subquery.values(_f.name)[1]),
Value(_f.get_default()), # Use defaults from model definition
output_field=_f,
)
for _f in operational_device_fields
if _f.name not in allowed_device_fields_names
# NOTE: if fields other than `ip` "overlap", then you might consider
# changing logic here. Current implementation keeps fields from the
# AllowedDevice
}
# Unpacked dict is partially equivalent to this:
# mac=Coalesce(
# Subquery(operational_devices_subquery.values('mac')[:1]),
# default_for_mac_eg_fallback_text_value,
# output_field=null_char_field,
# ),
# other_field = Coalesce(...),
# ...
)
)
lonely_right_rows_qs = (
OperationalDevice.objects
.exclude(
ip__in=AllowedDevice.objects.all().values(common_field_name),
)
.annotate(
**{
_f.name: Value(_f.get_default(), output_field=_f), # Use defaults from model definition
for _f in allowed_device_fields
if _f.name not in operational_device_fields_names
# NOTE: See previous NOTE
}
)
)
final_qs = left_joined_qs.union(lonely_right_rows_qs)
将OneToOneField用于更好的 SQL
理论上，您可以使用 device_info = models.OneToOneField（OperationalDevice，db_column ='ip'，primary_key = True，related_name ='status_info'）：位于 AllowedDevice 中。在这种情况下，可以不使用子查询来定义您的第一个QS：
Using OneToOneField for "better" SQL
Theoretically you can use device_info = models.OneToOneField(OperationalDevice, db_column='ip', primary_key=True, related_name='status_info'): in AllowedDevice. In this case your first QS may be defined without use of Subquery:
from django.db.models import F
# Now 'ip' is not in field names ('device_info' is there), so add it:
allowed_device_fields_names.add(common_field_name)
# NOTE: I think this approach will result in a more compact SQL query without
# multiple `(SELECT "some_field" FROM device_info_table ... ) as "some-field"`.
# This also might result in better query performance.
honest_join_qs = (
AllowedDevice.objects
.all()
.annotate(
**{
_f.name: F(f'device_info__{_f.name}')
for _f in operational_device_fields
if _f.name not in allowed_device_fields_names
}
)
)
final_qs = honest_join_qs.union(lonely_right_rows_qs)
# or:
# final_qs = honest_join_qs.union(
# OperationalDevice.objects.filter(status_info__isnull=True).annotate(**missing_fields_annotation)
# )
# I'm not sure which approach is better performance-wise...
# Commented one will use something like:
# `SELECT ... FROM "device_info_table" LEFT OUTER JOIN "status_info_table" ON ("device_info_table"."ip" = "status_info_table"."ip") WHERE "status_info_table"."ip" IS NULL
#
# So it might be a little better than first with `union(QS.exclude(ip__in=honest_join_qs.values('ip'))`.
# Because later uses SQL like this:
# `SELECT ... FROM "device_info_table" WHERE NOT ip IN (SELECT ip FROM "status_info_table")`
#
# But it's better to measure timings of both approaches to be sure.
# @GrannyAching, can you compare them and tell in the comments which one is better ?
P.S。要自动执行模型定义，您可以使用
P.S. To automate models definition you can use manage.py inspectdb
PPS也许与自定义与使用 union 相比， OneToOneField（...，parent_link = True）对您可能更有用。
P.P.S. Maybe multi-table inheritance with custom OneToOneField(..., parent_link=True) may be more helpful for you than using union.
这篇关于在Django中将两个不相关的表/模型与相同的主键合并的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！