Doctrine DQL 连接可空列 [英] Doctrine DQL join on nullable columns

问题描述

是否可以使用 Doctrine QueryBuilder 基于可为空的列连接两个表?

Is it possible to join two tables based on a null-able column with the Doctrine QueryBuilder?

使用 Doctrine 中的默认相等运算符，转换为 MySQL 中的常规相等运算符，当比较两个 NULL 值时返回 NULL.

Using the default equality operator in Doctrine, translates into the regular equality operator in MySQL which returns NULL when comparing two NULL values.

我可以使用 空安全运算符.

SELECT t1.name, t1.field, t2.field2
FROM table1 t1 
  LEFT OUTER JOIN table2 t2 ON (t2.field <=> t1.field)

但是，由于我认为 <=> 是特定于 MySQL 的，所以在 Doctrine 中没有开箱即用的支持吗?

However, as I presume <=> is MySQL specific, there is no out-of-the-box support for this in Doctrine?

已尝试使用以下查询，但遗憾的是资源过于密集，因为这适用于相当多的列.

Using the following query has been tried, but is unfortunately too resource intensive as this applies to quite some columns.

SELECT t1.name, t1.field, t2.field2
FROM table1 t1 
  LEFT OUTER JOIN table2 t2 ON 
    (t2.field = t1.field AND t1.field IS NOT NULL) 
    OR (t2.field IS NULL AND t1.field IS NULL)

推荐答案

解决方案一:Doctrine Native SQL

实现此目的的一种方法是使用本机 MySQL 查询.这需要使用 Doctrine 的本机 SQL 功能并使用 ResultSetMapping.

我在两次执行本机 SQL 查询(使用不同的参数)时遇到一个问题，即第二个查询的结果集与第一个相同.GitHub 上的以下帖子 为我解决了这个问题.

I came across an issue when executing a Native SQL query twice (with different parameters), that the second query's result-set was the same as the first one. Following post on GitHub solved this for me.

使用以下连接条件将使用 MySQL 的内部优化器并将其视为 ref_or_null 连接类型

Using following join condition will use MySQL's internal optimizer and treat this as an ref_or_null join-type

SELECT a.*, b.* 
FROM a
   INNER JOIN b ON 
       a.column = b.column 
       OR (a.column IS NULL AND b.column IS NULL)

然后就可以在 DQL 中使用这个连接条件，这将在 SQL 中很好地转换以进行优化.

It is then possible to use this join-condition in DQL, which will be translated nicely in SQL to be optimized.

我编写了一个自定义的 DQL 函数，该函数在以下子句中进行了翻译:

I wrote a custom DQL-function that translated in the following clause:

SELECT a.*, b.* 
FROM a
   INNER JOIN b ON (a.column <=> b.column) = 1

不幸的是无法去掉这个子句的 =1 部分.这有效，但对我的查询造成主要性能影响:17s 与 0.5s，给出一些(非科学)指示.
所以我没有在这条路上走得更远.

Unfortunately is was not able to get rid of the = 1 part of this clause. This worked, but caused a major performance impact on my query: 17s vs 0.5s, to give some (non-scientific) indication.
So i didn't go further down that road.

这篇关于Doctrine DQL 连接可空列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！