/// <inheritdoc />
public async Task <List <MatchListing> > ReadMatchListings(MatchFilter filter, MatchSortOrder sortOrder)
{
filter = filter ?? new MatchFilter();
var cachePolicy = _policyRegistry.Get <IAsyncPolicy>(CacheConstants.MatchesPolicy);
var cacheKey = CacheConstants.MatchListingsCacheKeyPrefix + _matchFilterSerializer.Serialize(filter) + sortOrder.ToString();
return(await cachePolicy.ExecuteAsync(async context => await _matchListingDataSource.ReadMatchListings(filter, sortOrder), new Context(cacheKey)));
}
/// <summary>
/// Gets a list of matches and tournaments based on a query
/// </summary>
/// <returns>A list of <see cref="MatchListing"/> objects. An empty list if no matches or tournaments are found.</returns>
public async virtual Task <List <MatchListing> > ReadMatchListings(MatchFilter filter, MatchSortOrder sortOrder)
{
if (filter is null)
{
filter = new MatchFilter();
}
if (!filter.IncludeMatches && !filter.IncludeTournaments)
{
return(new List <MatchListing>());
}
if (ExcludeTournamentsDueToMatchTypeFilter(filter.MatchResultTypes))
{
filter.IncludeTournaments = false;
}
using (var connection = _databaseConnectionFactory.CreateDatabaseConnection())
{
var selectSql = new StringBuilder();
var whereSql = new StringBuilder();
var parameters = new Dictionary <string, object>();
var orderBy = new List <string>();
if (filter.IncludeMatches)
{
// Join to MatchInnings only happens if there's a batting team, because otherwise all you get from it is extra rows to process with just a MatchInningsId
var matchSelectSql = $@"SELECT m.MatchId, m.MatchName, m.MatchRoute, m.StartTime, m.StartTimeIsKnown, m.MatchType, m.PlayerType, m.PlayersPerTeam, m.MatchResultType,
NULL AS TournamentQualificationType, NULL AS SpacesInTournament, m.OrderInTournament,
(SELECT TOP 1 AuditDate FROM {Tables.Audit} WHERE EntityUri = CONCAT('{Constants.EntityUriPrefixes.Match}', m.MatchId) ORDER BY AuditDate ASC) AS FirstAuditDate,
(SELECT TOP 1 AuditDate FROM {Tables.Audit} WHERE EntityUri = CONCAT('{Constants.EntityUriPrefixes.Match}', m.MatchId) ORDER BY AuditDate DESC) AS LastAuditDate,
mt.TeamRole, mt.MatchTeamId,
mt.TeamId,
i.MatchInningsId, i.Runs, i.Wickets,
ml.MatchLocationId, ml.SecondaryAddressableObjectName, ml.PrimaryAddressableObjectName, ml.Locality, ml.Town, ml.Latitude, ml.Longitude
FROM { Tables.Match } AS m
LEFT JOIN {Tables.MatchTeam} AS mt ON m.MatchId = mt.MatchId
LEFT JOIN {Tables.MatchInnings} AS i ON m.MatchId = i.MatchId AND i.BattingMatchTeamId = mt.MatchTeamId
LEFT JOIN {Tables.MatchLocation} AS ml ON m.MatchLocationId = ml.MatchLocationId ";
selectSql.Append(matchSelectSql);
var(matchWhereSql, matchParameters) = BuildMatchQuery(filter,
$@"SELECT m.MatchId, m.OrderInTournament, m.StartTime
FROM {Tables.Match} AS m
<<JOIN>>
<<WHERE>> ");
whereSql.Append(matchWhereSql);
parameters = matchParameters;
if (filter.TournamentId != null)
{
orderBy.Add("OrderInTournament");
}
}
if (filter.IncludeMatches && filter.IncludeTournaments)
{
selectSql.Append(" UNION ");
whereSql.Append(" UNION ");
}
if (filter.IncludeTournaments)
{
var tournamentSelectSql = $@"SELECT tourney.TournamentId AS MatchId, tourney.TournamentName AS MatchName, tourney.TournamentRoute AS MatchRoute, tourney.StartTime, tourney.StartTimeIsKnown,
NULL AS MatchType, tourney.PlayerType, tourney.PlayersPerTeam, NULL AS MatchResultType,
tourney.QualificationType AS TournamentQualificationType, tourney.SpacesInTournament, NULL AS OrderInTournament,
(SELECT TOP 1 AuditDate FROM {Tables.Audit} WHERE EntityUri = CONCAT('{Constants.EntityUriPrefixes.Tournament}', tourney.TournamentId) ORDER BY AuditDate ASC) AS FirstAuditDate,
(SELECT TOP 1 AuditDate FROM {Tables.Audit} WHERE EntityUri = CONCAT('{Constants.EntityUriPrefixes.Tournament}', tourney.TournamentId) ORDER BY AuditDate DESC) AS LastAuditDate,
NULL AS TeamRole, NULL AS MatchTeamId,
NULL AS TeamId,
NULL AS MatchInningsId, NULL AS Runs, NULL AS Wickets,
ml.MatchLocationId, ml.SecondaryAddressableObjectName, ml.PrimaryAddressableObjectName, ml.Locality, ml.Town, ml.Latitude, ml.Longitude
FROM { Tables.Tournament} AS tourney
LEFT JOIN {Tables.MatchLocation} AS ml ON tourney.MatchLocationId = ml.MatchLocationId ";
selectSql.Append(tournamentSelectSql);
var(tournamentWhereSql, tournamentParameters) = BuildTournamentQuery(filter,
$@"SELECT tourney.TournamentId AS MatchId, NULL AS OrderInTournament, tourney.StartTime
FROM {Tables.Tournament} AS tourney
<<JOIN>>
<<WHERE>> ");
whereSql.Append(tournamentWhereSql);
foreach (var key in tournamentParameters.Keys)
{
if (!parameters.ContainsKey(key))
{
parameters.Add(key, tournamentParameters[key]);
}
}
}
if (sortOrder == MatchSortOrder.MatchDateEarliestFirst)
{
orderBy.Add("StartTime");
}
else if (sortOrder == MatchSortOrder.LatestUpdateFirst)
{
orderBy.Add("LastAuditDate DESC");
}
var pagedSql = $@"SELECT * FROM ({selectSql}) AS UnfilteredResults
WHERE MatchId IN
(
SELECT MatchId FROM ({whereSql}) AS MatchingRecords
ORDER BY {string.Join(", ", orderBy.ToArray())}
OFFSET @PageOffset ROWS FETCH NEXT @PageSize ROWS ONLY
)
ORDER BY {string.Join(", ", orderBy.ToArray())}";
parameters.Add("@PageOffset", (filter.Paging.PageNumber - 1) * filter.Paging.PageSize);
parameters.Add("@PageSize", filter.Paging.PageSize);
var matches = await connection.QueryAsync <MatchListing, TeamInMatch, Team, MatchInnings, MatchLocation, MatchListing>(pagedSql,
(matchListing, teamInMatch, team, matchInnings, location) =>
{
if (teamInMatch != null)
{
teamInMatch.Team = team;
matchListing.Teams.Add(teamInMatch);
if (matchInnings != null)
{
matchInnings.BattingMatchTeamId = teamInMatch.MatchTeamId;
matchInnings.BattingTeam = teamInMatch;
}
;
}
if (matchInnings != null)
{
matchListing.MatchInnings.Add(matchInnings);
}
matchListing.MatchLocation = location;
return(matchListing);
},
new DynamicParameters(parameters),
splitOn : "TeamRole, TeamId, MatchInningsId, MatchLocationId").ConfigureAwait(false);
var listingsToReturn = matches.GroupBy(match => match.MatchRoute).Select(copiesOfMatch =>
{
var matchToReturn = copiesOfMatch.First();
matchToReturn.MatchInnings = copiesOfMatch.Select(match => match.MatchInnings.SingleOrDefault()).OfType <MatchInnings>().ToList();
matchToReturn.Teams = copiesOfMatch.Select(match => match.Teams.SingleOrDefault()).OfType <TeamInMatch>().Distinct(new TeamInMatchEqualityComparer()).ToList();
return(matchToReturn);
}).ToList();
return(listingsToReturn);
}
}