On Sep 29, 11:53 am, "Michael Bayer" <[email protected]> wrote: > Wyatt Lee Baldwin wrote: > > > 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) > > ) > > OK I hope you can see that the example you originally sent me didn't > explain that "route_number" and "route_begin_date" are columns, and not > int/date objects.
Yeah, sorry about that. /appropriately chagrined > 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. --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
