在 Doctrine 中,使用 DQL 而不是 SQL 有什么好处?

2022-01-16 00:00:00 sql orm php doctrine dql

在使用 Doctrine Classes 时需要自定义查询时,有人可以向我提供几个使用/学习 DQL 与 SQL 的明确(事实支持)理由吗?

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?

我发现如果我不能使用 ORM 的内置关系功能来实现某些事情,我通常会在扩展的 Doctrine 或 DoctrineTable 类中编写自定义方法.在这种方法中,用直接 SQL 编写所需的内容(使用 PDO 和适当的准备好的语句/注入保护等......).DQL 似乎是一种额外的学习/调试/维护语言,但在最常见的情况下并没有提供足够令人信服的理由.DQL 似乎并不比 SQL 复杂得多,因此值得使用——事实上,我怀疑您是否可以在没有扎实的 SQL 理解的情况下有效地使用 DQL.大多数核心 SQL 语法移植在您将与 PHP 一起使用的最常见的数据库中都相当不错.

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.

我错过/忽略了什么?我确信这是有原因的,但我想听听那些有意大量使用它的人,以及尝试使用纯 ole SQL 的好处.

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.

我不是在寻找支持 ORM 的论据,而是在传统 LAMP 设置(使用 mysql、postgres 等...)

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

推荐答案

说实话,我是使用 Doctrine1.2 学习 SQL 的 :) 我什至不知道外键、级联操作、复杂函数如 group_concat 等等,还有很多其他的东西.索引搜索也是非常好用且方便的东西,开箱即用.

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 更易于编写和理解代码.例如,这个查询:

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

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

它将在类别和产品之间进行左连接,您不必在 p.category_id=c.id 上写入.

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

如果将来您将关系从一对多更改为多对多,则相同的查询将完全不做任何更改.教义会照顾到这一点.如果您使用 SQL 执行此操作,则必须更改所有查询以包含该中间多对多表.

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.

相关文章