Depends how you are displaying your list to do the liking and unliking.
A few thoughts....
Make sure your composite index has the field which filters out the most
records first.
In your controller you may be able to cut out most queries by considering
something like this:
- Hit the DB once and make a dict of all the listed songs with a flag
whether liked or not.
- Refer to the list when displaying the 'like' 'unlike' labels for each
song.
- Dispense with things like this db.likes.songname.requires=IS_NOT_IN_DB
etc
On Tuesday, September 11, 2012 10:06:54 PM UTC+1, Mark Li wrote:
>
> I went with the unique composite field index method (unique across 3
> fields, not just 2), with my database as follows:
>
> db.define_table('likes',
> Field('username', 'reference auth_user'),
> Field('songname', 'reference songs'),
> Field('playlist_name', 'reference playlist'))
>
> if auth.is_logged_in():
> already_liked = db((db.likes.username==auth.user.id) & (db.likes.
> playlist_name==request.vars.playlist_name))
>
> db.likes.songname.requires=IS_NOT_IN_DB(already_liked,
> 'likes.songname')
>
>
> I also changed my add_like() controller function to the following:
>
>
> def add_like():
>
>
> ret = db.likes.validate_and_insert(username=auth.user.id,playlist_name
> = request.vars.playlist_name, songname = request.vars.songname)
>
> if ret.errors:
>
> already_liked = (db.likes.username==auth.user.id) & (db.likes.
> playlist_name==request.vars.playlist_name) & (db.likes.songname==request.
> vars.songname)
> db(already_liked).delete()
>
> return "like removed"
>
> else:
> return "like added"
>
> I used the web2py ajax function to call the add_like action. The whole
> process seems somewhat sluggish, are there any unnecessary database calls
> I'm making?
>
> On Tuesday, September 11, 2012 4:53:44 AM UTC-7, villas wrote:
>>
>> How about: in 'likes' make a unique composite index of both fields
>> (created at DB level). Try to insert a record. If it fails (because of
>> the index) then delete instead.
>>
>>
>> On Monday, September 10, 2012 11:58:03 PM UTC+1, Mark Li wrote:
>>>
>>> I have 3 datatables as follows:
>>>
>>>
>>> db.define_table('songs',
>>> Field('songname'),
>>> format='%(songname)s')
>>>
>>> db.define_table('likes',
>>> Field('username', 'reference auth_user'),
>>> Field('songname', 'reference songs'))
>>>
>>> with the 3rd table being db.auth_user
>>>
>>> I would like to implement a "like" button, where clicking it adds an
>>> entry to the intermediate table 'likes'. For example, if User1 likes Song1,
>>> it would perform the following:
>>> db.likes.insert(username=auth.user.id, songname=1)
>>>
>>>
>>>
>>> Right now I have the like button as the following in the view:
>>> {{=A('like me', callback=URL('add_like'))}}
>>>
>>> and the function as:
>>> def add_like():
>>> db.likes.insert(username=auth.user.id, songname=1)
>>>
>>> The problem I'm having is that a user should only be allowed to 'like' a
>>> song once; if the user has already liked the song, clicking on 'like' again
>>> should remove that like. Basically a toggle functionality for the 'like'
>>> button.
>>>
>>> I'm not sure how to implement the last part, as it seems checking the
>>> entire database for the existence of that record on every 'like' click is
>>> overkill. Is there an eloquent way (minimal database calls) to go about
>>> implementing this toggle functionality for a 'like' button?
>>>
>>>
>>>
>>>
>>>
>>>
--
