Combine two unrelated tables/models with same primary key in Django
django composite primary key
django join query sets from multiple tables
django join multiple tables
django join tables without foreign key
django model inheritance
django foreign key
I have two unrelated tables with the same primary key.
ip mac 18.104.22.168 48-C0-09-1F-9B-54 22.214.171.124 4E-10-A3-BC-B8-9D 126.96.36.199 CD-00-60-08-56-2A 188.8.131.52 23-CE-D3-B1-39-A6 ip type owner 184.108.40.206 laptop John Doe 220.127.116.11 server XYZ Department 18.104.22.168 VM Mary Smith 22.214.171.124 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 126.96.36.199 48-C0-09-1F-9B-54 Unauthorized 188.8.131.52 23-CE-D3-B1-39-A6 Unauthorized 184.108.40.206 laptop John Doe Down 220.127.116.11 printer ZWV Department Down 18.104.22.168 4E-10-A3-BC-B8-9D server XYZ Department OK 22.214.171.124 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?
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 126.96.36.199 48-C0-09-1F-9B-54 Unauthorized 188.8.131.52 23-CE-D3-B1-39-A6 Unauthorized 184.108.40.206 4E-10-A3-BC-B8-9D server XYZ Department OK 220.127.116.11 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):
18.104.22.168 laptop John Doe Down 22.214.171.124 printer ZWV Department Down
I asked for help here, but it seems it can't be done with
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
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
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()
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
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
- You need to share more about the data flow if you want help with modelling. Are
macunique 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?
ipis primary key in both, so it's unique.
macdoesn't matter; it's just one of the fields. There are other fields... I want to associate the two tables by
- Question - both tables have multiple fields. For example, the table with macs has fqdn (dfds.company.com), open ports, ... Do I do multiple
coalescecalls, one for each field?
- I was able to tweak your code using multiple separate
subquerycalls, 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
- 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?
- No. This generates query
... WHERE tableB.ip IS NULL
IP's missing in first table.. when the
modelAgets 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