Symfony2 & Doctrine: Create Custom SQL-Query

Symfony2 & Doctrine: Create custom SQL-Query

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

Execute raw SQL using Doctrine 2

I found out the answer is probably:

A NativeQuery lets you execute native
SQL, mapping the results according to
your specifications. Such a
specification that describes how an
SQL result set is mapped to a Doctrine
result is represented by a
ResultSetMapping.

Source: Native SQL.

Symfony2 Using Query in Custom Repository Class

You seem to mix DQL and SQL. You can have a look at what is possible with DQL here.

Looking at your query, I suggest that you use SQL instead of DQL.

An example of how to do a SQL query inside a repository:

$sql = 'SELECT * FROM table WHERE id = :id';
$params = array(
    'id' => 4,
);

return $this->getEntityManager()->getConnection()->executeQuery($sql, $params)->fetchAll();

Execute custom SQL in symfony

$query = "SELECT * from something complicated";
$rs = Doctrine_Manager::getInstance()->getCurrentConnection()->fetchAssoc($query);

The resultset is an array.

Symfony Sonata SEO custom sql query with LIKE

The problem was the %, which is a special char in yaml.

So there's two ways to get this to work:

To escape it, it needs to be preceded by another % so:

AND `path` NOT LIKE('checkout/%%')

Alternatively you can avoid using the % as per Jared's comment and instead do:

LEFT(path, 9) != 'checkout/'

---

Related Topics