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

Question

Can someone provide me a couple clear (fact supported) reasons to use/learn DQL vs. SQL when needing a custom query while working with Doctrine Classes?

I find that if I cannot use an ORM's built-in relational functionality to achieve something I usually write a custom method in the extended Doctrine or DoctrineTable class. In this method write the needed it in straight SQL (using PDO with proper prepared statements/injection protection, etc...). DQL seems like additional language to learn/debug/maintain that doesn't appear provide enough compelling reasons to use under most common situations. DQL does not seem to be much less complex than SQL for that to warrant use--in fact I doubt you could effectively use DQL without already having solid SQL understanding. Most core SQL syntax ports fairly well across the most common DB's you'll use with PHP.

What am I missing/overlooking? I'm sure there is a reason, but I'd like to hear from people who have intentionally used it significantly and what the gain was over trying to work with plain-ole SQL.

I'm not looking for an argument supporting ORMs, just DQL when needing to do something outside the core 'get-by-relationship' type needs, in a traditional LAMP setup (using mysql, postgres, etc...)

Answer 1

To be honest, I learned SQL using Doctrine1.2 :) I wasn't even aware of foreign-keys, cascade operations, complex functions like group_concat and many, many other things. Indexed search is also very nice and handy thing that simply works out-of-the-box.

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

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

It will do left join between Categories and Products and you don't have to write ON p.category_id=c.id.

And if in future you change relation from one-2-many to let's say many-2-many, this same query will work without any changes at all. Doctrine will take care for that. If you would do that using SQL, than all the queries would have to be changed to include that intermediary many-2-many table.