SQLAlchemy one to one relationship across multiple tables

390 views Asked by At

I have been trying to model a relationship between four tables, where entries from table A can be linked to entries in the other three tables. For example:

Table A (components)
id     brand     status
N1     br3       free
N2     br2       used
N3     br2       used
N4     br3       used
N5     br2       used

Table B (device1)
id     comp1     comp2
2      N2        N3

Table C (device2)
id     comp1     comp2
6      N4        N5

Table D (device3)
id     comp1     comp2

I can link the component from the device table like so:

class Device1(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    comp1 = db.Column(db.String(20), db.ForeignKey('components.id'), unique=True) 
    comp2 = db.Column(db.String(20), db.ForeignKey('components.id'), unique=True) 

But how do I implement a relationship for all three tables from table A? I was looking for something like component.deviceinstalled which would return the device that the component is attached to, regardless of which table it belongs to (B, C or D), for instance:

component1 -> <N2  br2  used>
component1.deviceinstalled -> <2  N2  N3> from table B

component2 -> <N4  br3  used>
component2.deviceinstalled -> <6  N4  N5> from table C
0

There are 0 answers