0

I have a database structure for playlists and tracks.

My database code looks like this: 

 #table for tracks
 db.define_table('track',
            Field('artist'),
            Field('album'),
            Field('title'),
            Field('duration', 'float'),
            ......
            )
#table for playlists
db.define_table('playlist',
            Field('title')
            )
#table for references
db.define_table('playlist_reference',
            Field('playlist', 'reference playlist'),
            Field('track','reference track')
            )

I have created a controller that retrieves all tracks in a specific playlist by accessing these tables. Here's what that code looks like: 

def get_playlist_tracks():
    title = request.vars.title
    tracks = []

    q = (title == db.playlist.title)

    #searches the playlist database for the playlist that 
    #matches the title of the playlist whose tracks I want to retrieve
    #and gives me its id
    plist_id = db(q).select().first().id

    #returns all the references in which that playlist appears
    q = (plist_id == db.playlist_reference.playlist)
    refs = db(q).select(db.playlist_reference.ALL)

    #for each reference I get the track id and append to tracks array
    for i, r in enumerate(refs):
        t_id = r.track
        q = (t_id == db.track.id)
        track = db(q).select(db.track.ALL)
        print track
        tracks.append(track)

    return response.json(dict(
        tracks=tracks)
    )

For some reason, this code does not return what I expect, which is a list of the tracks from that playlist.

What could be the problem?

Thank you

metmirr
  • 4,234
  • 2
  • 21
  • 34
user2030942
  • 215
  • 5
  • 25
  • `refs = db(plist_id == db.playlist_reference.playlist).select(db.playlist_reference.ALL)` Should return one or more refs? I think every playlist has an id and it is unique right? – metmirr Dec 06 '16 at 07:28
  • As I understand: you are getting a title with `title = request.vars.title` then you query this title on playlist table to get the playlist id by title of playlist, after this you query this id on playlist_reference(`q = (plist_id == db.playlist_reference.playlist)`). So now you have got playlist id and if your purpose to get all track which on this playlist; query all track which has this playlist id. – metmirr Dec 06 '16 at 07:30
  • and that is what I'm doing when I run the code in the for loop. The four loop traverses all of these references, extracts the tract id for each one and then uses that track id to traverse the tracks table and return a track. For some reason this still doesn't return what I need. – user2030942 Dec 06 '16 at 07:47
  • First, did you read the [many-to-many documentation](http://web2py.com/books/default/chapter/29/06/the-database-abstraction-layer#Many-to-many) recommended in response to your previous question? It shows exactly how to do this (much more efficiently than your approach above). Second, you should either require that the playlist title be unique, or use some other means to identify a playlist -- otherwise, using the approach above, if duplicate playlist titles are created, the duplicates will never be accessible. – Anthony Dec 06 '16 at 15:12
  • Tried it on my side, the logic of retrieving the data and building the tracks list works fine. Question : how are you calling the `get_playlist_tracks()` method and how are you passing the `request.vars.title` to it ? – zee Dec 07 '16 at 09:42
  • Have you tried changing the return statement as following : `return json(dict(tracks=tracks))` or `return json(tracks)` if you are passing to this to some javascript ? You will need to add an import statement in your script for this to work `from gluon.serializers import json` – zee Dec 07 '16 at 09:47

0 Answers0