Joe, it seems to me all you need is this part here:
select course, event_code, min(finals) as "min_time" from times
where id_competitor=XXXX and finals>0
group by course, event_code
Or are you interested in returning other columns within the 'times' table?
if so, just use min(other columns) in the query, no need for a derived
table.
So, in any case, web2py works a little differently. Taking the book example
and modifying it, here's what I get...
for row in db(db.times.competitor==xxxx).select(
db.times.course, db.times.event_code, min_time,
groupby=(db.times.course,db.times.event_code)):
print row.times.course, row[min_time]
What you need to do before that can work is to create the min_time
operator...
min_time = db.times.finals.min()
oh, but you wanted to only select non negative numbers, and finals has some
negatives in it... (add a where clause to the select)
Good luck!
On Monday, February 17, 2014 2:46:23 PM UTC-7, Joe Barnhart wrote:
>
> 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 <joe.ba...@gmail.com>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 <joe.ba...@gmail.com>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 web2py+un...@googlegroups.com.
>>>>>
>>>>> 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 web2py+un...@googlegroups.com.
>>> 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 web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
