Yes, I had read the documentation. I am just new to SQL and databases in
general. I think there may be even a cleaner way to do this since I have
relationships() built into the models, but since the tables have multiple
foreign keys I think I had to specify the "on clause" anyhow. Thanks for
the help!
For the record, the final ORM level calls:
bus1_alias = aliased(sam.Bus)
bus2_alias = aliased(sam.Bus)
branch_db = self.db_hook.session.query(sam.Branch). \
join(bus1_alias, and_(bus1_alias.id==sam.Branch.id_from_bus,
bus1_alias.id_model==sam.Branch.id_model)). \
join(bus2_alias, and_(bus2_alias.id==sam.Branch.id_to_bus,
bus2_alias.id_model==sam.Branch.id_model)). \
filter(sam.Branch.ckt == ckt). \
filter(sam.Branch.id_model == case.id). \
filter(or_(and_(bus1_alias.number == bus1, bus2_alias.number == bus2),
and_(bus1_alias.number == bus2, bus2_alias.number == bus1)))
On Monday, April 3, 2017 at 4:38:14 PM UTC-5, Mike Bayer wrote:
>
>
> Have you read the tutorial at
> http://docs.sqlalchemy.org/en/rel_1_1/orm/tutorial.html#querying-with-joins
> and read all the examples at
>
> http://docs.sqlalchemy.org/en/rel_1_1/orm/query.html#sqlalchemy.orm.query.Query.join?
>
>
> There's nothing odd about the joins you're trying to build here.
> You've got the aliased(), you have the and_/or_, and all the conditions.
> Just need to move some of those filter() conditions into
> query.join(bus1_alias, <onclause>).
>
>
>
>
>
> On 04/03/2017 04:53 PM, Jason T. wrote:
> > Okay. I figured out how to use the ORM without joins, but I still can't
> > figure out how to use the joins. :(
> >
> > bus1_alias = aliased(sam.Bus)
> > bus2_alias = aliased(sam.Bus)
> >
> > branch_db = self.db_hook.session.query(sam.Branch). \
> > filter(sam.Branch.id_model == sam.Bus.id_model). \
> > filter(sam.Branch.ckt == ckt). \
> > filter(sam.Branch.id_model == case.id). \
> > filter(or_(and_(sam.Branch.id_from_bus == bus1_alias.id,
> sam.Branch.id_to_bus == bus2_alias.id),
> > and_(sam.Branch.id_from_bus == bus1_alias.id,
> sam.Branch.id_to_bus == bus2_alias.id))). \
> > filter(bus1_alias.number == bus1). \
> > filter(bus2_alias.number == bus2).first()
> >
> >
> >
> >
> > On Monday, April 3, 2017 at 1:29:49 PM UTC-5, Jason T. wrote:
> >
> > All,
> >
> > I am able to join two tables and get the desired result with the
> > below SQL syntax; however, I am having trouble translating this SQL
> > to the ORM join syntax. Any help will be appreciated.
> >
> > SELECT *
> > FROM raw.branch as b
> > JOIN raw.bus AS bus1
> > ON bus1.id = b.id_from_bus
> > AND bus1.id_model = b.id_model
> > JOIN raw.bus AS bus2
> > ON bus2.id = b.id_to_bus
> > AND bus2.id_model = b.id_model
> > WHERE b.id_model = 10
> > AND b.ckt = '1'
> > AND ((bus1.number = 510417 AND bus2.number = 547486) OR
> > (bus2.number = 510417 AND bus1.number = 547486))
> >
> > --
> > 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 [email protected] <javascript:>
> > <mailto:[email protected] <javascript:>>.
> > To post to this group, send email to [email protected]
> <javascript:>
> > <mailto:[email protected] <javascript:>>.
> > Visit this group at https://groups.google.com/group/sqlalchemy.
> > For more options, visit https://groups.google.com/d/optout.
>
--
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
