I have problem to write this sql query in QueryBuilder:
SELECT SUM(TableA.SUM) AS TOTAL, TableB.name
FROM TableA INNER JOIN TableB ON TableA.category_id = TableB.id GROUP BY TableB.name
it's work when i called it into mysql query. And i get something like:
Category | TOTAL category a | 40 category b | 67 category c | 5
My Expenditure Entity class:
/**
* Expenditure
*
* @ORM\Table(name="accountant_expenditure")
* @ORM\Entity(repositoryClass="accountant\ExpenditureBundle\Repository\ExpenditureRepository")
*/
class Expenditure
{
/**
* @var integer
*
* @ORM\Column(name="id", type="integer")
* @ORM\Id
* @ORM\GeneratedValue(strategy="AUTO")
*/
private $id;
/**
* @ORM\ManyToOne(targetEntity="accountant\UserBundle\Entity\User")
* @ORM\JoinColumn(name="create_by_user_id", referencedColumnName="id", onDelete="CASCADE")
*/
private $createBy;
/**
* @var \DateTime
*
* @ORM\Column(name="create_at", type="datetime")
*/
private $createAt;
/**
* @var integer
*
* @ORM\ManyToOne(targetEntity="accountant\ExpenditureBundle\Entity\ExpenditureCategories")
* @ORM\JoinColumn(name="category_id", referencedColumnName="id", onDelete="CASCADE")
*/
private $category;
/**
* @var float
*
* @ORM\Column(name="sum", type="float")
*/
private $sum;
//...
ExpenditureCategories entity class:
/**
* ExpenditureCategories
*
* @ORM\Table()
* @ORM\Entity(repositoryClass="accountant\ExpenditureBundle\Repository\ExpenditureCategoriesRepository")
*/
class ExpenditureCategories
{
/**
* @var integer
*
* @ORM\Column(name="id", type="integer")
* @ORM\Id
* @ORM\GeneratedValue(strategy="AUTO")
*/
private $id;
/**
* @var string
*
* @ORM\Column(name="name", type="string", length=255)
*/
private $name;
//...
I write something like this but it's not correct:
public function findCurrentMonthExpendituresSumByCategory()
{
$firstDay = new \DateTime('first day of this month');
$lastDay = new \DateTime('last day of this month');
return $this->createQueryBuilder('e')
->select('SUM(e.sum) AS TOTAL, c.name')
->from('ExpenditureBundle:ExpenditureCategories', 'c')
->leftJoin('e.category', 'c')
->where('e.createAt BETWEEN :start AND :end')
->setParameter('start', $firstDay->format('Y-m-d'))
->setParameter('end', $lastDay->format('Y-m-d'))
->groupBy('c.name')
->getQuery()
->getResult();
}
Maybe it's problem with my annotations in entities? I get:
[Semantical Error] line 0, col 154 near 'c WHERE e.createAt': Error: 'c' is already defined.
Really thanks for help anyway ;)