CODEWITHSUNDEEP
sqlsymfony1doctrine
Levi Hackwith
Levi Hackwith

Reputation: 9322

Using Raw SQL with Doctrine

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?

Upvotes: 44

Views: 61607

Answers (6)

SMSM
SMSM

Reputation: 1529

Symfony insert raw sql using doctrine.

This in version Symfoney 1.3

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

Upvotes: 0

Nikola Petkanski
Nikola Petkanski

Reputation: 4794

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'));

Upvotes: 6

Twelve47
Twelve47

Reputation: 3982

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

Upvotes: 6

Tom
Tom

Reputation: 30698

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

See the Doctrine API documentation for different execution methods.

Upvotes: 56

takeshin
takeshin

Reputation: 50688

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);
     }
}

Upvotes: 6

richsage
richsage

Reputation: 27102

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).

Upvotes: 41

Related Questions