Doctrine DQL join on nullable columns

Question

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

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

I can achieve this in plain MySQL with the null-safe operator.

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

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)

Answer 1

Solution 1 : Doctrine Native SQL

One way to achieve this is by using native MySQL queries. This require the use of Doctrine's Native SQL feature and mapping the results of the query using a ResultSetMapping.

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.

Solution 2 : Using MySQL's internal optimizer

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)

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

Solution 3 : Write Custom DQL function

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

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.