Multiple join paths and relationships that span across multiple tables

[Alexios Damigos]

Hello everyone,

I have been trying to create a model for my database, with no luck so far.
I am using SQLAlchemy 1.2.10 together with PyMySQL 0.9.2 to connect to a MariaDB database.

A description of the database model:

Table A (components)
id brand status
N1 br3   free
N2 br2   used
N3 br2   used
N4 br3   used
N5 br2   used
N6 br3   used
N7 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
1   N6    N7

So there are two foreign keys pointing to the same table (A) and column for all the other three tables, and every item on table A can only be assigned to a singe component column of a single table B, C or D.

Ideally what I would like to achieve is something like this:

component1 -> <N2 br2 used> 
component1.deviceInstalled -> <2 N2 N3> (from table B) 
component2 -> <N4 br3 used> 
component2.deviceInstalled -> <6 N4 N5> (from table C)

I tried following the guidelines in Multiple Join Paths, still got ambiguous foreign keys error.
As far as the relationship deviceInstalled, I have not managed to find somewhere how to implement that, since it has to span across three tables.


My working but incomplete code at the moment, with all the failed attempts removed.

Any help would be very much appreciated, as I have been trying for a couple of days with no luck!
Thank you

[Mike Bayer]

Looking at the code, I dont know what:

ForeignKey('')

means, I guess that means you don't know which table to refer towards?

if batmon/radmon/deported are B, C, and D, and "Sensor" is not part of
the problem, just make them FK's to"Device"? Here's that


from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.declarative import declared_attr

Model = declarative_base()


class Device(Model):
__tablename__ = 'device'

id = Column(Integer, primary_key=True)
type = Column(String(30), unique=False, nullable=False)
variant = Column(String(30), unique=False, nullable=True)
serialNo = Column(String(30), unique=True, nullable=False)

batmonConf = relationship('Batmon', backref='device',
uselist=False, lazy=True)
radmonConf = relationship('Radmon', backref='device',
uselist=False, lazy=True)
deportedConf = relationship('Deported', backref='device',
uselist=False, lazy=True)


class Batmon(Model):
__tablename__ = 'batmon'

id = Column(Integer, ForeignKey('device.id'), primary_key=True)
voltage = Column(Integer, unique=False, nullable=False)
fet1 = Column(String(20), ForeignKey(''), unique=True)
fet2 = Column(String(20), unique=True, nullable=True)
pin = Column(String(20), unique=True, nullable=True)
mem1 = Column(String(20), unique=True, nullable=True)
mem2 = Column(String(20), unique=True, nullable=True)


class Radmon(Model):
__tablename__ = 'radmon'

id = Column(Integer, ForeignKey('device.id'), primary_key=True)
fet1 = Column(String(20), unique=True, nullable=True)
fet2 = Column(String(20), unique=True, nullable=True)
pin = Column(String(20), unique=True, nullable=True)
mem1 = Column(String(20), unique=True, nullable=True)
mem2 = Column(String(20), unique=True, nullable=True)


class Deported(Model):
__tablename__ = 'deported'

id = Column(Integer, ForeignKey('device.id'), primary_key=True)
fet1 = Column(String(20), unique=True, nullable=True)
fet2 = Column(String(20), unique=True, nullable=True)
pin = Column(String(20), unique=True, nullable=True)


configure_mappers()

>
> Any help would be very much appreciated, as I have been trying for a couple
> of days with no luck!
> Thank you
>

> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+...@googlegroups.com.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Alexios Damigos]

Thank you for the reply.
The lack of foreign keys must be some bug of pastecode.xyz, because if you see it on the browser or on "raw", they are indeed set just like your suggestion.

The issue is that I cannot manage to add a second ForeignKey('sensor.id') to the tables batmon/radmon/deported, or figure out how to declare the relationship in the sensor table in order to point to the correct batmon/radmon or deported entry.

[Mike Bayer]

On Wed, Aug 22, 2018 at 11:34 AM, Alexios Damigos <damig...@gmail.com> wrote:
> Thank you for the reply.
> The lack of foreign keys must be some bug of pastecode.xyz, because if you
> see it on the browser or on "raw", they are indeed set just like your
> suggestion.
>
> The issue is that I cannot manage to add a second ForeignKey('sensor.id') to
> the tables batmon/radmon/deported,

that does not create any issue by itself, relationship() knows to only
look at the tables that are involved in the linkage. Feel free to
illustrate a code example that shows a failure and I can show you why.

or figure out how to declare the
> relationship in the sensor table in order to point to the correct
> batmon/radmon or deported entry.

Can you provide a pseudocode example illustrating what this
relationship looks like? you want a single Sensor.thing that returns
any of batmon/radmon/deported? can you share the actual structure of
the tables or is that part of what you are trying to decide upon ?

in the simplest terms this looks like you could just have
Radmon/Batmon/Deported be joined-table subclasses of Device, then link
Sensor->Device, and you're done. but im not sure what these concepts
mean. Also check out generic foreign key examples:
http://docs.sqlalchemy.org/en/latest/orm/examples.html#module-examples.generic_associations

[Alexios Damigos]

On Wednesday, August 22, 2018 at 6:13:36 PM UTC+2, Mike Bayer wrote:

that does not create any issue by itself, relationship() knows to only
look at the tables that are involved in the linkage.   Feel free to
illustrate a code example that shows a failure and I can show you why.

You were right, adding a second foreign key sensor.id to the Batmon table indeed works. I must have done something wrong previously when I tried it, thanks.
 

Can you provide a pseudocode example illustrating what this
relationship looks like?   you want a single Sensor.thing that returns
any of batmon/radmon/deported?   can you share the actual structure of
the tables or is that part of what you are trying to decide upon ?

I have included the structure of the tables and pseudocode for the relationship on my first email, and the structure of the tables is set to what I describe.
 

in the simplest terms this looks like you could just have
Radmon/Batmon/Deported be joined-table subclasses of Device, then link
Sensor->Device, and you're done.   but im not sure what these concepts
mean.    Also check out generic foreign key examples:
http://docs.sqlalchemy.org/en/latest/orm/examples.html#module-examples.generic_associations

How can I achieve that when Radmon/Batmon/Deported are separate tables?
It would be as if the sensor.thing (component1.deviceInstalled in my original email) is looking for any instance of sensor.id on batmon.fet1 and batmon.fet2 and radmon.fet etc

Maybe if I created a union table with all the id's, fet1's and fet2's of all three tables and looked there?
For example if I query this:

SELECT id,fet1 FROM batmon
UNION
SELECT id,fet2 FROM batmon
UNION
SELECT id,fet1 FROM radmon
UNION
SELECT id,fet2 FROM radmon
UNION
SELECT id,fet1 FROM deported
UNION
SELECT id,fet2 FROM deported;

I get a table with all the fets (either 1 or 2) installed and the id of the device that they are installed in.
If I somehow create a relationship on sensor that looks in that table for the id and grabs the device with that id, I would have achieved what I'm after.

Thank you,
Alex