On Jun 2, 2010, at 12:48 AM, SQLAlchemy User wrote:
> OK, I get the need for column_property, but the select is fairly
> complex and involves unions of the containing class (and it hasn't
> been defined yet). How do I get around this, and other questions
> below....
>
> I think I got the SQL right (for a device with id = "foo"):
>
> SELECT v1.adminstate
> FROM (
> (SELECT devices.adminstate, devices.processdate as insert_ts,
> devices.deviceid
> FROM devices
> WHERE devices.deviceid = "foo")
> UNION
> (SELECT devicestates.adminstate, devicestates.insert_ts,
> devicestates.deviceid
> FROM devicestates
> WHERE devicestates.deviceid = "foo")) AS v1
> ORDER BY v1.insert_ts DESC LIMIT 1;
You dont need to get into UNION here. My suggestion would be to avoid UNION
at all costs, in fact, they perform poorly and are very cumbersome to work
with. A correlated subquery here should do what you need: (i.e., when you
say query(Device).filter(Device.curradminstate=='FOO'):
select device.* from
device where
coalesce(
(select devicestate.adminstate from devicestate where
devicestate.insert_ts > device.import_ts order by devicestate.insert_ts
limit 1),
device.adminstate
) = 'FOO'
you'd need to verify the above works first (particularly the LIMIT inside the
subquery, I know mysql can do it, not sure about PG, don't know your backend).
property is:
class Device(Base):
...
device_subq = select([DeviceState.adminstate]).where(DeviceState.insert_ts >
device.import_ts).order_by(DeviceState.insert_ts).limit(1).as_scalar()
Device.curradminstate = column_property(
func.coalesce(device_subq, Device.adminstate)
)
>
> So, three questions come to mind:
> 1) how do I create a select statement that references a label (I did
> it using quotes - that is, select(['v1.adminstate'],...) but not sure
> whether that's the most correct way, and I'm running into difficulties
> later on in the order_by);
> 2) how do I reference Device from within the select statement that's a
> column_property for Device; and
> 3) am I on the right track here or (more likely) am I off-base and
> missing something simple (like func.max())?
>
> Thanks again -
>
> S.
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlalch...@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 sqlalch...@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.
