List<AggregatedChampionStatistics> LoadAggregatedChampionStatistics(Summoner summoner, MapType map, GameModeType gameMode, NpgsqlConnection database) { const string query = "with source as " + "(select player.champion_id, player.won, player.kills, player.deaths, player.assists, player.gold, player.minion_kills from game_result, player where game_result.map = cast(:map as map_type) and game_result.game_mode = cast(:game_mode as game_mode_type) and (game_result.team1_id = player.team_id or game_result.team2_id = player.team_id) and player.summoner_id = :summoner_id) " + "select statistics.champion_id, coalesce(champion_wins.wins, 0) as wins, coalesce(champion_losses.losses, 0) as losses, statistics.kills, statistics.deaths, statistics.assists, statistics.gold, statistics.minion_kills from " + "(select source.champion_id, sum(source.kills) as kills, sum(source.deaths) as deaths, sum(source.assists) as assists, sum(source.gold) as gold, sum(source.minion_kills) as minion_kills from source group by source.champion_id) " + "as statistics " + "left outer join " + "(select champion_id, count(*) as wins from source where won = true group by champion_id) " + "as champion_wins " + "on statistics.champion_id = champion_wins.champion_id " + "left outer join " + "(select champion_id, count(*) as losses from source where won = false group by champion_id) " + "as champion_losses " + "on statistics.champion_id = champion_losses.champion_id;"; DatabaseCommand select = GetCommand(query, database); select.SetEnum("map", map.ToEnumString()); select.SetEnum("game_mode", gameMode.ToEnumString()); select.Set("summoner_id", summoner.Id); using (NpgsqlDataReader reader = select.ExecuteReader()) { List<AggregatedChampionStatistics> output = new List<AggregatedChampionStatistics>(); while (reader.Read()) { AggregatedChampionStatistics statistics = new AggregatedChampionStatistics(reader); statistics.ChampionName = GetChampionName(statistics.ChampionId); output.Add(statistics); } output.Sort(); return output; } }
List<AggregatedChampionStatistics> LoadAggregatedChampionStatistics(Summoner summoner, MapType map, GameModeType gameMode, DbConnection connection) { string viewName = GetViewName(); try { //Create a temporary view with a dynamically generated name to emulate the former CTE string createViewQuery = "create temporary view {0} as select game.map, game.game_mode, game.blue_team_id, game.purple_team_id, game.blue_team_won, player.team_id, player.summoner_id, player.champion_id, player.kills, player.deaths, player.assists, player.gold, player.minion_kills from game, player where game.blue_team_id = player.team_id or game.purple_team_id = player.team_id"; using (var createView = Command(createViewQuery, connection, viewName)) { createView.Execute(); string commonWhereClause = string.Format("{0}.summoner_id = :summoner_id and {0}.map = :map and {0}.game_mode = :game_mode", viewName); string selectQuery = "select statistics.champion_id, coalesce(champion_wins.wins, 0) as wins, coalesce(champion_losses.losses, 0) as losses, statistics.kills, statistics.deaths, statistics.assists, statistics.gold, statistics.minion_kills from " + "(select {0}.champion_id, sum({0}.kills) as kills, sum({0}.deaths) as deaths, sum({0}.assists) as assists, sum({0}.gold) as gold, sum({0}.minion_kills) as minion_kills from {0} where {1} group by {0}.champion_id) " + "as statistics " + "left outer join " + "(select champion_id, count(*) as wins from {0} where {1} and ((blue_team_won = 1 and blue_team_id = team_id) or (blue_team_won = 0 and purple_team_id = team_id)) group by champion_id) " + "as champion_wins " + "on statistics.champion_id = champion_wins.champion_id " + "left outer join " + "(select champion_id, count(*) as losses from {0} where {1} and ((blue_team_won = 0 and blue_team_id = team_id) or (blue_team_won = 1 and purple_team_id = team_id)) group by champion_id) " + "as champion_losses " + "on statistics.champion_id = champion_losses.champion_id"; using (var select = Command(selectQuery, connection, viewName, commonWhereClause)) { select.Set("map", map); select.Set("game_mode", gameMode); select.Set("summoner_id", summoner.Id); using (var reader = select.ExecuteReader()) { List<AggregatedChampionStatistics> output = new List<AggregatedChampionStatistics>(); while (reader.Read()) { AggregatedChampionStatistics statistics = new AggregatedChampionStatistics(reader); output.Add(statistics); } using (var dropView = Command("drop view {0}", connection, viewName)) dropView.Execute(); return output; } } } } finally { ReleaseViewName(viewName); } }
List<AggregatedChampionStatistics> LoadAggregatedChampionStatistics(Summoner summoner, MapType map, GameModeType gameMode, DbConnection database) { const string query = "with source as " + "(select team_player.champion_id, team_player.won, team_player.kills, team_player.deaths, team_player.assists, team_player.gold, team_player.minion_kills from game_result, team_player where game_result.result_map = cast(:result_map as map_type) and game_result.game_mode = cast(:game_mode as game_mode_type) and (game_result.team1_id = team_player.team_id or game_result.team2_id = team_player.team_id) and team_player.summoner_id = :summoner_id) " + "select statistics.champion_id, coalesce(champion_wins.wins, 0) as wins, coalesce(champion_losses.losses, 0) as losses, statistics.kills, statistics.deaths, statistics.assists, statistics.gold, statistics.minion_kills from " + "(select source.champion_id, sum(source.kills) as kills, sum(source.deaths) as deaths, sum(source.assists) as assists, sum(source.gold) as gold, sum(source.minion_kills) as minion_kills from source group by source.champion_id) " + "as statistics " + "left outer join " + "(select champion_id, count(*) as wins from source where won = true group by champion_id) " + "as champion_wins " + "on statistics.champion_id = champion_wins.champion_id " + "left outer join " + "(select champion_id, count(*) as losses from source where won = false group by champion_id) " + "as champion_losses " + "on statistics.champion_id = champion_losses.champion_id;", myquery = @"SELECT statistics.champion_id, coalesce(champion_wins.wins, 0) as wins, coalesce(champion_losses.losses, 0) as losses, statistics.kills, statistics.deaths, statistics.assists, statistics.gold, statistics.minion_kills FROM ( SELECT source.champion_id, sum(source.kills) as kills, sum(source.deaths) as deaths, sum(source.assists) as assists, sum(source.gold) as gold, sum(source.minion_kills) as minion_kills FROM source WHERE source.result_map = ?result_map AND source.game_mode = ?game_mode AND source.summoner_id = ?summoner_id GROUP BY source.champion_id ) AS statistics LEFT OUTER JOIN ( SELECT champion_id, count(*) as wins FROM source WHERE source.won = true AND source.result_map = ?result_map AND source.game_mode = ?game_mode AND source.summoner_id = ?summoner_id GROUP BY champion_id ) champion_wins ON statistics.champion_id = champion_wins.champion_id LEFT OUTER JOIN ( SELECT champion_id, count(*) as losses FROM source WHERE source.won = false AND source.result_map = ?result_map AND source.game_mode = ?game_mode AND source.summoner_id = ?summoner_id GROUP BY champion_id ) champion_losses ON statistics.champion_id = champion_losses.champion_id;"; SQLCommand select; if (database is MySql.Data.MySqlClient.MySqlConnection) select = GetCommand(myquery, database); else select = GetCommand(query, database); select.SetEnum("result_map", map.ToEnumString()); select.SetEnum("game_mode", gameMode.ToEnumString()); select.Set("summoner_id", summoner.Id); using (DbDataReader reader = select.ExecuteReader()) { List<AggregatedChampionStatistics> output = new List<AggregatedChampionStatistics>(); while (reader.Read()) { AggregatedChampionStatistics statistics = new AggregatedChampionStatistics(reader); statistics.ChampionName = GetChampionName(statistics.ChampionId); output.Add(statistics); } output.Sort(); return output; } }