On Wednesday, February 14, 2018 at 1:02:52 PM UTC-8, Dave S wrote:
>
>
>
> On Tuesday, February 13, 2018 at 7:37:22 AM UTC-8, Anthony wrote:
>>
>> On Tuesday, February 13, 2018 at 4:09:02 AM UTC-5, Dave S wrote:
>>>
>>> That's yielding 0 rows from the run table (for my chosen args).. It
>>> looks to me like it is requiring the segment row to match both arguments.
>>> If I use
>>> a/c/f/Cabot/Cabot in the url, I get the 10 rows for runs that have
>>> Cabot. But a/c/f/office/Cabot give 0 rows, even though at least run.id
>>> == 20 (see the example) should show up.
>>>
>>
>> OK, now I see how the data are structured. I think this should work:
>>
>>
> Thank you! I meant to mention yesterday that this is looking good. I'll
> add some comments shortly.
>
Both methods work with my main test data (the source of 111 X and 10 Y),
yielding 9 runs that match both. The alias method, however, let a
duplicate row sneak in. I need to test more pairs, but I think you
(Anthony) solved my problem.
BTW, can one do a groupby inappadmin/select?
>> seg1_run_ids and seg2_run_ids are sub-selects that return the run ids of
>> segments that match each of the waypoints. The final query includes runs
>> whose ids are in both sets of ids from the sub-selects. It will produce SQL
>> like:
>>
>> SELECT run.id, run.description, run.distance, run.duration
>> FROM run
>> WHERE run.id IN (SELECT segment.partof FROM segment WHERE segment.waypoint
>> LIKE '%[value 1]%')
>> AND run.id IN (SELECT segment.partof FROM segment WHERE segment.waypoint
>> LIKE '%[value 2]%')
>>
>> This can also be done with a double join with aliases, but that is a
>> little more cumbersome to put together using the DAL. I think it would be
>> something like this:
>>
>> join1 = db.segment.with_alias('seg1').on('seg1.partof = run.id')
>> join2 = db.segment.with_alias('seg2').on('seg2.partof = run.id')
>> seg1q = 'seg1.waypoint like "%%%s%%"' % request.args(0)
>> seg2q = 'seg2.waypoint like "%%%s%%"' % request.args(1)
>> runs = db((db.run.id > 0) & seg1q & seg2q).select(join=[join1, join2])
>>
>> Note, the (db.run.id > 0) is just needed so the DAL can figure out the
>> table for the query and so a Query object is constructed in conjunction
>> with the "& seg1q" (otherwise, "seg1q & seg2q" by itself would produce an
>> error because seg1q and seg2q are just strings). The above will produce SQL
>> like:
>>
>> SELECT run.id, run.description, run.distance, run.duration
>> FROM run
>> JOIN segment AS seg1 ON seg1.partof = run.id
>> JOIN segment AS seg2 ON seg2.partof = run.id
>> WHERE run.id > 0
>> AND seg1.waypoint like "%[value 1]%"
>> AND seg2.waypoint like "%[value 2]%"
>>
>> Anthony
>>
>
>
/dps
--
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/d/optout.
