Once defined that you want a recordset that is the result of a join
between the two tables, with all the fields of the two tables and
containing only the records having the maximum start date, I think
that the right query for the job is
select curve.*, site.*
from curve
inner join
(
select max("start") as start, site from curve
group by site
) b
on b.start = curve.start
and
b.site = curve.site
inner join
site
on site.id = curve.site
[antimysql mode on]
That blowing query on the first post should have never been written,
and if I managed to see it working in anything calling itself
relational database I'll be erasing all database's instances from my
pc
[antimysql mode off]
I don't think that DAL allows inner join on a constructed str... so no
tricks come to my mind :D
but maybe Massimo or heavy DAL devs can help on that (DAL.py, lines
1116-1123).
For the time being you can define a view e.g.
create view curve_max as
select 1 as id, max("start") as start, site from curve
group by site
and define a "fake" model e.g.
db.define_table('curve_max', #I swear I'll never do insert or update
on this one
Field('start', 'date'),
Field('site', db.site),
migrate=False,
fake_migrate=False
)
you will have to swear to observe a strict "only read" policy on that,
but you can do
query = db((db.curve.start == db.curve_max.start) & (db.curve.site ==
db.curve_max.site) & (db.site.id ==
db.curve.site)).select(db.curve.ALL, db.site.ALL)
and live happily (until someone smarter thinks how to do that NOT
using an intermediate view with a "fake" model)
Can you live with that ?
