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
