CODEWITHSUNDEEP
doctrine-ormzend-framework2
dan2k3k4
dan2k3k4

Reputation: 1419

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

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.

Upvotes: 1

Views: 193

Answers (1)

acontell
acontell

Reputation: 6932

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.

Upvotes: 1

Related Questions