this is a bug ticket http://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 at
> http://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.
