Am Donnerstag 18 Februar 2010 02:07:15 schrieb Michael Bayer:
> 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)})
Wow, that was complicated. I managed to simplify (and probably speed up) the
query by using order_by with limit. To sum up, I tried the following:
1) Your idea with a slightly modified mapper:
irate_select =
select([table_irate]).alias().order_by(table_irate.c.date.desc()).limit(1).alias()
irate_mapper = mapper(IRate, irate_select, non_primary = True)
mapper(Acc, acc, properties={"current_debit_irate", relation(irate_mapper)})
---> Did not work as my database (MaxDB) unfortunately does not support order
by in subqueries
2) Hint from stepz via #freenode:
# Current debit irate
'current_debit_irate_new2': relation(
IRate, uselist=False,
primaryjoin=table_irate.c.irateid == select(
[table_irate.c.irateid], table_irate.c.accid == table_acc.c.accid
).correlate(table_acc).order_by(
table_irate.c.date.desc()).limit(1)),
----> Does also not work for the same reason.
3) Hint in another list reply: Use the original with lazy='dynamic'
# Dynamic loading
'current_debit_irate_dynamic': relation(
IRate, order_by = table_irate.c.date.desc(),
uselist = False, lazy = 'dynamic', primaryjoin = and_(
table_acc.c.accid == table_irate.c.accid,
table_irate.c.type == "S"),
cascade="all"),
And then in my class, I have something like this:
@property
def current_debit_irate(self):
return self.current_debit_irate_query[0]
----> This adds a limit 1 at the end of the query and acutally works! Great!
---------- Conclusion -----------
To my mind, an excellent thing would be to add something like "limit" similar
to "order_by" to the relation() specifier, as this would then save all of the
above.
Best Regards,
Hermann
--
[email protected]
GPG key ID: 299893C7 (on keyservers)
FP: 0124 2584 8809 EF2A DBF9 4902 64B4 D16B 2998 93C7
--
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.
