On Feb 17, 2010, at 12:27 PM, Hermann Himmelbauer wrote: > Hi, > I have the following many to one relation: > > - A bank account table (acc) > - An Interest rate table, which relates to the account table. Colums are an > ID, a rate (decimal), a date and a flag outlining if the interest rate is > credit or debit ('H' / 'S') > - One account may have multiple interest rates (one to many) > > What I now want is to retrieve the most recent (the current valid) interest > rate for a specific account. For now, I did this like this: > > mapper_acc = mapper(Acc, table_acc, properties = { > # Current debit irate > 'current_debit_irate': relation( > IRate, order_by = table_irate.c.date.desc(), > uselist = False, primaryjoin = and_( > table_acc.c.accid == table_irate.c.accid, > table_irate.c.type == "S"), > cascade="all") > }) > > > This works but is very inefficient, as this mapper seems to read in all > interest rate objects despite I use "uselist=False", which is slow. So I > wonder if it's possible to optimize this in some way so that SQLAlchemy > constructs some specific SQL, something like: > > "select * from irates where irateid = (select irateid, max(date) from irates > where accid = 123 and type = 'S' group by date)"
Here, you'd build the query representing the "max()" for your related item, then create a "non-primary" mapper which maps "IRate" to it. Build your relation then using that nonprimary mapper as the target. I think i just showed this to someone on this list about a week ago. iratealias = irate.alias() i.e. irate_select = select(irate).where(irate.c.id=select([iratealias.c.id, max(date)]).where(...)).alias() irate_mapper = mapper(IRate, irate_select, non_primary=True) mapper(Acc, acc, properties={"current_irate", relation(irate_mapper)}) -- 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.