Potrzebuje takie zapytanie zrobić w repozytorium Games, ale czas mnie nie puszcza żeby to ogarnąć biorąc pod uwagę iż nie jestem jeszcze wymiataczem w SQL, a co dopiero przerobienie tego zapytania na createQuery (nie wspominając o createQueryBuilder, chyba nawet nie obsługuje niektórych funkcji które są użyte w zapytaniu).
Zapytanie umieściłem poniżej, oczywiście sam go nie stworzyłem, jedynie przerobiłem na własne potrzeby. Służy ono do wygenerowania tabeli tournamentu, Dane pobierane są z dwóch Entities/Tabel. Pierwsza z nich to tabela Games, a druga Users. Poniżej zapytania wklejam również wsyzstkie Entities, które w tym przypadku mogą być przydatne.
Bardzo proszę o pomoc. Na tym przykładzie chciałbym się czegoś nauczyć w temacie SQL/DQL, ale niestety mam deadline apki, a moje wiedza w tej kwestii jest skromna ponieważ jestem stażystą.
ZAPYTANIE SQL
SELECT
username AS Team, Sum(P) AS P,Sum(W) AS W,Sum(D) AS D,Sum(L) AS L,
SUM(F) as F,SUM(A) AS A,SUM(GD) AS GD,SUM(Pts) AS Pts
FROM(
SELECT
hteam_id Team,
CASE WHEN hteam_score is not null then 1 else 0 end P,
IF(hteam_score > ateam_score,1,0) W,
IF(hteam_score = ateam_score,1,0) D,
IF(hteam_score < ateam_score,1,0) L,
hteam_score F,
ateam_score A,
hteam_score-ateam_score GD,
CASE WHEN hteam_score > ateam_score THEN 3 WHEN hteam_score = ateam_score THEN 1 ELSE 0 END PTS
FROM games
where tournaments_id = 45
UNION ALL
SELECT
ateam_id,
CASE WHEN ateam_score is not null then 1 else 0 end P,
IF(hteam_score < ateam_score,1,0),
IF(hteam_score = ateam_score,1,0),
IF(hteam_score > ateam_score,1,0),
ateam_score,
hteam_score,
ateam_score-hteam_score GD,
CASE WHEN hteam_score < ateam_score THEN 3 WHEN hteam_score = ateam_score THEN 1 ELSE 0 END
FROM games
where tournaments_id = 45
) as tot
JOIN users t ON tot.Team=t.id
GROUP BY Team
ORDER BY SUM(Pts) DESC, SUM(GD) DESC, SUM(F) DESC;
Klasa Games
<?php
namespace AppBundle\Entity;
use Doctrine\ORM\Mapping as ORM;
/**
* Games
*
* @ORM\Table(name="games", indexes={@ORM\Index(name="fk_games_users1_idx", columns={"hteam_id"}), @ORM\Index(name="fk_games_users2_idx", columns={"ateam_id"}), @ORM\Index(name="fk_games_tournaments1_idx", columns={"tournaments_id"})})
* @ORM\Entity
*
* @ORM\Entity(repositoryClass="AppBundle\Repository\GamesRepository")
*/
class Games
{
/**
* @var integer
*
* @ORM\Column(name="hteam_score", type="integer", nullable=true)
*/
private $hteamScore;
/**
* @var integer
*
* @ORM\Column(name="ateam_score", type="integer", nullable=true)
*/
private $ateamScore;
/**
* @var integer
*
* @ORM\Column(name="id", type="integer")
* @ORM\Id
* @ORM\GeneratedValue(strategy="IDENTITY")
*/
private $id;
/**
* @var \AppBundle\Entity\Users
*
* @ORM\ManyToOne(targetEntity="AppBundle\Entity\Users")
* @ORM\JoinColumns({
* @ORM\JoinColumn(name="ateam_id", referencedColumnName="id")
* })
*/
private $ateam;
/**
* @var \AppBundle\Entity\Users
*
* @ORM\ManyToOne(targetEntity="AppBundle\Entity\Users")
* @ORM\JoinColumns({
* @ORM\JoinColumn(name="hteam_id", referencedColumnName="id")
* })
*/
private $hteam;
/**
* @var \AppBundle\Entity\Tournaments
*
* @ORM\ManyToOne(targetEntity="AppBundle\Entity\Tournaments")
* @ORM\JoinColumns({
* @ORM\JoinColumn(name="tournaments_id", referencedColumnName="id")
* })
*/
private $tournaments;
Klasa Users, która korzysta z FOSBundle Symfony3
<?php
namespace AppBundle\Entity;
use Doctrine\ORM\Mapping as ORM;
use FOS\UserBundle\Model\User as BaseUser;
/**
* Users
*
* @ORM\Table(name="users")
* @ORM\Entity
*/
class Users extends BaseUser
{
/**
* @ORM\Id
* @ORM\Column(type="integer")
* @ORM\GeneratedValue(strategy="AUTO")
*/
protected $id;
/**
* @ORM\ManyToMany(targetEntity="AppBundle\Entity\Tournaments", mappedBy="users", cascade={"remove", "persist"}))
*/
protected $tournamentUsers;
public function __construct()
{
parent::__construct();
// your own logic
}
/**
* Add tournamentUser
*
* @param \AppBundle\Entity\Tournaments $tournamentUser
*
* @return Users
*/
public function addTournamentUser(\AppBundle\Entity\Tournaments $tournamentUser)
{
$this->tournamentUsers[] = $tournamentUser;
return $this;
}
/**
* Remove tournamentUser
*
* @param \AppBundle\Entity\Tournaments $tournamentUser
*/
public function removeTournamentUser(\AppBundle\Entity\Tournaments $tournamentUser)
{
$this->tournamentUsers->removeElement($tournamentUser);
}
/**
* Get tournamentUsers
*
* @return \Doctrine\Common\Collections\Collection
*/
public function getTournamentUsers()
{
return $this->tournamentUsers;
}
}
I na wszelki wypadek klasa Tournamnets
<?php
namespace AppBundle\Entity;
use Doctrine\ORM\Mapping as ORM;
/**
* Tournaments
*
* @ORM\Table(name="tournaments", uniqueConstraints={@ORM\UniqueConstraint(name="name_UNIQUE", columns={"name"})}, indexes={@ORM\Index(name="fk_tournaments_users1_idx", columns={"owner_id"})})
* @ORM\Entity
*
*
* @ORM\Entity(repositoryClass="AppBundle\Repository\TournamentsRepository")
*
*/
class Tournaments
{
/**
* @var string
*
* @ORM\Column(name="name", type="string", length=45, nullable=false)
*/
private $name;
/**
* @var \DateTime
*
* @ORM\Column(name="create_date", type="datetime", nullable=false)
*/
private $createDate;
/**
* @var integer
*
* @ORM\Column(name="rounds", type="integer", nullable=false)
*/
private $rounds;
/**
* @var integer
*
* @ORM\Column(name="id", type="integer")
* @ORM\Id
* @ORM\GeneratedValue(strategy="IDENTITY")
*/
private $id;
/**
* @var \AppBundle\Entity\Users
*
* @ORM\ManyToOne(targetEntity="AppBundle\Entity\Users", fetch="EAGER")
* @ORM\JoinColumns({
* @ORM\JoinColumn(name="owner_id", referencedColumnName="id")
* })
*/
private $owner;
/**
* @ORM\ManyToMany(targetEntity="AppBundle\Entity\Users", inversedBy="tournamentUsers", fetch="EAGER")
* @ORM\JoinTable(name="users_in_tournament")
*/
protected $users;
Z góry dziękuję wszystkim, którzy będą starać się pomóc :)
PHP 
SQL