Posted in Database Doctrine


Doctrine ORM expects every table to be an Entity. But that is not easy , when dealing with complex query and which requires querying the result..
So, a hack to this problem is Resultsetmapping class, which allows you to write sql query and then map the result to Doctrine… Here is an example:

use Doctrine\ORM\Query\ResultSetMapping;
$query =   "select as id,q1.display_id as display_id,q1.product_id from
                        	(SELECT *
                        	FROM (
                        	SELECT id, display_id,active_flag,hash,product_id
                        	FROM test_case
                        	where  product_id=$productId and active_flag=0
                        	ORDER BY display_id DESC
                        	) a
                        	GROUP BY hash
                        	) AS q1
                        	(SELECT *
                        	FROM (SELECT id,display_id,active_flag,hash
                        	FROM test_case where product_id=$productId
                        	ORDER BY display_id DESC) b
                        	GROUP BY hash ) AS q2
    	$rsm = new ResultSetMapping();
    	$rsm->addEntityResult('Test\Entity\TestCase', 'TestCase');
    	$rsm->addFieldResult('TestCase', 'id', 'id');
    	$rsm->addFieldResult('TestCase', 'display_id', 'displayId');
    	$rsm->addFieldResult('TestCase', 'product_id', 'productId');
    	$query = $this->getEntityManager()->createNativeQuery($query, $rsm);
        $testCaseDrafts = $query->getResult();