How to use date() function / return hydrated objects?

Question

In my Entity Repository for Doctrine2, I have the following:

$date = new DateTime('NOW');
$date = $date->format('Y-m-d');

if ($region) {
    $region_sql = " AND WHERE region LIKE ?3 ";
} else {
    $region_sql = "";
}

$sql = "SELECT *, count(month) as count FROM (SELECT *, date(date_from, 'start of month', '+1 month', '-1 day') as month FROM manifestations WHERE date_to >= :date_to " . $region_sql . ") GROUP BY month";

$stmt = $em->getConnection()->prepare($sql);
$stmt->bindValue(':date_to', $date);

if($region) {
    $stmt->bindValue(3, sprintf('%%,%s,%%', $region));
}

$stmt->execute();

return $stmt->fetchAll();

But I need to change this so that it returns the objects hydrated instead of an array. I originally wanted to use DQL or queryBuilder but could not find a way to get the date() function to work.

http://stackoverflow.com/questions/13272224/use-a-date-function-in-a-where-clause-with-dql for using date functions in DQL. I am not sure if you can use SQL and then use Doctrine to hydrate your entities. — Jurian Sluiman, Jan 20 '15 at 14:08

acontell · Accepted Answer · 2015-01-21T07:29:49.387

With NativeQuery you can execute native SELECT SQL statements and map the results to Doctrine entities or any other result format supported by Doctrine.

What you want to do can be achieved using the ResultSetMappingBuilder.

ResultSetMappingBuilder is a convenience wrapper. It can generate the mappings for you based on Entities.

This is how I'd do it (I assume your query works, maybe you'll have to adjust it, as I will use a new alias):

Create the ResultSetMapping:

use Doctrine\ORM\Query\ResultSetMapping;// Don't forget this

$rsm = new ResultSetMappingBuilder($entityManager);// $entityManager points to your entity manager.
$rsm->addRootEntityFromClassMetadata('path/to/class/MyClass', 'a');// Notice the a, it's an alias that I'll later on use in the query.
$rsm->addScalarResult("count", "count");// column, alias

Prepare $region_sql part as you do in your code and add the a alias to whatever you want to map. a.* will be mapped to an object (notice the as a I use in the query):

$sql = "SELECT a.*, count(month) as count FROM (SELECT *, date(date_from, 'start of month', '+1 month', '-1 day') as month FROM manifestations WHERE date_to >= :date_to " . $region_sql . ") as a GROUP BY month";

Execute the query:

$query = $entityManager->createNativeQuery($sql, $rsm);
$query->setParameter('date_to', $date);

$result = $query->getResult();

This will give you an array of rows. Each of them will be a mixed array, $result[n][0] will contain the object and $result[n]["count"] the value of the count column of the query (name of the column is the same as the alias we set up in the $rsm) where n is the number of the row.

This is more or less the way I did it, however I used `DisconnectedClassMetadataFactory` and looped the metadata for the entity to add all the columns to be "hydrated" with: `$rsm->addFieldResult('m', $obj["columnName"], $obj["fieldName"]);` - so I can call `$object->getFieldName();` — dan2k3k4, Jan 26 '15 at 08:02

How to use date() function / return hydrated objects?

1 Answers1