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...)

Cashew

I don't have fact-supported arguments, but I have found name relationships to be easier to use than join conditions, bit less error prone and tedious to write. – Nyala

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.

Fortenberry

+1 , especially the relation between objects (foreign key) is already connected in the Base class – Shindig

@Fortenberry of the responses I've received, your final point in your answers regarding changing relationship types is the only compelling one for my situation. I'm not concerned as much for why it's good for folks who don't understand how relational db's work--good for beginners isn't always best in the long run. Also, since you note the ON clause... sometimes you want to add additional criteria besides keys inside the 'ON' clause--DQL doesn't support that as far as I know. That's an example of easy for most of what you need to do, but extremely difficult to use for certain situations. – Cashew

You are right, you will have additional clauses. For that case DQL provides WITH statement. And yes; that one will also work with both one-2-one, one-2-many, many-2-many... w/o any change in code. My advice: try it. Once you get hooked, you will never go back. It's like driving top of the class Mercedes vs bottom level Yugo. Both will take you from point A to point B but I don't see lines formed for Yugo. – Fortenberry

@Zeljko, to me, that is grossly misstated comparison. :) I'd never say that with DQL you can achieve much more or much more stable results, or much easier than with plain, old SQL. Maybe for some basic needs. It's more a comparison between automatic and manual gears. – Mesothorax

Answer 2

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

Your code will be easy to migrate to any RDBMS.

And most important, DQL is object query language for your object model, not for your relational schema.

Design

I think your first is qualitative--I've seen really convoluted/complex DQL to accomplish tasks a simple SQL querys could address. Your second point I completely agree, but as noted in my question not a concern for the basic SQL needed to do cover everything DQL can accomplish. Your last point is the most compelling way to look at it. Ok, it's a query language for objects not the underlying storage. What do see as the core benefit of a query language for objects? Does it improve performance? Does it support a well vetted OO pattern? Does it allow for more efficient/better code? – Cashew

@Cashew after using Doctrine 1.2 , its really harmful for performance. – Shindig

do you have guys any reference to understand the model class that doctrine generated (TestTable, BastTest , Test ) and how to deal with them properly , who to call in the controller ? can you please visit my question #11529724 – Shindig

Answer 3

Using DQL helps you to deal with Objects. in case inserting into databae , you will insert an Object

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

in case of selecting from databae, you will select an array and then you can fill it in your 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

Shindig

Answer 4

Zeljko's answer is pretty spot-on.

Most important reason to go with DQL instead of raw SQL (in my book): Doctrine separates entity from the way it is persisted in database, which means that entities should not have to change as underlying storage changes. That, in turn, means that if you ever wish to make changes on the underlying storage (i.e. renaming columns, altering relationships), you don't have to touch your DQL, because in DQL you use entity properties instead (which only happen to be translated behind the scenes to correct SQL, depending on your current mappings).

Markusmarl

After working on an app for the past month and having MANY changes to the database, I agree with this answer. – Lemures