Execute raw SQL using Doctrine 2

I got it to work by doing this, assuming you are using PDO.

//Place query here, let's say you want all the users that have blue as their favorite color
$sql = "SELECT name FROM user WHERE favorite_color = :color";

//set parameters 
//you may set as many parameters as you have on your query
$params['color'] = blue;


//create the prepared statement, by getting the doctrine connection
$stmt = $this->entityManager->getConnection()->prepare($sql);
$stmt->execute($params);
//I used FETCH_COLUMN because I only needed one Column.
return $stmt->fetchAll(PDO::FETCH_COLUMN);

You can change the FETCH_TYPE to suit your needs.


How to execute a raw Query and return the data.

Hook onto your manager and make a new connection:

$manager = $this->getDoctrine()->getManager();
$conn = $manager->getConnection();

Create your query and fetchAll:

$result= $conn->query('select foobar from mytable')->fetchAll();

Get the data out of result like this:

$this->appendStringToFile("first row foobar is: " . $result[0]['foobar']);

//$sql - sql statement
//$em - entity manager

$em->getConnection()->exec( $sql );

Here's an example of a raw query in Doctrine 2 that I'm doing:

public function getAuthoritativeSportsRecords()
{   
    $sql = " 
        SELECT name,
               event_type,
               sport_type,
               level
          FROM vnn_sport
    ";

    $em = $this->getDoctrine()->getManager();
    $stmt = $em->getConnection()->prepare($sql);
    $stmt->execute();
    return $stmt->fetchAll();
}   

Tags:

Sql

Php

Doctrine