[mythtv] [PATCH] Mythweb -- Mythmusic playlist functionality AND Request for SQL help

[Daniel C. Casimiro]

I can't look at my myth database right now because my myth box is down.  
(Blew out 4 capacitors)

I assume that you are trying to sort against the playlist table entry 
that lists the song ids.   I can't think of any automatic way to order 
on this string.


However, you could re-arrange the tables slightly.

First, make the playlist table simpler.  The two important fields would 
be playlist id and name.

Next, make a new table called "playlistitems" or something similar.  
This table would link the songs to their respective playlists.  This new 
table would have at least 3 fields: Song ID, Playlist ID, Playlist 
Position(track).

Now, by using SQL joins, you can grab a playlist's songs by supplying 
the playlist name, and order by the Playlist Position.

For example, the MySQL query string to select all songs in playlist 
"PlaylistA" would be:

string name = "PlaylistA";
string req = "SELECT 
musicmetadata.artist,musicmetadata.title,musicmetadata.filename ";
req += "FROM musicmetadata ";
req += "LEFT JOIN musicplaylistitem ON ";
req += "musicmetadata.intid=musicplaylistitem.songid ";
req += "LEFT JOIN musicplaylist USING(playlistid) ";
req += "WHERE musicplaylist.name='" + name + "'";
req += "ORDER BY musicplaylistitem.track;";

Or, at least something similar.

Of course, the downside of this is that you need to recode some parts of 
mythmusic to use the new table schema.
Just a suggestion...

~Dan

Ryan Steffes wrote:

>http://www.afferentsys.com/~rbsteffes/patch-mythmusic.bz2
>
>This patch adds to the functionality I've been working on to add
>playlist manipulation to mythweb.  Previously added was the ability to
>create/rename/add to/remove from a playlist.  I've added reordering
>playlists.
>
>That brings me to the second part of the request.  If anyone out there
>is particularly good at SQL I'm looking for a way to sort a select
>statement against a known string.  The reason for this is because the
>way mythweb gets track names is efficient from a query standpoint, it
>gets them in the order of the intid key for the tracks in the db not
>the order they are listed in the playlist.  It's because it retrieves
>as where intid is in ( " playlist ") essentially.  Is there a way I
>can have it sort against the playlist before returning the rows? 
>That'd make playlist reordering look a lot better.
>
>Right now, while it works, it doesn't appear to because the tracks
>show up in database order, not playlist order.  If there isn't a good
>way, there are work around fixes but they'd require more active
>sorting and could get ugly fast.
>
>Thanks
>
>Ryan
>  
>
>------------------------------------------------------------------------
>
>_______________________________________________
>mythtv-dev mailing list
>mythtv-dev at mythtv.org
>http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-dev
>  
>