List of missing records for reverse OneOnOneField in django

Question

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?

The required `on_delete` parameter is missing from your relationship definition. If it is `SET_NULL`, then you cannot use the one-to-one field as a primary key. If it is `CASCADE`, then your `AllowedDevices` will be deleted when an `OperationalDevice` goes down, making this question moot. — Endre Both, Apr 27 '19 at 07:36
Django can be persuaded to use left joins with one-to-many relations, but apparently not with one-to-one. — Endre Both, May 01 '19 at 08:52
@EndreBoth - but it doesn't make sense to use **one-to-many** if they are both unique fields. — Granny Aching, May 01 '19 at 17:39
@EndreBoth - I thought you were suggesting me to try `one-to-many` — Granny Aching, May 01 '19 at 18:09

imposeren · Answer 1 · 2019-05-11T00:15:57.933

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.

List of missing records for reverse OneOnOneField in django

1 Answers1

Linked