With Doctrine, what are the benefits of using DQL over SQL?

Question

Can someone provide me with a couple of clear (confirmed facts) reasons to use / learn DQL or SQL when you need a custom query when working with Doctrine classes?

I find that if I cannot use ORM's built-in relational functionality to accomplish something, I usually write my own method in the extended Doctrine or DoctrineTable class. In this method, write what you need in direct SQL (using PDO with appropriate prepared instructions / injection protection, etc.). DQL seems to be an additional language for training / debugging / support, which does not appear, which provides sufficient convincing reasons for use in most common situations. DQL does not seem to be much more complicated than SQL to guarantee its use - in fact, I doubt that you could use DQL effectively without having a solid understanding of SQL. Most of the basic SQL syntax ports are pretty good for most of the common databases you will use with PHP.

What am I missing / not? I am sure there is a reason, but I would like to hear from people who intentionally used it significantly and what was achieved when trying to work with a simple SQL language.

I'm not looking for an argument that supports ORM, just DQL, when you need to do something outside of the basic needs of a get-by-relationship type, in a traditional LAMP setup (using mysql, postgres, etc.).

Answer 1

Honestly, I learned SQL with Doctrine1.2 :) I did not even know about foreign keys, cascading operations, complex functions like group_concat and many others. Indexed search is also very nice and convenient, which just works out of the box.

DQL is much easier to write and understand code. For example, this query:

$query = ..... // some query for Categories ->leftJoin("c.Products p") 

It will make a left join between categories and products, and you do not need to write ON p.category_id = c.id.

And if in the future you change the relationship from one or two to say many-2-many, this same query will work without any changes at all. The doctrine will take care of this. If you do this using SQL, then all queries must be modified to include this set-2-many staging table.

Answer 2

I find DQL more readable and convenient. If you configure it correctly, it will be easier to combine objects, and queries will be easier to write.

Your code is easily portable to any DBMS.

And most importantly, DQL is an object query language for your object model, and not for your relational schema.

Answer 3

Using DQL helps you deal with objects. if pasted in a databae, you will enter Object

 $test = new Test(); $test->attr = 'test'; $test->save(); 

if you select from databae you select an array and then you can fill it in an object

 public function getTestParam($testParam) { $q=Doctrine_Query::create() ->select('t.test_id , t.attr') ->from('Test t ') $p = $q->execute(); return $p; } 

You can check the Doctrine documentation for more details.

Answer 4

The answer to Zeljko is pretty spot-on.

The most important reason to use DQL instead of raw SQL (in my book): Doctrine separates an entity from how it is stored in the database, which means that entities should not be changed as basic changes to the repository. This, in turn, means that if you ever want to make changes to the underlying repository (i.e. Rename columns by changing relationships), you do not need to touch your DQL, because in DQL you use entity properties instead ( what happens only with being backstage to fix SQL, depending on your current mappings).