Subquery with LIMIT in Doctrine

Question

I'm trying to do a query that has a subquery with Doctrine. Right now it's giving me an error. My function in the repository is:

public function getRecentPlaylists($count = 3) {


    $q = $this->_em->createQuery("
            SELECT p.id,
            p.featuredImage,
            p.title,
            p.slug,         
            a.firstName,
            a.lastName,
            a.slug as authorSlug,
            (SELECT updated 
                     FROM \Entities\Articles 
                     ORDER BY updated DESC LIMIT 1) as updated
            FROM \Entities\Playlist p
            JOIN \Entities\Account a
                        ON p.account_id = a.id
            ")
        ->setMaxResults($count);

            try{    
            return $q->getResult();
            }catch(Exception $e){
                echo $e->message();
            }

}

This gives me this error:

[Semantical Error] line 0, col 210 near 'LIMIT 1) as updated FROM': Error: Class 'LIMIT' is not defined.

I'm almost giving up on Doctrine, I haven't been able to figure out how to do queries with subqueries or unions with subqueries. Any help with this function? Thanks!

I don't have any experience with Doctrine, but have you tried capitalizing `as` to `AS`? — Brobin, Jun 05 '14 at 19:52

score 20 · Answer 1 · answered Oct 10 '17 at 10:02

You can quite easily add your own syntax to Doctrine to for example add LIMIT 1 to subqueries, as Colin O'Dell explained on his blog.

// AppBundle\DBAL\FirstFunction
<?php

use Doctrine\ORM\Query\AST\Functions\FunctionNode;
use Doctrine\ORM\Query\AST\Subselect;
use Doctrine\ORM\Query\Lexer;
use Doctrine\ORM\Query\Parser;
use Doctrine\ORM\Query\SqlWalker;

/**
 * FirstFunction ::=
 *     "FIRST" "(" Subselect ")"
 */
class FirstFunction extends FunctionNode
{
    /**
     * @var Subselect
     */
    private $subselect;

    /**
     * {@inheritdoc}
     */
    public function parse(Parser $parser)
    {
        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);
        $this->subselect = $parser->Subselect();
        $parser->match(Lexer::T_CLOSE_PARENTHESIS);
    }

    /**
     * {@inheritdoc}
     */
    public function getSql(SqlWalker $sqlWalker)
    {
        return '(' . $this->subselect->dispatch($sqlWalker) . ' LIMIT 1)';
    }
}

# app/config/config.yml
doctrine:
    # ...
    orm:
        # ...
        dql:
            string_functions:
                FIRST: AppBundle\DBAL\FirstFunction

Use as follows:

$dqb->from('MyAppBundle:Foo', 'foo')
    ->leftJoin('foo.bar', 'bar', 'WITH', 'bar = FIRST(SELECT b FROM MyAppBundle:Bar b WHERE b.foo = foo AND b.published_date >= :now ORDER BY t.startDate)');

but what do if i needed, for example `LIMIT 15` ? this would help, doctrine returned with error that sub query return many results ( instead one. — Vladimir Ch, Apr 18 '19 at 20:42
You're my **hero** @simon-epskamp! Thank you so much. I made a change to the function `SUB_LIMIT( , )` — Joachim, Jun 22 '21 at 18:22

score 10 · Answer 2 · answered Jan 09 '17 at 10:48

10

In this case you can use Doctrine's aggregate expression MAX to get the most recent date:

SELECT MAX(a.updated) FROM AppBundle:Article a

You don't need to use LIMIT.

answered Jan 09 '17 at 10:48

José Vte. Calderón

1,348
12
17

score -8 · Accepted Answer · answered Jun 05 '14 at 20:20

-8

What you need is to take out the inner query and make the DQL separately for that, then use the generated DQL inside

$inner_q = $this->_em
    ->createQuery("SELECT AR.updated FROM \Entities\Articles AR ORDER BY AR.updated DESC")
    ->setMaxResults(1)
    ->getDQL();

$q = $this->_em->createQuery("SELECT p.id,
        p.featuredImage,
        p.title,
        p.slug,         
        a.firstName,
        a.lastName,
        a.slug as authorSlug,
        (".$inner_q.") AS updated
        FROM \Entities\Playlist p
        JOIN \Entities\Account a
             ON p.account_id = a.id
    ")
    ->setMaxResults($count);
try{    
    return $q->getResult();
}
catch(Exception $e){
    echo $e->message();
}

answered Jun 05 '14 at 20:20

Javad

4,339
3
21
36

Thanks for the answer. This gives me the error : line 0, col 289 near 'a ': Error: Identification Variable \Entities\Account used in join path expression but was not defined before. – raygo Jun 06 '14 at 13:36
@raygo you can `var_dump($q->getSQL());` then paste the query in your MySQL DB and see where is the error; play around with the SQL query to fix it then apply those changes in your DQL – Javad Jun 06 '14 at 13:56
@raygo Furthermore, are you sure about the inner query; because it's not even a join you are always selecting the first record – Javad Jun 06 '14 at 14:04
Yeah, I for the date of the playlist, I want the date of the last article that belongs to that playlist – raygo Jun 06 '14 at 16:16
@raygo Then why don't you use join? Of course base on the relation type you defined you will need to change your DQL – Javad Jun 06 '14 at 16:25
How could I do that with a JOIN? – raygo Jun 06 '14 at 16:57
If you show us the mapping code for entities or some structure that how you relate those entities, we will be able to help you for the JOIN – Javad Jun 06 '14 at 17:02
Otherwise, I think you can do it by using `MAX(AR.updated)` and `GROUP BY AR.updated` in a join query – Javad Jun 06 '14 at 17:19
Does this solution still work? In my case subquery has no "LIMIT" clause when translated to SQL... – Oscar Pérez Mar 08 '17 at 13:04
17

This is not working setMaxResults is ignored on subquery – cesar moro Mar 28 '17 at 15:30
it is works for my query – pedram shabani Jan 01 '20 at 18:06

Subquery with LIMIT in Doctrine

3 Answers3

Linked