public override async Task <Track> LastPlayedTrack(int userId)
{
var sql = @"SELECT t.*
FROM `usertrack` ut
join `track` t on (ut.trackId = t.id)
WHERE ut.userId = {0}
ORDER by ut.lastPlayed desc
LIMIT 1";
return(await Tracks.FromSqlRaw(sql, userId)
.Include(x => x.TrackArtist)
.Include(x => x.ReleaseMedia)
.Include("ReleaseMedia.Release")
.Include("ReleaseMedia.Release.Artist")
.FirstOrDefaultAsync());
}
public override async Task <SortedDictionary <int, int> > RandomTrackIds(int userId, int randomLimit, bool doOnlyFavorites = false, bool doOnlyRated = false)
{
var sql = @"SELECT t.id
FROM `track` t
# Rated filter
WHERE ((t.rating > 0 AND {3} = 1) OR {3} = 0)
# Artist and TrackArtist is not disliked
AND ((t.id NOT IN (select tt.id
FROM `track` tt
JOIN `releasemedia` rm on (tt.releaseMediaId = rm.id)
JOIN `release` r on (rm.releaseId = r.id)
JOIN `userartist` ua on (r.artistId = ua.artistId)
WHERE ua.userId = {1} AND ua.isDisliked = 1
UNION
select tt.id
FROM `track` tt
JOIN `userartist` ua on (tt.artistId = ua.artistId)
WHERE ua.userId = {1} AND ua.isDisliked = 1))
# Release is not disliked
AND (t.id NOT IN (select tt.id
FROM `track` tt
JOIN `releasemedia` rm on (tt.releaseMediaId = rm.id)
JOIN `userrelease` ur on (rm.releaseId = ur.releaseId)
WHERE ur.userId = {1} AND ur.isDisliked = 1))
# Track is not disliked
AND (t.id NOT IN (select tt.id
FROM `track` tt
JOIN `usertrack` ut on (tt.id = ut.trackId)
WHERE ut.userId = {1} AND ut.isDisliked = 1)))
# If toggled then only favorites
AND ((t.id IN (select tt.id
FROM `track` tt
JOIN `usertrack` ut on (tt.id = ut.trackId)
WHERE ut.userId = {1} AND ut.isFavorite = 1) AND {2} = 1) OR {2} = 0)
order BY RIGHT( HEX( (1<<24) * (1+RAND()) ), 6)
LIMIT 0, {0}";
var ids = await Tracks.FromSqlRaw(sql, randomLimit, userId, doOnlyFavorites? "1" : "0", doOnlyRated? "1" : "0").Select(x => x.Id).ToListAsync();
var dict = ids.Select((id, i) => new { key = i, value = id }).ToDictionary(x => x.key, x => x.value);
return(new SortedDictionary <int, int>(dict));
}