On Sep 29, 3:12 pm, "Michael Bayer" <mike...@zzzcomputing.com> wrote:
> Wyatt Lee Baldwin wrote:
>
> > On Sep 29, 2:00 pm, "Michael Bayer" <mike...@zzzcomputing.com> wrote:
> >> Wyatt Lee Baldwin wrote:
>
> >> >> Anyway the primaryjoin here looks fine and does
> >> >> represent the same thing you're getting in your route() @property.
> >> >> It's
> >> >> a simple many-to-one with an additional criterion. Nothing needs to
> >> be
> >> >> configured in the database as far as foreign keys, configuring it as
> >> >> such
> >> >> within the table metadata is all SQLA cares about.
>
> >> > I'm still not clear if/how I can replace the property with the
> >> > relation. When I use the relation shown above, I get the big join.
> >> > Sorry if I'm being daft.
>
> >> what big join ?
>
> > When I define the route relation as above (2nd version) and access the
> > route attribute of a Trip, I eventually get a MemoryError. I assume
> > this is because of a join. When I use the property version, accessing
> > route is speedy. Here is the SQL that's being generated from the
> > relation (inside a `paster shell` session):
>
> > In [14]: trip = sess.query(Trip).first()
>
> > In [15]: trip.route_number
> > Out[15]: 925
>
> > In [16]: trip.route_begin_date
> > Out[16]: datetime.date(2007, 5, 13)
>
> > In [17]: trip.route
> > 14:40:00,356 INFO [trimet.db.TriMetEngine.0x...2e8c] SELECT
> > trans.route_def.route_number AS trans_route_def_route_number,
> > trans.route_def.route_begin_date AS trans_route_def_route_be_1,
> > trans.route_def.route_end_date AS trans_route_def_route_end_date,
> > trans.route_def.public_route_description AS
> > trans_route_def_public_r_2, trans.route_def.route_type AS
> > trans_route_def_route_type, trans.route_def.route_sub_type AS
> > trans_route_def_route_sub_type, trans.route_def.route_usage AS
> > trans_route_def_route_usage, trans.route_def.route_sort_order AS
> > trans_route_def_route_so_3
> > FROM trans.route_def, trans.trip
> > WHERE :param_1 = trans.route_def.route_number AND
> > trans.trip.route_begin_date >= trans.route_def.route_begin_date AND
> > trans.trip.route_begin_date <= trans.route_def.route_end_date ORDER BY
> > route_sort_order
> > 14:40:00,356 INFO [trimet.db.TriMetEngine.0x...2e8c] {'param_1': 925}
> > 14:40:01,379 DEBUG [trimet.db.TriMetEngine.0x...2e8c] Col
> > ('TRANS_ROUTE_DEF_ROUTE_NUMBER', 'TRANS_ROUTE_DEF_ROUTE_BE_1',
> > 'TRANS_ROUTE_DEF_ROUTE_END_DATE', 'TRANS_ROUTE_DEF_PUBLIC_R_2',
> > 'TRANS_ROUTE_DEF_ROUTE_TYPE', 'TRANS_ROUTE_DEF_ROUTE_SUB_TYPE',
> > 'TRANS_ROUTE_DEF_ROUTE_USAGE', 'TRANS_ROUTE_DEF_ROUTE_SO_3')
>
> > After a delay, with `engine.echo = True`, this starts spewing out
> > thousands of trans.route_def records and eventually dies. Why is a
> > literal value (:param_1 = 925) substituted for route_number while the
> > column name (trans.trip.route_begin_date) is substituted for
> > route_begin_date?
>
> so, first you debug the join condition using
>
> import logging
> logging.basicConfig()
> logging.getLogger('sqlalchemy.orm').setLevel(logging.INFO)
>
> and then look for
>
> INFO:sqlalchemy.orm.strategies.LazyLoader:Trip.route lazy loading clause
> :param_1 = routedef.route_number AND trip.route_begin_date >=
> routedef.route_begin_date AND trip.route_begin_date <=
> routedef.route_end_date
>
> which is wrong. then you ply it with arguments to convince it that the
> comparisons are part of what it should be parameterizing.
>
> the most direct way is:
>
> remote_side=[RouteDef.route_number, RouteDef.route_begin_date,
> RouteDef.route_end_date]
>
> which will force it to figure out (local, remote) based on those columns
> against the primaryjoin, to determine the "local remote pairs". you
> should probably do it this way.
This works perfectly. I passed over the `remote_side` arg multiple
times because of "used for self-referential relationships..."
> Another way is:
>
> viewonly=True,
> foreign_keys=[route_number, route_begin_date]
I could have sworn I tried this at some point... but apparently not
this *exact* combination, because I tried it just now, and it works,
too.
> this one has it look for columns have dependent values based on what is
> considered a foreign key. the "viewonly" flag means we don't have to
> worry about populating those columns during flush, so it matches <= and >=
> operators. then the "local remote pairs" is taken from that. not as
> direct. But this relation() should probably have viewonly=True in any
> case.
I went with this method, since I agree with you about viewonly. Thanks
for the quick answers.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
