Doctrine search full name in 2 fields

0
votes

I have a User entity with firstName and lastName fields. How can I search full name in any order. For example search 'John Doe', 'Doe John', 'John D' and get relevant results

Here is my current query. But I can search by only one column at a time, i.e either get result matching to 'firstName' or 'lastName'. For Example, I get results for 'John' or 'Doe', but not for 'John Doe'

  $likeName = $name.'%';
  $qb = $this->createQueryBuilder('user')
        ->where($qb->expr()->orX(
            $qb->expr()->like('user.firstName',  "'$likeName'"),
            $qb->expr()->like('user.lastName', "'$likeName'")
   );
3
mysqlsymfonydoctrine-orm
What does it mean " I can search by only one column at a time"? Can you explain well what is the result that you want and what ypu get now? Make a little example of data please - Alessandro Minoccheri
@AlessandroMinoccheri it means I can get results matching wither firsName or lastName, but when I enter full name I don't get anything - user4666065

3 Answers

1
votes
        $qb->where($qb->expr()->andX(
    $qb->expr()->orX(
        $qb->expr()->like(
            $qb->expr()->concat('employee_alias.firstName', $qb->expr()->concat($qb->expr()->literal(' '), 'employee_alias.lastName')),
            $qb->expr()->literal($phrase.'%')
        ),
        $qb->expr()->like(
            $qb->expr()->concat('employee_alias.lastName', $qb->expr()->concat($qb->expr()->literal(' '), 'employee_alias.firstName')),
            $qb->expr()->literal($phrase.'%')
        )
    ))
);
0
votes

try this:

$likeName = $name.'%';
  $qb = $this->createQueryBuilder('user')
        ->where($qb->expr()->orX(
            $qb->expr()->like($qb->expr()->concat($qb->expr()->concat('user.lastName', ' '), 'user.firstName')
,  "'$likeName'"),
            $qb->expr()->like($qb->expr()->concat($qb->expr()->concat('user.firstName', ' '), 'user.lastName')
, "'$likeName'")
   ));
0
votes

Maybe try create repository class http://symfony.com/doc/current/doctrine/repository.html and something like this query:

$this->getEntityManager()
            ->createQueryBuilder('user')
            ->andWhere('user.firstName LIKE :firstName')
            ->andWhere('user.lastName LIKE :lastName')
            ->setParameter('firstName', $firstName)
            ->setParameter('lastName', $lastName)
            ->getQuery()
            ->getResult();