Doctrine Subquery: [Syntax Error] line 0, col 270: Error: Expected DoctrineORMQueryLexer::T_ALIASED_NAME, got ‘SELECT’

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

Here’s my Doctrine query:

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('AppEntityShoes', 's')
            ->join('AppEntityBrands', 'b', 'with', 'b.brandid=s.brandid')
            ->join('AppEntityOrderitems', 'oi', 'with', 'oi.shoesid=s.shoesid')
            ->join('AppEntityOrders', '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('AppEntityParentcompany','pc', 'with', 'pc.pcompanyid=b.pcompanyid')
            ->join('AppEntityShoes', 's', 'with', 's.brandid=b.brandid')
            ->join($subqb->getDQL(), 'ss', 'with', 'ss.ssshoesid=s.shoesid')
            ->groupBy('b.brandid')
            ->orderBy('b.brandname')
        ;
        return $qb->getQuery()->getResult(); //error here
    }

But I get the next error:

[Syntax Error] line 0, col 270: Error: Expected
DoctrineORMQueryLexer::T_ALIASED_NAME, got ‘SELECT’

What code changes should I do?

Source: Symfony Questions

Was this helpful?

0 / 0

Leave a Reply 0

Your email address will not be published. Required fields are marked *