CreateQuery Doctrine Symfony 2 StateFieldPathExpression

Question

I have this Query in MySql

SELECT l.id as like_id, l.spotted_id as spotted_id,count(l.spotted_id) as numero_likes
FROM sn_like_spotted l
LEFT JOIN prof_foto f
ON f.id = l.spotted_id
LEFT JOIN sn_profilo p
ON f.profilo_id = p.id
WHERE p.id = 3
GROUP BY l.spotted_id
ORDER BY numero_likes DESC LIMIT 0,10

I try to do this in Doctrine

public function getFoo($profilo_id){
    $em = $this->getEntityManager();
    $query = $em->createQuery('
    SELECT  l.id as like_id, l.spotted_id as spotted_id,count(l.spotted_id) as numero_likes
    FROM SNLikeBundle:LikeSpotted l
    LEFT JOIN SNFotoBundle:Foto f
    WITH f.id = l.spotted_id
    LEFT JOIN SNProfiloBundle:Profilo p
    WITH f.profilo_id = p.id
    WHERE p.id =  :profilo_id
    GROUP BY l.spotted_id
    ORDER BY numero_likes DESC
  ')->setFirstResults(0)
    ->setMaxResults(10)
    ->setParameter('profilo_id', $profilo_id);

    $results = $query->getResult();
    return $results;


}

Then i have this error

[Semantical Error] line 0, col 36 near 'spotted_id as': Error: Class SN\LikeBundle\Entity\LikeSpotted has no field or association named spotted_id

I change SELECT in:

SELECT  l.id as like_id, l.spotted as spotted_id,count(l.spotted) as numero_likes

and I have this Error:

[Semantical Error] line 0, col 36 near 'spotted as spotted_id,count(l.spotted)': Error: Invalid PathExpression. Must be a StateFieldPathExpression.

Then i Try With IDENTITY

SELECT IDENTITY l.id as like_id, l.spotted as spotted_id,count(l.spotted) as numero_likes

But i Have This Error

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

I try to create also in QueryBuilder (more criteria, same result)

    $q = $this->createQueryBuilder('l');
    $q->leftJoin("l.spotted", 's');
    $q->leftJoin("s.profilo", 'p');
    $q->leftJoin("p.utente", 'u');
    $q->where('(s.foto_eliminata IS NULL OR s.foto_eliminata != 1)');
    $q->andWhere('p.fase_registrazione = :fase');
    $q->andWhere('u.locked = :false');
    $q->andWhere('p.id = :profilo_id');
    $q->setParameter(':fase', 100);
    $q->setParameter('false', false);
    $q->setParameter('profilo_id', $profilo_id);
    $q->groupBy('l.spotted');
    $q->orderBy($q->expr()->count('l.spotted'), 'desc');
    $q->setMaxResults(10);
    $dql = $q->getQuery();
    $results = $dql->execute();

    return $results;

My error is

[Syntax Error] line 0, col 285: Error: Expected end of string, got '('

The problem is in

$q->orderBy($q->expr()->count('l.spotted'), 'desc');

Barno Barno · Accepted Answer · 2013-11-21T10:31:44

For CreateBuilder Case I Resolved:

 $q = $this->createQueryBuilder('l');
        $q->addSelect('count(l.spotted) as numero_likes');
        $q->leftJoin("l.spotted", 's');
        $q->leftJoin("s.profilo", 'p');
        $q->leftJoin("p.utente", 'u');
        $q->where('(s.foto_eliminata IS NULL OR s.foto_eliminata != 1)');
        $q->andWhere('p.fase_registrazione = :fase');
        $q->andWhere('u.locked = :false');
        $q->andWhere('p.id = :profilo_id');
        $q->setParameter(':fase', 100);
        $q->setParameter('false', false);
        $q->setParameter('profilo_id', $profilo_id);
        $q->groupBy('l.spotted');
        $q->addOrderBy('numero_likes','DESC');
        $q->setMaxResults(10);
        $dql = $q->getQuery();
        $results = $dql->execute();

        return $results;

if i don't want numero_likes in my results i can do this:

    $q->addSelect('count(l.spotted) as HIDDEN numero_likes');

But i'd like how can i do this in createQuery

CreateQuery Doctrine Symfony 2 StateFieldPathExpression

1 Answers