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.