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