Doctrine subquery: [syntax error] line 0, col 270: error: expected doctrine\ORM\query\lexer::T_ALIASED_NAME, got 'SELECT'

See more:

SQL

PHP

MySQL

Doctrine

Symfony

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
    }