CODEWITHSUNDEEP
symfonydoctrine-ormdqlquery-string
Sergio Hernández
Sergio Hernández

Reputation: 73

how to search array words with Doctrine DQL using bucle

i'm trying to search words stored in array in this way:

$dql_cat =' SELECT c
                FROM FrontendBundle:Categoria c
                WHERE';

        foreach ($palabras as $palabra){    

            if ($palabra === reset($palabras)){
                $dql_cat .= ' c.nombre LIKE %'.$palabra.'%';
            }
            else{
                $dql_cat .= ' OR c.nombre LIKE %'.$palabra.'%';
            }               
        }

        $dql_cat .= ' ORDER BY c.nombre';       
        $query = $em->createQuery($dql_cat);            
        $resultados['categorias'] = $query->getResult();

But i get an exception with the query.

Query result:

SELECT c FROM FrontendBundle:Categoria c WHERE c.nombre LIKE %carpinteria% OR c.nombre LIKE %aluminio% ORDER BY c.nombre

Query exception:

QueryException: [Syntax Error] line 0, col 98: Error: Expected StateFieldPathExpression | string | InputParameter | FunctionsReturningStrings | AggregateExpression, got '%'

I think that is more proper to use queryBuilder to avoid mistakes but i don't know how to use it with a parameters array.

I need a solution with or without queryBuilder.

Thanks.

Upvotes: 2

Views: 882

Answers (2)

Sergio Hernández
Sergio Hernández

Reputation: 73

queryBuilder would be as follows:

$qb = $em->createQueryBuilder();
        $qb->select('c')
            ->from('FrontendBundle:Categoria', 'c');

        foreach ($palabras as $palabra){
            if ($palabra === reset($palabras)){
                $qb->where($qb->expr()->like('c.nombre', $qb->expr()->literal('%' . $palabra . '%')));
            }                                       
            $qb->orWhere($qb->expr()->like('c.nombre', $qb->expr()->literal('%' . $palabra . '%')));
        }

        $query = $qb->orderBy('c.nombre', 'ASC')
        ->getQuery();

        $resultados['categorias'] = $query->getResult();

Upvotes: 3

devilcius
devilcius

Reputation: 1884

To begin with %$palabra% should be quoted, right?:

    foreach ($palabras as $palabra){    

        if ($palabra === reset($palabras)){
            $dql_cat .= " c.nombre LIKE '%".$palabra."%'";
        }
        else{
            $dql_cat .= " OR c.nombre LIKE '%".$palabra."%'";
        }               
    }

Upvotes: 0

Related Questions