php – Using native SQL query without entity class-ThrowExceptions

Exception or error:

I need to create native SQL query with couple of unions and subqueries. It’ll look approximately like this:

SELECT, COUNT(*) as count_ids
    SELECT FROM ... a WHERE ... LIKE ('%:param%')
    SELECT FROM ... b WHERE ... LIKE ('%:param%')
) res
ORDER BY count_ids asc

Result won’t match any Entity I use in my application. Is it possible to create ResultSetMapping with “anonymous” object? Or is it, at least, possible to create an Entity that wouldn’t create table next time I update schema, so I can map results to it?

Or is there any other Doctrine-friendly way to deal with such query? Making changes to database isn’t possible though, as I’m dealing with legacy stuff that cannot be touched. I’d also strongly prefer if I did everything on database side, not involving much of PHP in it.

How to solve:

Do you have a particular need to map results to a domain object? If not, you could use the DBAL to make a plain old query, which will return an array, as detailed in the Symfony2 cookbook and the Doctrine DBAL documentation:

$conn = $this->container->get('database_connection');
$sql = 'SELECT, COUNT(*)...';
$rows = $conn->query($sql);


Use addScalarResult method of ResultSetMapping

$rsm = new ResultSetMapping();

$rsm->addScalarResult('cnt', 'cnt');
$rsm->addScalarResult('id', 'id');

$query = $this->em->createNativeQuery('SELECT count(*) AS  cnt, id_column as id FROM your_table group by id', $rsm);

$result = $query->getResult();


Result array:

array (size=1)
  0 => 
    array (size=2)
      'cnt' => int 1
      'id' => int 15

Leave a Reply

Your email address will not be published. Required fields are marked *