Combine two unrelated tables/models with same primary key in Django

django join two models with foreign key
django composite primary key
django join query sets from multiple tables
django join multiple tables
django join tables without foreign key
django subquery
django model inheritance
django foreign key

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.

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?


Update:

I tried using OneToOneField for the second table.

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

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

I asked for help here, but it seems it can't be done with OneToOneField

Django query - combine two different cases in filter, How to do union of two querysets from same or different models , The UNION Join two models in Django filter, Use values or values_list to join the tables and get Joining unrelated models and filter the queryset, Joining unrelated models primary key, and you can still refer to the table ModelA using the field modelA. If True, this field is the primary key for the model. If you don’t specify primary_key=True for any fields in your model, Django will automatically add an IntegerField to hold the primary key, so you don’t need to set primary_key=True on any of your fields unless you want to override the default primary-key behavior. For more, see Automatic

Since ip is primary key in both an the first table is getting updated frequently, I suggest updating the second table and converting the ip in the second table to have ip of the first table as a OneToOneField.

This is how your models should look like:

class ModelA(models.Model):
    ip = models.GenericIPAddressField(unique=True)
    mac = models.CharField(max_length=17, null=True, blank=True)

class ModelB(models.Model):
    ip = models.OneToOneField(ModelA)
    type = models.CharField()
    owner = models.CharField()

docs

You can also have the one to one relation using a separate column:

class ModelB(models.Model):
    ip = models.GenericIPAddressField(unique=True) 
    type = models.CharField()
    owner = models.CharField()
    modelA = models.OneToOneField(ModelA)

So now you can have the ip address as the primary key, and you can still refer to the table ModelA using the field modelA.

10. How to perform join operations in django ORM?, A SQL Join statement is used to combine data or rows from two or more tables based on a common field between them. Join can be carried� Django's contenttypes framework is really a good option for this use case. From the docs: At the heart of the contenttypes application is the ContentType model, which lives at django.contrib.contenttypes.models.ContentType. Instances of ContentType represent and store information about the models installed in your project, and new instances of

Once you have a value from one of both tables just do a query into the other one, looking for id. Since these two tables are separated you must do an extra query. You don't need to create an explicit relation, since you are looking into its "id/ip". So once you have a first value, named 'first_object', just look for its relative into the other table.

other_columns = ModelB.objects.get(id=first_object.id)

Then if you want just 'add' the desired columns to the other model and sent a single object to whatever you want:

first_object.attr1 = other_columns.attr1
...

Join two models without a foreign key : django, We had a need for joining to models not related by foreign key at my work, and made the solution open source. I'd love to get feedback on this. Thanks. 8. How to specify the table name for a model? 9. How to specify the column name for model field? 10. What is the difference between null=True and blank=True? 11. How to use a UUID instead of ID as primary key? 12. How to use slug field with django for more readability? 13. How to add multiple databases to the django application ? 1.

Relationships and Combining Data in SQL, Get the low-down on how to JOIN tables and create UNIONs. Welcome to SQL 3: Building Relations and Combining Data Sets a foreign key in our orders table to a primary key in our Customers table: a merge, you should ask yourself why you're merging two unrelated sets of data in the first place). How to join tables with VLOOKUP. If you are to merge two tables based on one column, VLOOKUP is the right function to use. Supposing you have two tables in two different sheets: the main table contains the seller names and products, and the lookup table contains the names and amounts. You want to combine these two tables by matching data in the

Combine Two Querysets in Django (With Different Models), Read my note under step 5. Interests. Posted in these interests: django. PRIMARY. 6 guides. Subscribe. This example is very similar to the example for multi-table inheritance in the previous section, and if you saved and migrated these models, you would get the same result as Figure 9-1—Django would create the events_myclubuser and events_subscriber tables in your database, but, because UserBase is an abstract model, it won’t be added to the

Chapter 5: Models — Django Book 0.1 documentation, Many complex Web sites provide some combination of the two. With the database layer, we're applying that same philosophy to data access logic. Rather, Django creates an additional table – a many-to-many “join table” – that handles the mapping Each Django model is required to have a single-column primary key. If the object’s primary key attribute is not set or if the UPDATE didn’t update anything (e.g. if primary key is set to a value that doesn’t exist in the database), Django executes an INSERT. If the object’s primary key attribute defines a default then Django executes an UPDATE if it is an existing model instance and primary key is set

Comments
  • You need to share more about the data flow if you want help with modelling. Are ip and mac unique in the first table? On what basis do you associate IPs to devices in the second table? (If anything, I would expect MACs to be associated to devices.) Where do the user data come from?
  • ip is primary key in both, so it's unique. mac doesn't matter; it's just one of the fields. There are other fields... I want to associate the two tables by ip.
  • Question - both tables have multiple fields. For example, the table with macs has fqdn (dfds.company.com), open ports, ... Do I do multiple coalesce calls, one for each field?
  • I was able to tweak your code using multiple separate coalesce/subquery calls, one for each field, and it's kind-of working. I'm still tweaking it. It feels ugly to have 4 separate subqueries.
  • Yes, when it comes to multiple fields from same query, then django ORM is a bit ugly: currently there is no good built-in way for this
  • It took me forever to figure out how to use a default value in Coalescence instead of None...
  • Ooop. sorry about that. Will add a comment in the answer
  • How can I use these models to get all the records of ModelB that have no matching ModelA record?
  • ModelB.objects.filter(ip__isnull=True)
  • No. This generates query ... WHERE tableB.ip IS NULL
  • IP's missing in first table.. when the modelA gets updated every minute, do you delete entries from the table?
  • Since both ip are primary key, you can create the relation using an additional column