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:
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])
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
--
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.
