public async Task <IEnumerable <SourceFull> > FullSourcesForShow(Artist artist, Show show)
{
var LinkMapper = new EntityOneToManyMapper <SourceFull, Link, int>()
{
AddChildAction = (source, link) =>
{
if (source.links == null)
{
source.links = new List <Link>();
}
if (link != null)
{
source.links.Add(link);
}
},
ParentKey = (source) => source.id
};
var TrackMapper = new EntityOneToManyMapper <SourceSet, SourceTrack, int>()
{
AddChildAction = (set, track) =>
{
if (set.tracks == null)
{
set.tracks = new List <SourceTrack>();
}
if (track != null)
{
set.tracks.Add(track);
}
},
ParentKey = (set) => set.id
};
return(await db.WithConnection(async con => {
var t_srcsWithReviews = await con.QueryAsync <SourceFull, Link, SourceFull>(@"
SELECT
s.*
, COALESCE(review_counts.source_review_count, 0) as review_count
, l.*
FROM
sources s
LEFT JOIN links l ON l.source_id = s.id
LEFT JOIN source_review_counts review_counts ON review_counts.source_id = s.id
WHERE
s.artist_id = @artistId
AND s.show_id = @showId
ORDER BY
s.avg_rating_weighted DESC
" ,
LinkMapper.Map,
new { showId = show.id, artistId = artist.id }
);
var t_setsWithTracks = await con.QueryAsync <SourceSet, SourceTrack, SourceSet>(@"
SELECT
s.*, t.*
FROM
source_sets s
LEFT JOIN sources src ON src.id = s.source_id
LEFT JOIN source_tracks t ON t.source_set_id = s.id
WHERE
src.show_id = @showId
ORDER BY
s.index ASC, t.track_position ASC
" ,
TrackMapper.Map,
new { showId = show.id }
);
var setsWithTracks = t_setsWithTracks
.Where(s => s != null)
.GroupBy(s => s.source_id)
.ToDictionary(grp => grp.Key, grp => grp.AsList())
;
var sources = t_srcsWithReviews
.Where(s => s != null)
.ToList();
foreach (var src in sources)
{
src.sets = setsWithTracks[src.id];
}
return sources;
}));
}
public async Task <IEnumerable <SourceFull> > FullSourcesForShow(Artist artist, Show show)
{
var ReviewMapper = new EntityOneToManyMapper <SourceFull, SourceReview, int>()
{
AddChildAction = (source, review) =>
{
if (source.reviews == null)
{
source.reviews = new List <SourceReview>();
}
if (review != null)
{
source.reviews.Add(review);
}
},
ParentKey = (source) => source.id
};
var TrackMapper = new EntityOneToManyMapper <SourceSet, SourceTrack, int>()
{
AddChildAction = (set, track) =>
{
if (set.tracks == null)
{
set.tracks = new List <SourceTrack>();
}
if (track != null)
{
set.tracks.Add(track);
}
},
ParentKey = (set) => set.id
};
var t_srcsWithReviews = db.WithConnection(con => con.QueryAsync <SourceFull, SourceReview, SourceFull>(@"
SELECT
s.*, r.*
FROM
sources s
LEFT JOIN source_reviews r ON r.source_id = s.id
WHERE
s.artist_id = @artistId
AND s.show_id = @showId
",
ReviewMapper.Map,
new { showId = show.id, artistId = artist.id })
);
var t_setsWithTracks = db.WithConnection(con => con.QueryAsync <SourceSet, SourceTrack, SourceSet>(@"
SELECT
s.*, t.*
FROM
source_sets s
LEFT JOIN sources src ON src.id = s.source_id
LEFT JOIN source_tracks t ON t.source_set_id = s.id
WHERE
src.show_id = @showId
",
TrackMapper.Map,
new { showId = show.id })
);
await Task.WhenAll(t_srcsWithReviews, t_setsWithTracks);
var srcsWithReviews = t_srcsWithReviews.Result
.Where(s => s != null)
;
var setsWithTracks = t_setsWithTracks.Result
.Where(s => s != null)
.GroupBy(s => s.source_id)
.ToDictionary(grp => grp.Key, grp => grp.AsList())
;
foreach (var src in srcsWithReviews)
{
src.sets = setsWithTracks[src.id];
}
return(srcsWithReviews);
}