Hi Richard --
I thought about a subquery... But I've never seen or heard of the trick of
using "subquery[0]" to pick out one column of the subquery.
I see your approach is to materialize the subquery as a Rows object and
then pick out elements. I don't think this will work as expected tho
because the "belongs" logic will choose incorrect values. For example say
the time is 10 seconds, but there are 20 different rows (NOT "best" times)
with the same time. Your approach will choose the 10 sec. time for ALL
combinations of event code and course -- not just the SPECIFIC combination
in the row that matches the "best" time..
That's why I chose a "join" -- it forces the elements to match perfectly.
But thanks for the technique. The idea of materializing the Rows and
using it in another query bears thinking about.
-- Joe
On Monday, February 17, 2014 7:32:57 AM UTC-8, Richard wrote:
>
> 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]<javascript:>
> > 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] <javascript:>.
>> 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.
