Actually, there is a "hackier" way, but I advise strongly not to
follow this path.
Psyco-maniac query that returns the same result (this is working on
Postgresql, on MSSQL || must be replaced with +, on mysql there is the
CONCAT function for that, but I don't have mysql installed to test it)
Postgre
select curve.*, site.*
from curve
inner join
site
on site.id = curve.site
where cast(start as varchar) || '$$$' || site IN (select
cast(max("start") as varchar) || '$$$' || site from curve
group by site)
mssql
select curve.*, site.*
from curve
inner join
site
on site.id = curve.site
where cast(start as varchar) + '$$$' + site IN (select
cast(max("start") as varchar) + '$$$' + site from curve
group by site)
mysql
select curve.*, site.*
from curve
inner join
site
on site.id = curve.site
where CONCAT(start, '$$$', site) IN (select concat(max("start"),'$$
$',site from curve
group by site)
So, to explain, what are we doing ?
We are constructing a "composed" column concatenating start and site
(the '$$$' in the middle is there to avoid potential collision). Start
and site are chosen because they are: the max() we want to filter for
and the external key we must join to.
We can use that to avoid the inner join and placing the group by in
the IN () construct.
Seems maybe a smart idea but here the database is forced to do a full
scan on the table because the IN condition is evaluated on a function,
not on a native field. This is the reason why I'm not recommending it,
execution time is greater than with the solution from the above post.
Anyway, time for big hacks :D
The way to reproduce the query using DAL is the following (only
postgresql example given)
concatted = "cast(start as varchar) || '$$$' || site IN (%s)"
max_concatted = "cast(max(start) as varchar) || '$$$' || site as
start"
q1 = db(db.curve.id>0)._select(max_concatted, groupby=db.curve.site)
query = db((db.site.id == db.curve.site) & (concatted %
(q1[:-1]))).select(db.curve.ALL, db.site.ALL)
It works, but again, I prefer the method from the previous post, both
for "hackiness", code maintenance and lower execution times.
