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))._select(db. segment.partof) seg2_run_ids = db(db.segment.waypoint.contains(request.args))._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.