Hi Richard --
Don't need to try it... Do a thought experiment... The "belongs" matches
the entire set of all affected times, event_codes and courses. It becomes
the cartesian product of all combinations of time, event_code, and course.
It matches N*M*K entries in the times table, where N, M, and K are the
counts of the members of the set. By contrast, the number of rows in the
minimum times query is a tiny fraction of that.
Let's say I have a competitor with a minimum time of 10 sec. in event 1,
course A. He may also have that exact time (on a bad day) for event 2,
course B. In this scenario, event 2 course B would normally be a much
faster time -- in fact his "best" times has such a faster time. But when
you do the overall query with "belongs" on ALL members of times,
event_code, and course, you will match BOTH 10 sec. times -- event 1 on
course A and event 2 on course B, so both times will appear in the final
result. (Along with the true best time for event 2, course B, which was
faster than 10 sec.)
I could probably do a correlated subquery to solve this problem, but those
are famously inefficient. The join, convoluted as it is (and I agree
there) is still the best way.
What I really wish I could do is use a select to return the min(time) and
the ID of the row it occurred on. But GROUP BY won't allow that. One of
the limitations of the set theory underpinning SQL. That's where the
correlated subquery comes it -- do a select with ORDER BY on time, then
choose LIMIT BY 0,1 to get only one row. But that query has to be repeated
for every combination of course and event_code. I have about 20 rows to
search for a typical competitor and each query above takes around 700 ms.
so that's not a good option!
-- Joe B.
On Monday, February 17, 2014 12:51:07 PM UTC-8, Richard wrote:
>
> But what you do in the join should be reproducible in a where clause... I
> start rewriting your join into a where clause at first and realize that I
> would need to rewrite 3-4 for time the same subquery in sql or I had to
> write a recursive query that would not help in translation into web2py
> query...
>
> I don't see how the multiples belongs would not lead to the samething as
> you query did you try it?
>
> You still have the option of just using db.executesql(''' your actual sql
> query ''')
>
> Richard
>
>
> On Mon, Feb 17, 2014 at 3:24 PM, Joe Barnhart
> <[email protected]<javascript:>
> > wrote:
>
>> 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]>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] <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.
