[mythtv-users] Re: [mythtv] [PATCH] Mythweb -- Mythmusic playlist
functionality AND Request for SQL help
rbsteffes at gmail.com
Thu Oct 28 13:04:48 UTC 2004
On Thu, 28 Oct 2004 08:02:39 -0400, Daniel C. Casimiro
<dcasimiro at cox.net> wrote:
> 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
> 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...
This is what I'm debating right now. Instead of dealing with the
issue directly, I just put in some sorting code for the moment. The
only thing I don't do right now is deal with a situation Thor brought
up that I didn't know about: Negative numbers allowing imbedded
My initial reaction is the amount of processing time to sort the list
is probably negligible for the vast majority of cases. I thought of a
halfway decent way to sort and stuck that in, although it may need
some tweaking to allow imbedded lists.
More information about the mythtv-users