retrieve data from many to many relation

[Vincenzo Ampolo]

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

[Alec Taylor]

Vincenzo: Have you looked at: https://github.com/niphlod/w2p_tvseries

--

[Cliff Kachinske]

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.

[Vincenzo Ampolo]

On 07/28/2012 03:38 AM, Alec Taylor wrote:
> Vincenzo: Have you looked at: https://github.com/niphlod/w2p_tvseries

What is that ? I can't figure out from the "objectives" section what
that application does...

On 07/28/2012 05:37 AM, Cliff Kachinske wrote:> 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.

Which is exactly like the web2py books suggests. Btw to retrieve all the
information of a movie in one shot (so join movie with genres with casts
with pictures) may be really database intense (3 joins!) So after a bit
of hacking I figured out a simply way to accomplish the same goal:

web2py automatically add the many-to-many sets to an instance with the
same name of the relation table. In my case I've movie.movies_genres for
each movie. Given so I can easily get the genres of a movie in a
template just doing.

In [36]: for m in movie.movies_genres.select(): print m.genre.name
Thriller

Best Regards,

[Mike Girard]

I am sure that this answer is exactly what I am looking for to solve a problem I am having. However, I don't quite understand the particulars. Vincenzo, could you please provide an example of the queries used here to get the movie linked up with the genre. Maybe some code? 

[Cliff Kachinske]

Vicenzo, just a couple of thoughts.

1.  Three joins is not database intense.  Joining tables is what they do.  Correct indexing is the key to good performance.

2.  If you have found a way to get Web2py to fetch the data, it is just doing the join for you behind the scenes.  Same difference.

On Saturday, July 28, 2012 5:25:33 PM UTC-4, Vincenzo Ampolo wrote: