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. 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. --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
