joining on the full PK of albums table avoids multiple matches
and erroneous extra results
$self->update_db;
my @result;
$self->update_db;
my @result;
- my $sth = $db->prepare_cached(<<SQL);
SELECT s.path, s.artist, s.album
FROM songs s
JOIN artists ar ON ar.artist=s.artist
SELECT s.path, s.artist, s.album
FROM songs s
JOIN artists ar ON ar.artist=s.artist
-JOIN albums al ON al.album=s.album
+JOIN albums al ON al.album=s.album AND al.artist=s.artist
WHERE (s.last_queued IS NULL OR s.last_queued < CURRENT_TIMESTAMP - (? || ' seconds')::interval)
AND (ar.last_queued IS NULL OR ar.last_queued < CURRENT_TIMESTAMP - (? || ' seconds')::interval)
AND (al.last_queued IS NULL OR al.last_queued < CURRENT_TIMESTAMP - (? || ' seconds')::interval)
WHERE (s.last_queued IS NULL OR s.last_queued < CURRENT_TIMESTAMP - (? || ' seconds')::interval)
AND (ar.last_queued IS NULL OR ar.last_queued < CURRENT_TIMESTAMP - (? || ' seconds')::interval)
AND (al.last_queued IS NULL OR al.last_queued < CURRENT_TIMESTAMP - (? || ' seconds')::interval)
ORDER BY random()
LIMIT ?
SQL
ORDER BY random()
LIMIT ?
SQL
- $sth->execute(
- $opt->min_song_interval,
- $opt->min_artist_interval,
- $opt->min_album_interval,
- $num,
+ my @params = (
+ $opt->min_song_interval, $opt->min_artist_interval,
+ $opt->min_album_interval, $num,
+ my $sth = $db->prepare_cached($sql);
+ $sth->execute(@params);
while ( my @row = $sth->fetchrow_array ) {
push @result,
{ song => $row[0], artist => $row[1], album => $row[2] };
while ( my @row = $sth->fetchrow_array ) {
push @result,
{ song => $row[0], artist => $row[1], album => $row[2] };