/**
* Counts number of bans for a version of the game for a specified champion
*
* @param string $version
* @param DLCompare\LoLApiBundle\Entity\Champion $champion
* @return integer
*/
public function countBansByVersionByChampion($version, Champion $champion)
{
$qb = $this->createQueryBuilder('g');
$ex = $qb->expr();
$result = $qb->select('COUNT(b)')->leftJoin('g.bans', 'b')->where($ex->like('g.version', $ex->literal($version . '%')))->andWhere($ex->eq('b.id', $champion->getId()))->getQuery()->getSingleScalarResult();
return $result;
}
/**
* Get usage of an item on for a champion, on a specified version
*
* @param DLCompare\LoLApiBundle\Entity\Champion $champion
* @param string $version
* @param integer $maxResults
* @return integer
*/
public function getUsage(Champion $champion, $version, Item $item)
{
$qb = $this->createQueryBuilder('i');
$ex = $qb->expr();
return $qb->select('COUNT(i)')->leftJoin('i.participants', 'p')->leftJoin('p.champion', 'c')->leftJoin('p.game', 'g')->where($ex->like('g.version', $ex->literal($version . '%')))->andWhere($ex->eq('c.id', $champion->getId()))->andWhere($ex->eq('i.id', $item->getId()))->getQuery()->getSingleScalarResult();
}
/**
* Get most common build of a champion based on game length and version
*
* @param DLCompare\LoLApiBundle\Entity\Champion $champion
* @param string $version
* @param integer $start
* @param integer $end
* @return array(DLCompare\LoLApiBundle\Entity\Item)
*/
public function getCommonBuild(Champion $champion, $version, $start = 40, $end = 1000)
{
$sql = "\n\t\t\tSELECT sub.id AS id, sub.build, COUNT(sub.id) AS cpt FROM\n\t\t\t(\n\t\t\t\tSELECT p.id , GROUP_CONCAT(i.item_id) AS build FROM participant p \n\t\t\t\tLEFT JOIN participant_items i ON i.participant_id = p.id \n\t\t\t\tLEFT JOIN game g ON p.game_id = g.id\n\t\t\t\tLEFT JOIN champion c ON p.champion_id = c.id\n\t\t\t\tWHERE c.id = " . $champion->getId() . "\n\t\t\t\t\tAND g.version LIKE '" . $version . "%'\n\t\t\t\t\tAND g.duration > " . $start * 60 . "\n\t\t\t\t\tAND g.duration < " . $end * 60 . "\n\t\t\t\tGROUP BY p.id \n\t\t\t\tORDER BY build DESC\n\t\t\t) AS sub\n\t\t\tGROUP BY sub.build\n\t\t\tORDER BY cpt DESC\n\t\t\tLIMIT 1\n\t\t";
$rsm = new ResultSetMapping();
$rsm->addScalarResult('id', 'id');
$query = $this->getEntityManager()->createNativeQuery($sql, $rsm);
$result = $query->getResult();
if (count($result) == 0) {
return new Participant();
}
return $this->findOneById($result[0]['id']);
}
