Symfony - How to use LIKE with COALESCE in a Doctrine request?

user_13028086 0
Viewed 30

I have some trouble with LIKE and COALESCE in a Doctrine request (not 100% sure that the trouble is there).

I would like to search inside a database with a filter that match only if exist, and only with a part of the value (for example find 'abc' with the filter set to 'ab').

This request works fine :

public function findUsers($entreprise, $filtres)
{
    $filtre_name = $filtres['name'];

    return $this->createQueryBuilder('users')
        ->where('users.entreprise = :entreprise')
        ->andWhere('users.name = COALESCE(:filtre_name, users.name)')
        ->setParameter('entreprise', $entreprise)
        ->setParameter('filtre_name', $filtre_name)
        ->orderBy('users.name', 'ASC')
        ->getQuery()
        ->getResult();
}

It return all the users of the company "entreprise" where "filtre_name" match (if not null) with "name" in the database. (If "filtre_name" is null, then the where match for all the database thanks to COALESCE).

I would like now to do the same thing but with "LIKE" instead of "=" because for now the name has to match perfectly and I would like a match for "abc" with only "ab" inside the filter for example.

public function findUsers($entreprise, $filtres)
{
    $filtre_name = $filtres['name'];

    return $this->createQueryBuilder('users')
        ->where('users.entreprise = :entreprise')
        ->andWhere('users.name LIKE COALESCE(:filtre_name, users.name)')
        ->setParameter('entreprise', $entreprise)
        ->setParameter('filtre_name', '%'.$filtre_name.'%')
        ->orderBy('users.name', 'ASC')
        ->getQuery()
        ->getResult();
}

The result is an error : "Warning: Undefined property: Doctrine\ORM\Query\AST\CoalesceExpression::$type".

symfony doctrine-orm coalesce sql-like
1 Answers

I have find a solution. I build my queryBuilder with parameter only if they are not Null. So I remove COALESCE and now I can use LIKE.

public function findUsers($entreprise, $filtres)
{        
    $filtre_nom = $filtres['nom'];
    $filtre_gestionnaire = $filtres['gestionnaire'];

    $qb = $this ->createQueryBuilder('users');
    $qb ->where('users.entreprise = :entreprise')
        ->setParameter('entreprise', $entreprise);
    
    if ($filtre_nom != Null) {
        $qb ->andWhere('users.nom LIKE :filtre_nom')
            ->setParameter('filtre_nom', '%'.$filtre_nom.'%');
    }
    if ($filtre_gestionnaire != Null) {
        $qb ->andWhere('users.gestionnaire LIKE :filtre_gestionnaire')
            ->setParameter('filtre_gestionnaire', '%'.$filtre_gestionnaire.'%');
    }
        
    $qb->addorderBy('users.nom', 'ASC');         

    return $qb  ->getQuery()
                ->getResult();
}
user_130280860
Related