On Jun 1, 3:31 pm, Michael Bayer <mike...@zzzcomputing.com> wrote: > On Jun 1, 2010, at 1:06 PM, SQLAlchemy User wrote: > > I have two tables (both declarative representations): the first, > > Device, describes devices (device ID, import time, other details), and > > the second, DeviceState, lists state changes for those devices (device > > ID, old state, new state, timestamp). The device table is imported > > daily and contains state information at the time the export is > > performed. The state changes come in to DeviceState as the devices > > change state. > > > I have a property on Device as follows: > > > @property > > def curradminstate(self): > > return > > Session.query(DeviceState.adminstate).filter(DeviceState.deviceid == > > self.deviceid).filter(DeviceState.insert_ts > > > self.import_ts).order_by(desc(DeviceState.insert_ts)).limit(1).scalar() > > or self.adminstate > > > ... and this "works" as expected: that is, I can query > > device.curradminstate as a property instead of a method. > > > I'd like to create a Comparator so I can do something like the > > following: > > > Session.Query(Device).filter(Device.curradminstate == "ACTIVE") > > you don't need a custom comparator here, you just want to map a column > attribute to a correlated subquery, such as that illustrated > inhttp://www.sqlalchemy.org/docs/mappers.html#sql-expressions-as-mapped.... > you have an "I want the max(related X)" type of pattern which is fairly > common, I should get around to adding several examples for this to the wiki.
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; 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.
