public async Task <SetlistSongWithShows> ForIdWithShows(Artist artist, int id)
{
SetlistSongWithShows bigSong = null;
await db.WithConnection(con => con.QueryAsync <SetlistSongWithShows, Show, VenueWithShowCount, Tour, Era, Year, SetlistSongWithShows>(@"
SELECT
s.*
, shows.*
, cnt.max_updated_at as most_recent_source_updated_at
, cnt.source_count
, cnt.has_soundboard_source
, v.*, t.*, e.*, y.*
FROM
setlist_songs s
LEFT JOIN setlist_songs_plays p ON p.played_setlist_song_id = s.id
LEFT JOIN setlist_shows set_shows ON set_shows.id = p.played_setlist_show_id
JOIN shows shows ON shows.date = set_shows.date AND shows.artist_id = @artistId
LEFT JOIN venues v ON shows.venue_id = v.id
LEFT JOIN tours t ON shows.tour_id = t.id
LEFT JOIN eras e ON shows.era_id = e.id
LEFT JOIN years y ON shows.year_id = y.id
INNER JOIN (
SELECT
src.show_id,
MAX(src.updated_at) as max_updated_at,
COUNT(*) as source_count,
BOOL_OR(src.is_soundboard) as has_soundboard_source
FROM
sources src
GROUP BY
src.show_id
) cnt ON cnt.show_id = shows.id
WHERE
s.artist_id = @artistId
AND s.id = @songId
ORDER BY shows.date
",
(song, show, venue, tour, era, year) => {
if (bigSong == null)
{
bigSong = song;
bigSong.shows = new List <Show>();
}
show.venue = venue;
show.tour = tour;
show.era = era;
show.year = year;
bigSong.shows.Add(show);
return(song);
},
new { artistId = artist.id, songId = id }));
return(bigSong);
}
public async Task <SetlistSongWithShows> ForIdWithShows(Artist artist, int id)
{
SetlistSongWithShows bigSong = null;
await db.WithConnection(con => con.QueryAsync <SetlistSongWithShows, Show, Venue, Tour, Era, SetlistSongWithShows>(@"
SELECT
s.*, shows.*, v.*, t.*
FROM
setlist_songs s
LEFT JOIN setlist_songs_plays p ON p.played_setlist_song_id = s.id
LEFT JOIN setlist_shows set_shows ON set_shows.id = p.played_setlist_show_id
JOIN shows shows ON shows.date = set_shows.date AND shows.artist_id = @artistId
LEFT JOIN venues v ON shows.venue_id = v.id
LEFT JOIN tours t ON shows.tour_id = t.id
LEFT JOIN eras e ON shows.era_id = e.id
WHERE
s.artist_id = @artistId
AND s.id = @songId
ORDER BY shows.date
",
(song, show, venue, tour, era) => {
if (bigSong == null)
{
bigSong = song;
bigSong.shows = new List <Show>();
}
show.venue = venue;
show.tour = tour;
show.era = era;
bigSong.shows.Add(show);
return(song);
},
new { artistId = artist.id, songId = id }));
return(bigSong);
}
