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.
> seg1_run_ids = db(db.segment.waypoint.contains(request.args[0]))._select(
> db.segment.partof)
> seg2_run_ids = db(db.segment.waypoint.contains(request.args[1]))._select(
> db.segment.partof)
> runs = db(db.run.id.belongs(seg1_run_ids) & db.run.id.belongs(seg2_run_ids
> )).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.
