Hello Joe,
Found your plain sql query already convoluted, there is not simpler way to
express it in SQL first? You seem to use join as if it was a where, join
not intent for this, using it like that will make thing slower...
Could this work :
subquery = db.executesql('''select course, event_code, min(finals) as
"min_time" from times
where id_competitor=XXXX and finals>0
group by course, event_code''')
rows = db((db.times.event_code.belongs(subquery[1])) &
(db.times.course.belongs(subquery[0])
& (db.times.min_time.belongs(subquery[2])) & (db.times.
competitor=='XXXX').select(db.times.ALL)
I can't try
:(
Richard
On Mon, Feb 17, 2014 at 6:38 AM, Joe Barnhart <[email protected]>wrote:
> 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.
>
--
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.
