How can I create a custom SQL query in Symfony2 using Doctrine? Or without Doctrine, I don't care.
Doesn't work like this:
$em = $this->getDoctrine()->getEntityManager();
$em->createQuery($sql);
$em->execute();
Thanks.
Add a comment
|
2 Answers
You can get the Connection object directly from the Entity Manager, and run SQL queries directly through that:
$em = $this->getDoctrine()->getManager(); // ...or getEntityManager() prior to Symfony 2.1
$connection = $em->getConnection();
$statement = $connection->prepare("SELECT something FROM somethingelse WHERE id = :id");
$statement->bindValue('id', 123);
$statement->execute();
$results = $statement->fetchAll();
However, I'd advise against this unless it's really necessary... Doctrine's DQL can handle almost any query you might need.
Official documentation: https://www.doctrine-project.org/projects/doctrine-dbal/en/2.9/reference/data-retrieval-and-manipulation.html
3 Comments
Orbling
There is also the Native SQL provision in Doctrine: docs.doctrine-project.org/en/latest/reference/native-sql.html
Oli
Please note getEntityManager is deprecated since Symfony 2.1. Use getManager instead
Halfstop
While it's true that Doctrine and DQL and querybuilder can probably do 90% of what you want to do with a database, but sometimes you need to run queries and you don't the object overheard and constraints. This usually come up for me when I'm doing reporting.
You can execute this code it works :
$em = $this->getDoctrine()->getEntityManager();
$result= $em->createQuery($sql)->getResult();
1 Comment
ioleo
$em->createQuery() does not execute SQL, but DQL.