On Sep 29, 7:55 am, "Michael Bayer" <[email protected]> wrote:
> Wyatt Lee Baldwin wrote:
>
> > I have a relation defined like this in a declarative-style class:
>
> > route = relation(
> > RouteDef,
> > primaryjoin=(
> > (route_number == RouteDef.route_number) &
> > (route_begin_date >= RouteDef.route_begin_date)
> > ),
> > )
>
> > What I *really* want is for `route_number` and `route_begin_date` to
> > be substituted with literal values when I do `instance.route`.
> > Instead, I get a big nasty join that runs forever. Instead of the
> > generated SQL containing a literal date value, for example, the SQL
> > contains the column name, `route_begin_date`.
>
> I don't see this join condition related to the parent (or even what the
> parent is). So an eagerload will produce a big join, sure. The join
> expression needs to relate the child to the parent in some way. Using
> literals within that condition is not an issue.
In my view (which may be warped), a Trip "has one" Route (and many
Trips follow the same Route). Here's more context:
class Trip(Base):
__tablename__ = 'trip'
__table_args__ = dict(schema='trans')
__mapper_args__ = dict(
order_by='trip_begin_date,trip_begin_time,route_number,direction')
route_number = Column(Integer, ForeignKey(RouteDef.route_number),
primary_key=True)
direction = Column(Integer, primary_key=True)
service_key = Column(CHAR(3), primary_key=True)
trip_number = Column(Integer, primary_key=True)
trip_begin_date = Column(Date, primary_key=True)
trip_end_date = Column(Date)
route_begin_date = Column(Date)
pattern_id = Column(Integer)
trip_begin_time = Column(Numeric(8))
trip_end_time = Column(Numeric(8))
@property
def route(self):
"""This works, but I want to use a `relation`."""
try:
self._route
except AttributeError:
session = object_session(self)
q = session.query(RouteDef)
q = q.filter(self.route_number == RouteDef.route_number)
q = q.filter(self.route_begin_date >=
RouteDef.route_begin_date)
q = q.filter(self.route_begin_date <=
RouteDef.route_end_date)
self._route = q.one()
return self._route
Even though `route_number` is defined as a foreign key in the Trip
class, there's not actually a FK constraint in the DB; in practice,
though, `route_number` *is* a FK. `route_begin_date` perhaps should be
a FK, but for legacy reasons, it's not, and that's the reason for the
BETWEEN filtering.
This is how I tried to define the `relation` but which results in a
big join:
route = relation(RouteDef,
primaryjoin=(
(route_number == RouteDef.route_number) &
(route_begin_date >= RouteDef.route_begin_date) &
(route_begin_date <= RouteDef.route_end_date)
)
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
