RobbH wrote:
> As I have said, I do not know SQL. The syntax used to invoke queries is
> a mystery to me. However, the structure of nested "AND" and "OR" filters
> appears to be very familiar and similar to other software I have used. I
> realize that appearance can be misleading, so I hope someone who knows
> SQL will offer some feedback.
>
> This appears to be the relevant part of the Advanced Search code (as
> reported in the log) that determines the selection of songs with genres
> "not in text search":
>
> >
Code:
--------------------
> > WHERE ( ( genre NOT IN ( SELECT me.id FROM genres me WHERE ( (
me.namesearch LIKE ? OR me.namesearch LIKE ? ) ) ) ....
--------------------
> >
>
> I am assuming that the question marks are placeholders for values that
> are read from fields appended to the end of the line, in this case:
> '%ROCK%', '%JAZZ%'.
>
> And I would expect that "genre NOT IN" would reject files that only
> have Rock or Jazz as the genre tag, but would include any file that
> contains any other genre tag, even if there is also a Rock or Jazz
> tag.
>
> Looking at the SQL code generated by the SQL Playlists plugin, I see
> this:
>
> >
Code:
--------------------
> > 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
('Jazz','Rock'))
--------------------
> >
>
> Again, I'm only guessing, but "not exists" sounds much more likely to
> exclude any file with a Rock or Jazz genre tag, even if other genre
> tags are present.
>
> How about it, SQL experts? Am I on the right track here? Does this
> explain why Advanced Search does not behave the way I expected it to?
Think they both will do the same, i.e. include Asleep At The Wheel and
Russo.
A change to the existing query and a further subquery may be required to
do what you wish, e.g:
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 not in
('Jazz','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
('Jazz','Rock')) )
Thinking of Russo as 2 entities, i.e. Russo Rock & Russo Classical, then
the 1st select (exists) will exclude Rock but include Classical but then
the 2nd select (not exists) will exclude Classical because it will find
a Rock entity exists (if Ive got my thinking right).
------------------------------------------------------------------------
skind56's Profile: http://forums.slimdevices.com/member.php?userid=9792
View this thread: http://forums.slimdevices.com/showthread.php?t=112872
_______________________________________________
Squeezecenter mailing list
[email protected]
http://lists.slimdevices.com/mailman/listinfo/squeezecenter
