Re: [web2py] Re: Helper for many-to-many result sets?
This all looks promising. Meanwhile, here's my model with irrelevant Fields
omitted and below that my query for the Select. Thanks for the help!
A simplified version of my model:
db.define_table('movie',
Field('title','string'),
db.define_table('person',
Field('name', 'string', unique=True),
db.define_table('star',
Field('movie_id', db.movie),
Field('person_id', db.person),
movies_and_stars = db((db.movie.id == db.star.movie_id) &
(db.star.person_id == db.person.id))
On Tuesday, August 14, 2012 2:16:56 AM UTC-4, rochacbruno wrote:
>
> I guess you can also do:
>
> table = TABLE(*[TR(TD(movie.title), TD(UL(*[LI(star.name) for star in *
> movie.stars.select()*])) for movie in movies])
>
> when a table is referenced by another, he gets a "DAL Set" object with the
> referer name.
>
> movie.*stars *will be a DAL Set. which has *select, update, delete*methods
>
> But it will only happens depending on your model and relations definition.
>
> Can you share your model for those 2 tables?
>
>
> On Tue, Aug 14, 2012 at 3:10 AM, Bruno Rocha
> > wrote:
>
>> Foreach movie in movies
>> print movie.title
>> foreach stars in movie.stars
>>print star.name
>>
>
>
--
Re: [web2py] Re: Helper for many-to-many result sets?
Salient bits of model at the top of the thread. On Tuesday, August 14, 2012 2:16:56 AM UTC-4, rochacbruno wrote: > > I guess you can also do: > > table = TABLE(*[TR(TD(movie.title), TD(UL(*[LI(star.name) for star in * > movie.stars.select()*])) for movie in movies]) > > when a table is referenced by another, he gets a "DAL Set" object with the > referer name. > > movie.*stars *will be a DAL Set. which has *select, update, delete*methods > > But it will only happens depending on your model and relations definition. > > Can you share your model for those 2 tables? > > > On Tue, Aug 14, 2012 at 3:10 AM, Bruno Rocha > > wrote: > >> Foreach movie in movies >> print movie.title >> foreach stars in movie.stars >>print star.name >> > > --
Re: [web2py] Re: Helper for many-to-many result sets?
I guess you can also do: table = TABLE(*[TR(TD(movie.title), TD(UL(*[LI(star.name) for star in * movie.stars.select()*])) for movie in movies]) when a table is referenced by another, he gets a "DAL Set" object with the referer name. movie.*stars *will be a DAL Set. which has *select, update, delete* methods But it will only happens depending on your model and relations definition. Can you share your model for those 2 tables? On Tue, Aug 14, 2012 at 3:10 AM, Bruno Rocha wrote: > Foreach movie in movies > print movie.title > foreach stars in movie.stars >print star.name > --
Re: [web2py] Re: Helper for many-to-many result sets?
Taking this exact example
Foreach movie in movies
print movie.title
foreach stars in movie.stars
print star.name
I guess `movie.stars` is a list:string field?
if so.. there's one-liner solution
table = TABLE(*[TR(TD(movie.title), TD(UL(*[LI(star.name) for star in
movie.stars]))) for movie in movies])
if db.stars has a list:reference to movies
table = TABLE(*[TR(TD(movie.title),
TD(UL(*[LI(star.name) for star in
db(movie.id.belongs(db.stars.movies)).select(db.stars.name)]))) for movie
in movies])
or with a subquery (for each loop iteration)
table = TABLE(*[TR(TD(movie.title),
TD(UL(*[LI(item.stars.name) \
for item in *db((db.movies.id ==
db.stars.id) & \*
* (
db.movies.id == movie.id)).select(db.stars.name)*]))) \
for movie in movies])
On Mon, Aug 13, 2012 at 9:51 PM, Cliff Kachinske wrote:
> There may be a more elegant way to do this, but it does work.
>
> Be sure to select the movie id in your query.
>
> Then you can do something like this:
>
> rows = db(query).select(.) #whatever you're doing
>
> trows = []
> stars = []
> for i, r in enumerate rows:
> stars.extend([r.stars.name, BR()])
> if r.movie.id != rows[i+1].movie.id or i+1==len(rows):
> trows.append(TR(
> TD(r.movie.name),
> TD(stars), # Other stuff from the row follows
>
> )
> )
> stars=[] # Reset the stars list
> return(TABLE(*trows))
>
>
>
>
>
> On Monday, August 13, 2012 6:04:44 PM UTC-4, Mike Girard wrote:
>>
>> I have a movie table that has a many-to-many relationship with a person
>> table expressed through a star table.
>>
>> A simplified version of my model:
>>
>> db.define_table('movie',
>>
>> Field('title','string'),
>>
>> db.define_table('person',
>>
>> Field('name', 'string', unique=True),
>>
>> db.define_table('star',
>>
>> Field('movie_id', db.movie),
>>
>> Field('person_id', db.person),
>>
>> I am able to create a select that joins the three tables and produces a
>> result with all the data I need.
>>
>> It's easy to iterate through the result and produce something akin to
>> this:
>>
>> Movie Title Star 1
>>
>> Movie Title Star 2
>>
>> Movie Title Star 3
>>
>>
>> What I want is:
>>
>> Movie Title 1
>>
>> Star 1, Star 2, Star 3
>>
>>
>> Movie Title 2
>>
>> Star 1, Star 2, Star 3
>>
>>
>> Programmatically, I'd like something like:
>>
>> for each movie in rows
>>
>> move.title
>>
>>
>>
>> for each star in movie.stars
>>
>> star.name
>>
>>
>> Before I write a function to pre-process the result, can someone tell me
>> if there is a helper for producing a result of this kind?
>>
>> This thread addresses the same issue -
>>
>> https://groups.google.com/**forum/?fromgroups#!topic/**web2py/GQsMt4qvqSs
>>
>> - but I was unable to discern the solution the question asker had
>> produced for himself, the key to which was this:
>>
>> "web2py automatically add the many-to-many sets to an instance with the
>>
>> same name of the relation table"
>>
>> I do not know what that means.
>>
> --
>
>
>
>
--
[web2py] Re: Helper for many-to-many result sets?
There may be a more elegant way to do this, but it does work.
Be sure to select the movie id in your query.
Then you can do something like this:
rows = db(query).select(.) #whatever you're doing
trows = []
stars = []
for i, r in enumerate rows:
stars.extend([r.stars.name, BR()])
if r.movie.id != rows[i+1].movie.id or i+1==len(rows):
trows.append(TR(
TD(r.movie.name),
TD(stars), # Other stuff from the row follows
)
)
stars=[] # Reset the stars list
return(TABLE(*trows))
On Monday, August 13, 2012 6:04:44 PM UTC-4, Mike Girard wrote:
>
> I have a movie table that has a many-to-many relationship with a person
> table expressed through a star table.
>
> A simplified version of my model:
>
> db.define_table('movie',
>
> Field('title','string'),
>
> db.define_table('person',
>
> Field('name', 'string', unique=True),
>
> db.define_table('star',
>
> Field('movie_id', db.movie),
>
> Field('person_id', db.person),
>
> I am able to create a select that joins the three tables and produces a
> result with all the data I need.
>
> It's easy to iterate through the result and produce something akin to this:
>
> Movie Title Star 1
>
> Movie Title Star 2
>
> Movie Title Star 3
>
>
> What I want is:
>
> Movie Title 1
>
> Star 1, Star 2, Star 3
>
>
> Movie Title 2
>
> Star 1, Star 2, Star 3
>
>
> Programmatically, I'd like something like:
>
> for each movie in rows
>
> move.title
>
>
>
> for each star in movie.stars
>
> star.name
>
>
> Before I write a function to pre-process the result, can someone tell me
> if there is a helper for producing a result of this kind?
>
> This thread addresses the same issue -
>
> https://groups.google.com/forum/?fromgroups#!topic/web2py/GQsMt4qvqSs
>
> - but I was unable to discern the solution the question asker had produced
> for himself, the key to which was this:
>
> "web2py automatically add the many-to-many sets to an instance with the
>
> same name of the relation table"
>
> I do not know what that means.
>
--
