Ah, belongs() + a clever select SQL. Thanks. Al
On Jun 29, 7:04 pm, mdipierro <mdipie...@cs.depaul.edu> wrote: > If I understand: > > for row in db(db.link_table.table1==r.id) > (db.table2.id==db.linked_table.table2).select(db.table2.id,db.table2.other): > print row.id, row.other > > if no double links else > > sub=db(db.link_table.table1==r.id)._select(db.linked_table.table2) > for row in > db(db.table2.id.belongs(sub)).select(db.table2.id,db.table2.other): > print row.id, row.other > > linked_view = db(db.table1.id == db.link_table.table1) & (db.table2.id > > > == db.linked_table.table2)) > > > for row in linked_view(db.table1.id == r.id).select([fields from > > table2 I want]) > > On 29 Giu, 17:54, Al <rayalans...@yahoo.com> wrote: > > > > > Given a many-to-many relationship between table1 and table2 and a row > > in table1, I am trying to figure out the fastest way to select all the > > rows in table2 that are associated with the row. (I'm still trying to > > pick up web2py, so thanks for everyone's patience if I've missed the > > blatently obvious.) > > > From the web2py book documentation, the best I can figure out is > > something like this: > > db.define_table('link_table', > > Field('table1', db.table1), > > Field('table2', db.table2) > > ) > > > # Pick the row from table 1 > > r = db(db.table1.name == "Example").select()[0] > > > linked_view = db(db.table1.id == db.link_table.table1) & (db.table2.id > > == db.linked_table.table2)) > > > for row in linked_view(db.table1.id == r.id).select([fields from > > table2 I want]) > > # Do something clever for the matching rows I've found > > pass > > > But I keep thinking there ought to be something cleverer that I can > > do, especially given that r has a set containing the rows of > > link_table that contain the entries of table2 that I want. But I can't > > figure out how to leverage that set into a shorter query. > > > Thanks, > > Al- Hide quoted text - > > - Show quoted text -