On May 29, 2014, at 2:09 PM, Daniel Grace <thisgenericn...@gmail.com> wrote:
> I have a table structure something like this:
>
> * Two tables Foo and Bar
> * FooBar, an many-to-many association table between Foo and Bar
> * A "County" lookup table referenced by both Foo and Bar
> * A "Region" lookup table referenced by County
>
> I'm frequently running queries that involve both Foo and bar, and
> County/Region tables joined to both of them. Since I need to reference the
> same table twice, and the join condition itself is ambiguous, I'm having to
> do something like this:
>
> FooCounty = orm.aliased(County, "FooCounty")
> FooRegion = orm.aliased(Region, "BarRegion")
> BarCounty = orm.aliased(County, "BarCounty")
> BarRegion = orm.aliased(Region, "BarRegion")
>
> session.query(Foo).join(FooBar).join(Bar).join(FooCounty, Foo.county_id ==
> FooCounty.id).join(BarCounty, Bar.county_id == BarCounty.id) # etc.
>
> Is there a way I can set something up so that FooCounty and BarCounty are
> essentially permanent aliases of County that will each follow the correct
> relationship, and likewise for FooRegion/BarRegion? In short, I'd like to be
> able to let SQLAlchemy automatically handle the Join condition and such, so I
> can get the above snippet down to a much simpler...
>
> session.query(Foo).join(FooBar).join(Bar).join(FooCounty).join(BarCounty) #
> etc.
usually this is what relationship is used for, and you can also use
aliased=True to reduce typing, like this:
session.query(Foo).join(Foo.bars).join(Foo.county,
aliased=True).join(Bar.county, aliased=True)
aliased=True has the limitation that you have to call any filter() on County
right after the join() before any others.
Otherwise if you just had FooCounty and BarCounty lying around you'd just
specify them:
session.query(Foo).join(Foo.bars).join(FooCounty,
Foo.county).join(BarCounty, Bar.county)
There's not a way to avoid ambiguity when doing the query(A).join(B) style of
join, you have to have some kind of additional argument to disambiguate.
you can reduce typing doing either this:
foo_county_join = (FooCounty, Foo.county)
bar_county_join = (BarCounty, Bar.county)
s.query(Foo).join(Foo.bars).join(*foo_county_join).join(*bar_county_join)
or more slick-ly, this (more recent SQLA versions probably best here):
foo_county_join = Foo.county.of_type(FooCounty)
bar_county_join = Bar.county.of_type(BarCounty)
s.query(Foo).join(Foo.bars).join(foo_country_join).join(bar_county_join)
but in all cases, in order to get ORM things you need to be on board with
relationship().
--
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.
