I have an intermediate table representing a many to many relationship
between users and songs. Users can like/dislike many songs, and songs can
be liked/disliked by many users. I am trying to retrieve a list of 30
songs, that can be any songs except for songs that have been disliked by
the user.
Here's a simplified version of the 3 tables:
#Table 1:
db.define_table('auth_user',
Field('id')
)
#Table 2:
db.define_table('songs',
Field('id')
)
#Table 3 (intermediate)
db.define_table('likes_and_dislikes',
Field('username', 'reference auth_user'),
Field('songname','reference songs'),
Field('like_score', 'integer')
)
Originally I had made 2 database selects to accomplish this, first finding
all the songs that have been disliked by the user (this can be a very high
number), and then querying songs to avoid the disliked songs:
disliked_ids = []
dislike_query = (db.likes.username==auth.user.id) & (db.likes.like_score==-1
) #score of -1 represents a dislike
disliked_songs = db(dislike_query).select().as_list()
for row in disliked_songs:
disliked_ids.append(row['songname'])
query = (~db.songinfo.id.belongs(disliked_ids))
song_rows = db(pre_query).select(orderby='<random>', limitby=(0,30))
I'm sure there's a more efficient way to do this, and I have been looking
into left joins and inner joins to accomplish everything in one database
select. My understanding of joins get a little hazy once there needs to be
more than join performed, any help is appreciated!
--
---
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/groups/opt_out.
