On Jun 2, 6:43 am, Michael Bayer <mike...@zzzcomputing.com> wrote: > 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) > ) > Thanks so much - this worked perfectly. -- 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.
