Am 08.07.2014 18:51, schrieb Jonathan Rogers:
> 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.
>
>
Hi Jonathon,
thanks a lot for the hint. Looks good to me.
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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
