Posted in Database Doctrine

ResultSetMapping

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 q1.id 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
                	join
                        	(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
                	on
                	q1.id = q2.id";
    	$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();
Advertisements

Author:

I am a passionate software developer currently located in Bengaluru. I love creating beautiful stuffs, reading and soccer. When I am not in front of the PC, you'll find me EITHER day dreaming ;-) OR playing soccer OR reading . FCB and NEUFC are my favorite clubs

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s