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)); }