Hi there,
I am wondering how the following would be translated to sqlalchemy.
I have three tables:
"ClientMachine"
"MachineToken"
"Token"
The table MachineToken acts as n:m mapping between "ClientMachine" and
"Token".
Each machine can have several tokens assigned.
Looks like this:
machinetoken_table = sa.Table('MachineToken', meta.metadata,
sa.Column('id',
sa.types.Integer(),
sa.Sequence('machinetoken_seq_id', optional=True),
primary_key=True, nullable=False),
sa.Column("token_id",
sa.types.Integer(), ForeignKey('Token.privacyIDEATokenId')),
sa.Column("machine_id", sa.types.Integer(), ForeignKey('ClientMachine.id')),
sa.Column("application", sa.types.Unicode(64)),
UniqueConstraint('token_id', 'machine_id', 'application', name='uix_1'),
implicit_returning=implicit_returning,
)
Now I'd like to get a list of all machines and if the machine has token-
and application-information also this information.
I figured out a left outer join:
select cl.cm_name
, mt.application
, (select privacyIDEATokenSerialnumber from Token where
privacyIDEATokenId = mt.token_id) as Serial
FROM ClientMachine cl
LEFT JOIN MachineToken mt
ON cl.id = mt.machine_id
This will give me one machine entry per assigned "Token" or
"MachineToken.application". This is my intended result. fine.
I need to translate the MachineToken.token_id to a human readable
Serialnumber, this is why I have the line
(select privacyIDEATokenSerialnumber from Token where
privacyIDEATokenId = mt.token_id)
To my understandung I would do in SQLA something like this:
Session.query(ClientMachine).outerjoin(MachineToken).filter(ClienteMachine.id
== MachineToken.machine_id)
But how would I add the Serialnumber from the table "Token"?
Thanks a lot and kind regards
Cornelius
--
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
