1

My Symfony2 app has a Person entity, it has $firstName and $lastName as its properties. It's got a method getFullName() which returns the full name of Person.

// Vendor/Bundle/Entity/Person.php
public function getFullName()
{
    return $this->firstName . ' ' . $this->lastName;
}

Now, I want to allow users to search Person. There is only one search field which users can enter some value. The value can be the full name, first name, or last name of the person they look for.

Here is some code from controller which will do the filtering:

// Vendor/Bundle/Controller/PersonController.php
public function listAction(Request $request)
{
    // ...
    $people = $this->get('doctrine')
        ->getRepository('SomeBundle:Person')
        ->createQueryBuilder('p')
        ->where('p.fullName = :fullName')
        ->setParameter('fullName', $request->query->get('fullName'))
        ->getQuery()
        ->getResult();
    // ...
}

However, it doesn't work. Apparently, it is because fullName is not one of Person's fields.

Is there a way to filter results using QueryBuilder, i.e. I do not want to iterate over all results and compare their names?

Although Victor's answer shows the correct result, I am still looking for a more 'elegant' way to do this. Please post your suggestion as answer if you have one!

Community
  • 1
  • 1
pikachu0
  • 802
  • 1
  • 12
  • 23
  • Well, it is because fullName is not a Person's field. Either you store it, or you filter on firstName or/and lastName (in your where clause). – Brice Feb 27 '14 at 10:56
  • @Brice But it won't work if a user enter the full name of a person. – pikachu0 Feb 27 '14 at 10:59
  • Why not? Just `explode()` the fullName and compare each part with `firstName` and `lastName`. – Brice Feb 27 '14 at 11:00

1 Answers1

1

What about to explode fullName with space:

// Vendor/Bundle/Controller/PersonController.php
public function listAction(Request $request)
{
    // ...
    $name = explode(' ', $request->query->get('fullName'));
    $people = $this->get('doctrine')
        ->getRepository('SomeBundle:Person')
        ->createQueryBuilder('p')
        ->where('p.firstname = :firstName')
        ->andWhere('p.lastName = :lastName')
        ->setParameter('firstName', $name[0])
        ->setParameter('lastName', $name[1])
        ->getQuery()
        ->getResult();
    // ...
}

or try to concatenate firstName and lastName fields in query:

// Vendor/Bundle/Controller/PersonController.php
public function listAction(Request $request)
{
    // ...
    $people = $this->get('doctrine')
        ->getRepository('SomeBundle:Person')
        ->createQueryBuilder('p')
        ->where('CONCAT(p.firstName, ' ', p.lastName) = :fullName')
        ->setParameter('fullName', $request->query->get('fullName'))
        ->getQuery()
        ->getResult();
    // ...
}

NOTE: it may be inaccurate if you change getFullName() behaviour

Victor Bocharsky
  • 11,930
  • 13
  • 58
  • 91