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 [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
