开发者

Doctrine 2: Writing a proper Subselect

开发者 https://www.devze.com 2023-03-07 06:23 出处:网络
I am trying to get the total records $qb will return before I apply a START & LIMIT property to the query $qb. My $qb and $totalQb run just fine by themselves, but when I try and use $qb as a sub-

I am trying to get the total records $qb will return before I apply a START & LIMIT property to the query $qb. My $qb and $totalQb run just fine by themselves, but when I try and use $qb as a sub-select, I get an error thrown:

$qb = $this->entityManager->createQueryBuilder()
        ->select('w, se')
        ->from('Dashboard\Entity\Section', 'se')
        ->innerJoin('se.word', 'w')
        ->innerJoin('se.location', 'l');

    $qb-&开发者_运维知识库gt;add('where', $qb->expr()->andx(
        $qb->expr()->eq('l.ignored', $ignored),
        $qb->expr()->eq('l.id', $params['l_id'])
    ), true);

Everything above this line runs great by itself

$totalQb = $this->entityManager->createQueryBuilder()
        ->select('COUNT(x.id)')
        ->from('Dashboard\Entity\Section', 'x');

The above $totalQb runs fine by itself. But when I do the following, and try and use the $qb as a subselect of $totalQb...

$dql = $qb->getDql();
$totalQb->add('where', $totalQb->expr()->exists( $dql ));
$totalSql = $totalQb->getQuery();

$sql = $totalSql->getSql();
$total = $totalSql->getSingleScalarResult();

THIS IS THE ERROR THROWN

It's referring to the SubSelect 'exists' statement. But when I run the $dql by itself it returns the expected results

[Syntax Error] line 0, col 69: Error: Expected Doctrine\ORM\Query\Lexer::T_FROM, got ','

What's the proper way to do a sub-select in my scenario?

UPDATE It was suggested I add the $totalSql->getDQL(); This is the output of that statement:

SELECT COUNT(x.id) FROM Dashboard\Entity\Section x WHERE 
EXISTS(
    SELECT w, se 
    FROM Dashboard\Entity\Section se 
    INNER JOIN se.word w 
    INNER JOIN se.location l 
    AND (l.ignored = 0) 
    AND (l.id = 2) 
    GROUP BY w.id, l.id
)


I was able to fix the above query by changing the inner ->select to only pull from one table.

0

精彩评论

暂无评论...
验证码 换一张
取 消