You need to define a set that includes all three tables in the relation. ((db.genres.id==db.movies_genres.genres_id) & (db.movies_genres.movie_id==db.movies.id))
To get the set of genres for a specific movie, do ((db.genres.id==db.movies_genres.genres_id) & (db.movies_genres.movie_id==db.movies.id) & (db.movies.id==1)) # or whatever variable you like Notice this query allows you to retrieve information from all three tables at one time. On Saturday, July 28, 2012 5:18:43 AM UTC-4, Vincenzo Ampolo wrote: > > Hi, > > Is there a way to get many to many relationship data easily? > The web2py book says > http://web2py.com/books/default/chapter/29/6#Many-to-many that "it may > be convenient to define a new set on which to perform operations" and it > basically does a join between the fields. > > But what if I've this models: > > db.define_table('genres', > Field('name') > ) > > db.define_table('movies', > Field('imdb_id', 'integer'), > Field('title'), > Field('poster', 'text'), > Field('trailer', 'text'), > Field('plot', 'text'), > Field('date', 'datetime'), > Field('updated', 'datetime') > ) > > db.define_table('movies_genres', > Field('movie', db.movies, requires=IS_IN_DB(db, 'movies.id', > db.movies._format)), > Field('genre', db.genres, requires=IS_IN_DB(db, 'genres.id', > db.genres._format)) > ) > > and i do: > > movie = db.movies[1] > > movie has an movies_genres which is a set. can I use movie.movies_genres > to get a genre instance ? > I'm asking this because I'm in a view and I would like a compact way to > print genre.name from a movie instance. > > Thank you > > best regards, > -- > Vincenzo Ampolo > http://vincenzo-ampolo.net > http://goshawknest.wordpress.com > --
