I think I've figured it out. I just needed one left join, but with the two
arguments in the ON clause:
query = ((db.likes.like_score!=-1) | (db.likes.like_score==None))
rows = db(query).select(db.songs.ALL, db.auth_user.id, db.likes.like_score,
left=db.likes.on((db.likes.songname == db.songs.id) & (db.likes.username
== auth.user.id))
, orderby='<random>', limitby=(0,30))
If there are any mistakes please let me know!
On Wednesday, July 17, 2013 8:05:18 PM UTC-7, Mark Li wrote:
>
> 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 web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
