How to Run Native SQL Queries with Doctrine

It’s been a long time again until I have written a post. This will be short one, I will just give an example how you can run native sql queries with your doctrine connection.

It’s generally hard to run a complicated query with multiple joins in Doctrine. So you generally need the raw sql format, where you can’t easily find on Doctrine which is one of the best ORM frameworks for PHP. Here is what you can do:

//Assume that you have connected to a database instance...
$statement = Doctrine_Manager::getInstance()->connection();
$results = $statement->execute("SELECT * FROM users WHERE id = ?", array(1));
var_dump($results->fetchAll());

That’s cool now you got a result with your Doctrine connection! Cool isn’t it? On second thought it’s not that cool because it’s just an array and not a Doctrine_Collection. Actually, if I had wanted to use arrays instead of Doctrine classes, I wouldn’t use Doctrine in the first place. So why I get an array?

Because you are not actually making a request via Doctrine. It’s the PDO instance that you are using and it does only return arrays. So it should be a workaround for this. Doctrine has a class named Doctrine_RawSql (what an appropriate name isn’t it?) and you can use it like this:

//Assume you have got a running connection again...
$q = new Doctrine_RawSql(Doctrine_Manager::getInstance()->connection());
$q->select('{u.*}, {p.*}') //Check that it's between curly braces...
  ->from('user u, phonenumbers p')
  ->where('u.id = p.user_id AND u.id = ?')
  ->addComponent('u', 'User u') //Dont forget to associate tables with Doctrine classes...
  ->addComponent('p', 'u.Phonenumber p'); 
  /*
  Notice how Im relating the phonenumbers and users. This means that user has a many-to-many relation with phonenumber and phonenumbers can be called as User->Phonenumber!! (just like Doctrine style!)
  */
$users = $q->execute(array(1));
var_dump($users);

So that’s all! Without bothering with DQL or anything else. Just write your sql query, make the associations (via addComponent) and you are good to go!