CODEWITHSUNDEEP
nativedoctrine-orm
Raul
Raul

Reputation: 145

Doctrine 2, Native Query

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

Upvotes: 9

Views: 19636

Answers (3)

Alpesh Panchal
Alpesh Panchal

Reputation: 1713

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.

Upvotes: 3

artaxerxe
artaxerxe

Reputation: 6421

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.

Upvotes: 1

Hakan Deryal
Hakan Deryal

Reputation: 2903

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

Upvotes: 8

Related Questions