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

Solution 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

Solution 2

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.

Solution 3

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.

Comments

[Raul]

The problem, is that like is a view, I didn't put identifier/primary key, the id that you see in table, are the id of other table.. so I can't use an entity. Maybe is not possible to receive just normal array? .. Maybe I just must to put a primary key and forget about the problem.. even I wouldn't need the nativeQuery call.. but it0s little ugly solution. Thanks for your time.

[Hakan Deryal]

Hmm, in that case you can define the related entities and use the addJoinedEntityResult method to map the info to their entities. There are examples to that on doctrine-project.org/docs/orm/2.0/en/reference/… . I'm not sure if that will work though. You can achieve the same result without the views with Doctrine, but I don't know which one yields the best performance, not an expert in the area.

[Hakan Deryal]

After giving it more thought, addJoinedEntityResult method should work as Doctrine doesn't care about the nature of the query with native SQL, only cares about how it is mapped to the entities. Updated the answer accordingly.

[Raul]

I believe is not good to add a identifier (id) in view, even maybe it is not possible, the view can change, so, identifier would be different for same data. So, I can't use doctrine entities, they need an identifier for work (that is the error that it appears). I can't use addEntityResult (this is obligatory, I believe), so, neither addJoinedEntityResult ..

[Raul]

Well.. finnaly I have little change my view to have all the ids of the different objects in each row, and I have composite an identifier with these id's... So, now I can use a normal entity and I don't need a nativeQuery.. Really difficult to use that in my problem.. Anyway, Hakan, I apreciate your suggestions

[Hakan Deryal]

Im glad that you found a solution. Using an ORM definitly requires a different set of thinking than SQL solutions.