How To Convert SQL Into DQL in Symfony and Doctrine?

Today I am going to talk about some examples on converting SQL queries into DQL . Doctrine query language (DQL) is the query language used by Doctrine ORM. Doctrine ORM 2.x is shipped with Symfony 2.x by default. Let’s take some examples and discuss. For the following examples we will be creating an instance of Doctrine query builder to structure DQL’s.

Creating an instance of doctrine query builder

$em = $this->getDoctrine()->getManager();
$result = $em->createQueryBuilder();

SQL

SELECT * FROM contact

DQL

$dql = $result->select(‘c’)
->from(‘ACMETestBundle:Contact’, ‘c’)
->getQuery();

In the above dql example inside ‘from’ tag you have to mention the fully qualified name of the entity which represents the contact table. I am taking a the result as a doctrine object. But if you want to get the results as an array instead of the doctrine object, you can add this line at the end of the code.

$dql = $result->select(‘c’)
->from(‘ACMETestBundle:Contact’, ‘c’)
->getQuery()
->getResult(\Doctrine\ORM\Query::HYDRATE_ARRAY);

Sometimes you want to see whether dql returns the correct sql or not. So, in that case you would like to return the relevant sql for the given dql. To do that you can add following piece of code after getQuery() tag.

$dql = $result->select(‘c’)
->from(‘ACMETestBundle:Contact’, ‘c’)
->getQuery()
->getSQL();

Assume you want to convert a sql which contains one or few where conditions in to dql.
SQL

SELECT * FROM contact WHERE first_name = ‘david’ AND last_name = ‘john’;

DQL

$dql = $result->select(‘c’)
->from(‘ACMETestBundle:Contact’, ‘c’)
->where(‘c.first_name = :fName’)
->setParameter(‘fName’, $firstName)
->andWhere(‘c.last_name = :lName’)
->setParameter(‘lName’, $lastName)
->getQuery()
->getResult(\Doctrine\ORM\Query::HYDRATE_ARRAY);

Remember the best practice is we don’t give the where condition as this ‘->where(‘c.first_name = ” ‘. $firstName.’ ” ‘ )’, which is wrong. We always pass the variable inside setParameter() method, which is sql safe. If you want to perform ‘first_name LIKE ‘%david%’ . You can alter above where and set parameter statements like this.

->where(‘c.first_name LIKE :fName’)
->setParameter(‘fName’, ‘%’.$firstName.’%’)

If you want to perform ‘OR’ condition in where instead of ‘AND’ you just use ‘orWhere()’ instead of ‘andWhere’.
In a situation where you would like to perform a JOIN such as LEFT JOIN there are two ways you can do it. If you have already mentioned the relationships (such as oneToOne, oneToMany) in orm.yml files you can easily perform the LEFT JOIN like this,
SQL

SELECT c, p FROM contact c LEFT JOIN person p ON p.contact_id = c.id

DQL

$dql = $result->select(‘c’,’p’)
->from(‘ACMETestBundle:Contact’, ‘c’)
->leftJoin(‘c.person’, ‘p’)
->getQuery()
->getResult(\Doctrine\ORM\Query::HYDRATE_ARRAY);

But, if you want to perform the LEFT JOIN relationship when you query (if you haven’t set the necessary relationships at first place), you can do the same like this manner,

$dql = $result->select(‘c’,’p’)
->from(‘ACMETestBundle:Contact’, ‘c’)
->leftJoin(‘c.person’, ‘p’, \Doctrine\ORM\Query\Expr\Join::ON, ‘p.contact_id = c.id’)
->getQuery()
->getResult(\Doctrine\ORM\Query::HYDRATE_ARRAY);

Inside ‘leftJoin’ tag you need to give name of the table unlike fully qualified entity name we gave inside ‘from’ tag.

Well, that’s it for today. I hope you guys had a better understanding about converting SQLs into DQLs.

Cheers!
Source: http://anjanasilva.com/blog/sql-into-dql-examples-symfony-2-doctrine-2/