erland wrote:
> How do you integrate ?
> I you integrate by implementing getNextDynamicPlayListTracks I guess you
> are aware that you need to return an array of Slim::Schema::Track
> objects and not the string with the sql statement. You need to execute
> the sql yourself and execute each sql statement separately, you can
> probably steal some code from SQL Playlist plugin function
> executeSQLForPlaylist:
> https://github.com/erland/lms-sqlplaylist/blob/5955592540b5c157518edf61a731b5ca484c8813/src/Plugin.pm#L1052
> It splits the statement string into separate statements, one per row,
> and execute the statements and lookup Slim::Schema::Track objects by
> calling the getTracksForResult function also included in SQL Playlist
> code. You can probably remove a large part of the code since you
> probably don’t need to cover all variants SQL Playlist supports.
>
> If you don’t want to execute the sql yourself you should instead
> integrate with SQL Playlist plugin. I think SQL playlist separate
> statements by new line character so you can’t write a statement on
> multiple rows as you did in your forum post.
>
> The free form query in Database Query only supports a single statement
> if I remember correctly so that’s probably the reason that doesn't
> work.
I have no problem with executing the sql in RL, gives me greater
control.
Here's the -entire- function so far that gets me zero tracks for
playlist *2* (DPL in LMS webUI sometimes even complains about "no
songs") :
Code:
--------------------
sub getNextDynamicPlayListTracks {
my ($client,$playlist,$limit,$offset,$parameters) = @_;
my $clientID = $client->id;
my $DPLid = @$playlist{dynamicplaylistid};
my @result = ();
my ($items, $sql, $sth, $track);
my $dbh = getCurrentDBH();
# Playlist1: "Rated"
if ($DPLid eq 'ratingslight_rated') {
$sql = "select tracks.url from tracks
join tracks_persistent on
tracks.url=tracks_persistent.url and tracks_persistent.rating > 0
left join comments as excludecomments on
tracks.id=excludecomments.track and excludecomments.value like
'%%never%%'
left join dynamicplaylist_history on
tracks.id=dynamicplaylist_history.id and
dynamicplaylist_history.client='$clientID'
where
audio=1
and dynamicplaylist_history.id is null
and excludecomments.id is null
and tracks.secs>90
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
('Classical','Classical - Opera'))
group by tracks.id
order by random()
limit $limit;";
}
# Playlist2: "Rated with top percentage"
if ($DPLid eq 'ratingslight_rated-with_top_percentage') {
my $percentagerated = $parameters->{1}->{'value'};
$sql = "DROP TABLE IF EXISTS randomweightedratingshigh;
DROP TABLE IF EXISTS randomweightedratingslow;
DROP TABLE IF EXISTS randomweightedratingscombined;
create temporary table randomweightedratingslow as select tracks.url as url
from tracks
join tracks_persistent on
tracks.url=tracks_persistent.url and tracks_persistent.rating
<= 49
left join comments as excludecomments on
tracks.id=excludecomments.track and excludecomments.value like
'%%never%%'
left join dynamicplaylist_history on
tracks.id=dynamicplaylist_history.id and
dynamicplaylist_history.client='$clientID'
where
audio=1
and excludecomments.id is null
and tracks.secs>90
and dynamicplaylist_history.id is null
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
('Classical','Classical - Opera'))
order by random()
limit (100-$percentagerated);
create temporary table randomweightedratingshigh as select tracks.url as url
from tracks
join tracks_persistent on
tracks.url=tracks_persistent.url and tracks_persistent.rating >
49
left join comments as excludecomments on
tracks.id=excludecomments.track and excludecomments.value like
'%%never%%'
left join dynamicplaylist_history on
tracks.id=dynamicplaylist_history.id and
dynamicplaylist_history.client='$clientID'
where
audio=1
and excludecomments.id is null
and tracks.secs>90
and dynamicplaylist_history.id is null
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
('Classical','Classical - Opera'))
order by random()
limit $percentagerated;
create temporary table randomweightedratingscombined as
SELECT * FROM randomweightedratingslow
UNION
SELECT * from randomweightedratingshigh;
SELECT * from randomweightedratingscombined
ORDER BY random()
limit $limit;
DROP TABLE randomweightedratingshigh;
DROP TABLE randomweightedratingslow;
DROP TABLE randomweightedratingscombined;
";
}
$sth = $dbh->prepare($sql);
$sth->execute();
my $trackURL;
$sth->bind_col(1,\$trackURL);
my @trackURLs = ();
while( $sth->fetch()) {
$track =
Slim::Schema->resultset("Track")->objectForUrl($trackURL);
push @result,$track;
}
$sth->finish();
return \@result;
}
--------------------
It's based on 'this SQLplaylist'
(https://github.com/AF-1/lms-misc/blob/main/Rating%20playlist%20definitions/RATED__2-with_TOP_PERCENTAGE.sql.xml).
If I work this out I want to do 'the others'
(https://github.com/AF-1/lms-misc/tree/main/Rating%20playlist%20definitions)
as well.
What I can't understand is why DynamicPlaylist is fine with a custom sql
playlist definition when it comes via SQLplaylist but not when it's
supplied by my plugin. I must have made a mistake somewhere, maybe in
the bind_col? Or some syntax stuff?
As I said, playlist1 ("Rated") works just fine.
P.S. I had looked at the very TS code you've referenced - and wonderd
exactly the same thing, if there's another option besides subscribing. I
posted a question in dev forum, maybe somebody there knows something
about this...
------------------------------------------------------------------------
afriend's Profile: http://forums.slimdevices.com/member.php?userid=39306
View this thread: http://forums.slimdevices.com/showthread.php?t=113344
_______________________________________________
plugins mailing list
plugins@lists.slimdevices.com
http://lists.slimdevices.com/mailman/listinfo/plugins
