So I'm trying to use the DAL to create all of my queries, but I may have
just exceeded its capability.
My task is to create a view of "best times" for a particular sport. The
sport has multiple events and the user is interested in collecting their
best times over the entire database, organized by event. The SQL looks
something like this:
select * from times as t
inner join (
select course, event_code, min(finals) as "min_time" from times
where id_competitor=XXXX and finals>0
group by course, event_code
) as tt
on t.event_code=tt.event_code
and t.course=tt.course
and t.finals=tt.min_time
and t.id_competitor=XXXX
order by t.course, t.event_code
The XXXX represents a specific literal ID number for the competitor. This
query is to be run for each competitor when they choose to see the view, so
limiting it to a single person is the desired approach.
The inner join choose the best (minimum) time for each course and
event_code.
Disqualifications are stored as negative times, so the clause to restrict
finals>0 is to exclude any of these outliers.
I've been studying up on the alternate join syntax for the DAL but I don't
see a way to handle the conditions such as "finals>0" in the new syntax.
If the "on" method could be attached to a Set object instead of only a
Table, maybe I could approach this query.
But I thought I'd ask the real SQL experts before I give up. Also, what is
my fallback if I need to do this query outside of DAL?
-- Joe B.
--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/groups/opt_out.
