I'm trying to convert SQL-query using subquery into Doctrine Query Builder format (I'm writing a project with Symfony for PHP). Here's the original SQL-query:
SELECT b.brandid, b.brandName, b.netIncome, pc.PCompanyName, sum(ss.qty) as qty, sum(ss.sumSell) as sumSell
FROM shoesShopInnoDB3.brands b
join shoesShopInnoDB3.parentCompany pc on pc.PCompanyid=b.PCompanyid
join shoesShopInnoDB3.shoes s on s.brandid=b.brandid
join (SELECT s.shoesid as ssShoesid, s.shoesTitle as ssShoesTitle, b.brandid as ssBrandid, b.brandName as ssBrandName, sum(oi.quantity) as qty, s.shoesPrice*sum(oi.quantity) as sumSell
FROM shoesShopInnoDB3.shoes s
join shoesShopInnoDB3.brands b on b.brandid=s.brandid
join shoesShopInnoDB3.orderItems oi on oi.shoesid=s.shoesid
join shoesShopInnoDB3.orders o on o.orderid=oi.orderid
group by s.shoesid) as ss on ss.ssShoesid=s.shoesid
group by b.brandid
order by b.brandName
As you see I need to get a sum of sum in subquery
Now I get the next error:
[Syntax Error] line 0, col 270: Error: Expected Doctrine\ORM\Query\Lexer::T_ALIASED_NAME, got 'SELECT'
What code changes should I do?
What I have tried:
public function reportBrands()
{
$subqb = $this->_em->createQueryBuilder();
$subqb
->select('s.shoesid as ssshoesid, s.shoestitle as ssshoestitle, b.brandid as ssbrandid, b.brandname as ssbrandname, sum(oi.quantity) as qty, s.shoesprice*sum(oi.quantity) as sumsell')
->from('App\Entity\Shoes', 's')
->join('App\Entity\Brands', 'b', 'with', 'b.brandid=s.brandid')
->join('App\Entity\Orderitems', 'oi', 'with', 'oi.shoesid=s.shoesid')
->join('App\Entity\Orders', 'o', 'with', 'o.orderid=oi.orderid')
->groupBy('s.shoesid')
;
$qb = $this->createQueryBuilder('b');
$qb
->select('b.brandid, b.brandname, b.netincome, pc.pcompanyname, sum(ss.qty) as qty, sum(ss.sumsell) as sumsell')
->join('App\Entity\Parentcompany','pc', 'with', 'pc.pcompanyid=b.pcompanyid')
->join('App\Entity\Shoes', 's', 'with', 's.brandid=b.brandid')
->join($subqb, 'ss', 'with', 'ss.ssshoesid=s.shoesid')
->groupBy('b.brandid')
->orderBy('b.brandname')
;
return $qb->getQuery()->getResult(); //error here
}