Awesome Mike thanks, sorry for the delay writing this, I didn't get an update for some reason! Much appreciated.
On May 19, 5:06 pm, Michael Bayer <mike...@zzzcomputing.com> wrote: > this issue is fixed, you can download the tip of 0.6 or 0.7 in the > "development versions" section at: > > http://www.sqlalchemy.org/download.html > > On May 19, 2011, at 11:49 AM, Michael Bayer wrote: > > > > > > > > > this is a bug tickethttp://www.sqlalchemy.org/trac/ticket/2169, ill have a > > fix committed in a few minutes. > > > On May 19, 2011, at 5:20 AM, boothead wrote: > > >> Hi, > > >> I have a declarative class that I'd like to be able to add an > >> attribute to: (please ignore the eye burning capitals - it's an > >> existing schema :-) ) > > >> class Schedule(Base): > >> __tablename__ = "SomeSchedule" > >> __table_args__ = ( > >> dict(schema="Schedule", useexisting=True) > >> ) > > >> def __str__(self): > >> return "%s %s to %s" % (self.Name, self.Start, self.End) > > >> query = Session.query_property() > > >> id = Column(Integer, primary_key=True) > >> NameId = Column(Integer, ForeignKey("reference.name.id"), > >> nullable=False) > >> Value = Column(Integer, nullable=False) > >> Start = Column(Date, nullable=False) > >> End = Column(Date, nullable=False) > > >> On instances of this class I would like an attribute NextValue which > >> would be the Value column of the row in the table where self.id == > >> next.id and self.End == next.Start > > >> So do I use a column_property like: > > >> rs1 = Schedule.__table__.alias('rs1') > >> rs2 = Schedule.__table__.alias('rs2') > > >> next_val_select = select([rs2.c.Value], from_obj=[rs1.join(rs2, > >> rs1.c.NameId==rs2.c.NameId)])\ > >> .where(rs1.c.End==rs2.c.Start) > > >> RollSchedule.NextContract = column_property(next_contract_select) > > >> This gets me almost there, but the from clause in the main select is > >> aliased to rs2 which means that the columns are messed up: > > >> SELECT [RollSchedule_1].id AS [Schedule_RollSchedule_id], > >> [RollSchedule_1].[NameId] AS [Schedule_RollSchedule_NameId], > >> [RollSchedule_1].[Contract] AS [Schedule_RollSchedule_Contract], > >> [RollSchedule_1].[Start] AS [Schedule_RollSchedule_Start], > >> [RollSchedule_1].[End] AS [Schedule_RollSchedule_End], (SELECT name_1. > >> [stringId] > >> FROM reference.name AS name_1 > >> WHERE name_1.id = [RollSchedule_1].[NameId]) AS anon_1, (SELECT rs2. > >> [Contract] > >> FROM [Schedule].[RollSchedule] AS rs1 JOIN [Schedule].[RollSchedule] > >> AS rs2 ON rs1.[NameId] = rs2.[NameId] > >> WHERE rs1.[End] = rs2.[Start]) AS anon_2 > >> FROM [Schedule].[RollSchedule] AS rs2 > > >> Or should I do it more like this: > > >> RollSchedule.NextRolls = relationship(Schedule, > > >> primaryjoin=(Schedule.NameId==Schedule.NameId) & > >> (Schedule.End==Schedule.Start), > >> foreign_keys=[Schedule.NameId], > >> # lazy="joined", > >> join_depth=2) > > >> and have an assocaition_proxy. > > >> The query I'm looking for is: > > >> SELECT rs."NameId", rs."Contract", rs."Start", rs."End", > >> ( > >> SELECT reference.name."stringId" > >> FROM reference.name > >> WHERE reference.name.id = rs."NameId") AS anon_1, > >> ( > >> SELECT rs1."Contract" > >> FROM "Schedule"."RollSchedule" rs1 > >> JOIN "Schedule"."RollSchedule" rs2 > >> ON rs1.nameId = rs2.nameId > >> WHERE rs2."End" = rs1."Start" and rs.id = rs2.id > >> ) AS anon_2 > >> FROM "Schedule"."RollSchedule" rs > > >> Thanks in advance > >> Ben > > >> -- > >> 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 > >> athttp://groups.google.com/group/sqlalchemy?hl=en. > > > -- > > 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 > > athttp://groups.google.com/group/sqlalchemy?hl=en. -- 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.
