Doctrine 2, Native Query

Question

I'm trying to make a native query with Doctrine 2.. but I can't make it works..

$q = "SELECT * FROM user_recent_activity WHERE id = {$user->id}";
$rsm = new \Doctrine\ORM\Query\ResultSetMapping;
$query = $this->_em->createNativeQuery($q, $rsm);
$result = $query->getResult();

This is returning empty array.. I'm not sure how it works "ResultSetMapping", but I can't Map nothing with this query as I have seen in examples in doctrine website, because user_recent_activity it's not a table, it's a view like this:

id  user_id  type     created_at
12  5        opinion  2011-02-22 23:29:00
2   2       vote     2011-01-30 14:16:51

id represent different objects, so, are not foreign key..

So, is it possible just to make a normal query to Doctrine 2 ?? .. I'm becoming crazy..

Thanks

Answer 1

It shouldn't be a problem if its a view or table as you get rows and columns from your query.

You need to map the results to entities for native sql queries using rsm.

$rsm->addEntityResult('UserRecentActivity', 'u');
$rsm->addFieldResult('u', 'id', 'id');
$rsm->addFieldResult('u', 'user_id', 'user_id');
$rsm->addFieldResult('u', 'type', 'type');
$rsm->addFieldResult('u', 'created_at', 'created_at');

And you should have the appropriate entity as well.

You can check http://www.doctrine-project.org/docs/orm/2.0/en/reference/native-sql.html for more detailed examples.

UPDATE:

In case of views that contains results from more than 1 table, addJoinedEntityResult() method can be used to map the results to their respective entities. You need to define the entities accordingly and map the results to their fields.

This method will probably end up with partial objects, so it should be used carefully to avoid data corruption.

Information about partial objects: http://www.doctrine-project.org/docs/orm/2.0/en/reference/partial-objects.html

Answer 2

Other possibility for your problem is to use Native queries in conjunction with scalar results. For more info , look at this link, paragraph 12.2.4. Hope to be useful.

Answer 3

To fetch single row

$result = $this->_em->getConnection()->fetchAssoc($sql)

To fetch multiple rows

$result = $this->_em->getConnection()->fetchAll($sql)

Here use sql native query in place of $sql above.