In case it helps anyone, the SQL statements that are used in SQL
Playlist for this looks like this:
Pop, Rock music excluding Christmas music
Code:
--------------------
select tracks.url from tracks
join genre_track on
tracks.id=genre_track.track
join genres on
genre_track.genre=genres.id
where
audio=1
and genres.name in ('Pop','Rock')
and not exists (select * from tracks t2,genre_track,genres
where
t2.id=tracks.id and
tracks.id=genre_track.track and
genre_track.genre=genres.id and
genres.name in
('Christmas'))
group by tracks.id
order by rand()
limit 10;
--------------------
All Christmas music independent if it's rock or pop
Code:
--------------------
select tracks.url from tracks
join genre_track on
tracks.id=genre_track.track
join genres on
genre_track.genre=genres.id
where
audio=1
and genres.name in ('Christmas')
group by tracks.id
order by rand()
limit 10;
--------------------
All Christmas music except for Rock Christmas music.
Code:
--------------------
select tracks.url from tracks
join genre_track on
tracks.id=genre_track.track
join genres on
genre_track.genre=genres.id
where
audio=1
and genres.name in ('Christmas')
and not exists (select * from tracks t2,genre_track,genres
where
t2.id=tracks.id and
tracks.id=genre_track.track and
genre_track.genre=genres.id and
genres.name in ('Rock'))
group by tracks.id
order by rand()
limit 10;
--------------------
As far as I can remember I haven't had any complaints about performance
issues related to the "not exists" query I'm using at the moment, but it
might be worth to keep in mind that my plugins can often get other
performance problems on slow NAS boxes so people with slow hardware
might not be using my plugins.
Erland Isaksson ('My homepage' (http://erland.isaksson.info))
(Developer of 'many plugins/applets (both free and commercial)'
(http://wiki.slimdevices.com/index.php/User:Erland).
If you like to encourage future presence on this forum and/or third
party plugin/applet development, 'consider purchasing some plugins'
(http://license.isaksson.info))
You may also want to try my Android apps 'Squeeze Display'
(https://play.google.com/store/apps/details?id=info.isaksson.squeezedisplay)
and 'RSS Photo Show'
(https://play.google.com/store/apps/details?id=info.isaksson.rssphotoshow)
*Interested in the future of music streaming ? 'ickStream - A world of
music at your fingertips'
(http://forums.slimdevices.com/showthread.php?98467-Pre-Announcement-ickStream&p=743516)*.
------------------------------------------------------------------------
erland's Profile: http://forums.slimdevices.com/member.php?userid=3124
View this thread: http://forums.slimdevices.com/showthread.php?t=100327
_______________________________________________
beta mailing list
[email protected]
http://lists.slimdevices.com/mailman/listinfo/beta
