On Jun 1, 2010, at 1:06 PM, SQLAlchemy User wrote: > Hi all: > > Hoping some kind soul on the list will be able to advise on this use/ > implementation problem I'm having: > > 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 in http://www.sqlalchemy.org/docs/mappers.html#sql-expressions-as-mapped-attributes . 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. > > but I'm completely lost as to how to do this. I've tried creating a > class that returns a PropComparator but it hasn't worked (I get a > variety of errors; here's my latest attempt which has at least one > error in doit()): > > class AdminStateComparator(PropComparator): > ''' > Compares admin states (case-insensitive) > ''' > def doit(self): > device = self.prop.parent.class_ > return > Session.query(DeviceState.adminstate).filter(DeviceState.deviceid == > device.deviceid).filter(DeviceState.insert_ts > > self.import_ts).order_by(desc(DeviceState.insert_ts)).limit(1).scalar() > or device.adminstate > sprop = property(doit) > def operate(self,op,other,**kwargs): > return op(self.sprop,other,**kwargs) > > (hope the formatting came through - if not, assume appropriate > indentation.) > I have read the docs (several times!) but don't quite understand how > PropComparator is supposed to work, especially with multiple tables. I > got it working with transparent encryption/decryption (using > func.aes_encrypt) for another table, but that was within a single > table. In addition, I'm enough of a novice with SQL that I can't even > describe what I want to do that way - I'm hoping someone on the list > will be able to help regardless. > > Thanks, > > 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.
