/** * 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']); }