On 07/08/2014 12:39 PM, Cornelius Kölbel wrote:
> 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
I'd just use another outer join rather than a subquery:
select cl.cm_name
, mt.application
, privacyIDEATokenSerialnumber
FROM ClientMachine cl
LEFT JOIN MachineToken mt
ON cl.id = mt.machine_id
LEFT JOIN Token
ON privacyIDEATokenId = mt.token_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"?
Since you've already defined the foreign keys in SQLA, you shouldn't
need to do so again in the query. You can get any number of values
from a query. Try something like this:
Session.query(ClientMachine, Token.privacyIDEATokenSerialnumber) \
.outerjoin(MachineToken).outerjoin(Token)
That should give pairs of a ClientMachine instance and a
privacyIDEATokenSerialnumber.
--
Jonathan Rogers
--
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.
