How would I write this SQL in DQL?

Question

What I'm after is the following SQL query

SELECT p.*
  FROM player p
 WHERE CONCAT(p.first_name, ' ', p.last_name) = ?

in DQL. What I have so far is

$qb->add('select', 'p')
   ->add('from', 'VNNCoreBundle:Player p')
   ->add('where', $qb->expr()->eq(
        $qb->expr()->concat('p.firstName', $qb->expr()->concat(' ', 'p.lastName')),
        ':name'
     ))
   ->setParameters(array('name' => $name));

but that's wrong.

I know I could always just get the ID of my record with native SQL, then do findOneById() to get the record with Doctrine. On one hand, it feels like a kludge/hack to do that, and on the other hand, it feels kind of dumb to do in DQL to do what would be a simple query in straight SQL, since the DQL isn't shaping up to be very elegant. Anyway, I've put this much time into it, and I'm curious now what the right way to do it is in DQL.

Answer 1

I ended up going the native SQL route. I kind of forgot about that third option.

$rsm = new ResultSetMapping;
$rsm->addEntityResult('Player', 'p');
$rsm->addFieldResult('p', 'id', 'player_id');
$rsm->addFieldResult('p', 'name', 'name');

$sql = " 
    SELECT p.*
      FROM players p
     WHERE CONCAT(p.first_name, ' ', p.last_name) = ?
";

$query = $this->getEntityManager()->createNativeQuery($sql, $rsm);
$query->setParameter(1, $name);