Using Raw SQL with Doctrine

Question

I have some extremely complex queries that I need to use to generate a report in my application. I'm using symfony as my framework and doctrine as my ORM.

My question is this:

What is the best way to pass in highly-complex sql queries directly to Doctrine without converting them to the Doctrine Query Language? I've been reading about the Raw_SQL extension but it appears that you still need to pass the query in sections (like from()). Is there anything for just dumping in a bunch of raw sql commands?

Excuse me the off-topic but what tool/library are you using to generate your application reports? Best regards! — Rui Gonçalves, May 08 '10 at 16:25

score 56 · Accepted Answer · answered May 05 '10 at 21:46

56

$q = Doctrine_Manager::getInstance()->getCurrentConnection();
$result = $q->execute(" -- RAW SQL HERE -- ");

See the Doctrine API documentation for different execution methods.

answered May 05 '10 at 21:46

Tom

30,090
27
90
124

@simple: Yes, you can use any SQL you want. – Tom Nov 27 '10 at 11:07
7

As this answer seems to be popular, I should add that this method bypasses the escaping offered by Doctrine, so escape stuff manually or you may leave yourself open to SQL injection holes. – Tom Nov 01 '11 at 16:40
5

It looks to me that this answer is for Doctrine version < 2. For raw queries in Doctrine 2, I found this post helpful: http://forum.symfony-project.org/viewtopic.php?f=23&t=37872 – Jason Swett Mar 30 '12 at 15:09
I found that adding ->getDbh() as in @richsage's answer prevents warnings from happening when the doctrine profiler is enabled. – cmc May 18 '15 at 14:15

score 41 · Answer 2 · answered May 05 '10 at 18:01

41

Yes. You can get a database handle from Doctrine using the following code:

$pdo = Doctrine_Manager::getInstance()->getCurrentConnection()->getDbh();

and then execute your SQL as follows:

$query = "SELECT * FROM table WHERE param1 = :param1 AND param2 = :param2";
$stmt = $pdo->prepare($query);

$params = array(
  "param1"  => "value1",
  "param2"  => "value2"
);
$stmt->execute($params);

$results = $stmt->fetchAll();

You can use bound variables as in the above example.

Note that Doctrine won't automatically hydrate your results nicely into record objects etc, so you'll need to deal with the results being returned as an array, consisting of one array per row returned (key-value as column-value).

answered May 05 '10 at 18:01

richsage

26,912
8
58
65

Is there a way to force doctrine to hydrate the results I get via the PDO? or do I just have to work with what I'm given? – Levi Hackwith May 05 '10 at 19:38
From memory when I used the above, not automatically as Doctrine doesn't know what columns you have. If you named the columns well enough, I guess you could use fromArray() or similar, but the data would need to be re-formatted into something usable for that. – richsage May 05 '10 at 20:23
If you can, use your query to return a record ID and then use plain old $table->find($id) to load it. – lotsoffreetime May 06 '10 at 13:18

score 6 · Answer 3 · answered May 05 '10 at 20:51

I'm not sure what do you mean saying raw SQL, but you coud execute traditional SQL queries this way:

... 
// $this->_displayPortabilityWarning();

$conn = Doctrine_Manager::connection();
$pdo = $conn->execute($sql);
$pdo->setFetchMode(Doctrine_Core::FETCH_ASSOC);
$result = $pdo->fetchAll();
...

The following method is not necsessary, but it shows a good practice.

protected function _displayPortabilityWarning($engine = 'pgsql')
{
     $conn = Doctrine_Manager::connection();
     $driver = $conn->getDriverName();

     if (strtolower($engine) != strtolower($driver)) {
        trigger_error('Here we have possible database portability issue. This code was tested on ' . $engine . ' but you are trying to run it on ' . $driver, E_USER_NOTICE);
     }
}

score 6 · Answer 4 · answered Jun 03 '10 at 13:34

6

You can also use Doctrine_RawSql(); to create raw SQL queries which will hydrate to doctrine objects.

answered Jun 03 '10 at 13:34

Twelve47

3,924
3
22
29

score 6 · Answer 5 · answered Dec 08 '11 at 14:08

6

It should be noted, that Doctrine2 uses PDO as a base, thus I would recommend using prepared statements over plain old execute.

Example:

$db = Doctrine_Manager::getInstance()->getCurrentConnection();
$query = $db->prepare("SELECT `someField` FROM `someTable` WHERE `field` = :value");
$query->execute(array('value' => 'someValue'));

answered Dec 08 '11 at 14:08

Nikola Petkanski

4,724
1
33
41

This question is about Symfony 1. On Symfony 1, Doctrine 1.2 is used - This answer might be misguiding. – xmc Feb 27 '13 at 23:01
Sorry, I didn't mean to mislead. The solution is still valid though as Symfony 1 can be used with Doctrine as well. – Nikola Petkanski Feb 28 '13 at 09:02

score 0 · Answer 6 · edited Jan 19 '13 at 09:58

0

Symfony insert raw sql using doctrine.

This in version Symfoney 1.3

$q = Doctrine_Manager::getInstance()->getCurrentConnection();
$result = $q->execute($query);

edited Jan 19 '13 at 09:58

j0k

22,600
28
79
90

answered Jan 19 '13 at 03:11

SMSM

1,509
3
19
34

Using Raw SQL with Doctrine

6 Answers6

Linked