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.
