Doctrine2: Arbitrary join and single table inherit

Question

Note: This is an ORM limitation reported on the project's issue tracker

I'm facing an issue building a DQL query using the arbitrary join syntax introduced in Doctrine 2.3 on an entity class which is the root of a hierarchy.

Given these classes:

A - no inheritance

B1 - abstract, root of a hierarchy, discriminator column is named 'type'

I setup a query builder like this:

$qb->select('a.id AS idA, b.id AS idB')
    ->from('\Entity\A', 'a')
    ->leftJoin('\Entity\B1', 'b', \Doctrine\ORM\Query\Expr\Join::WITH, 'a.something=b.something');

And the SQL Doctrine generates is something like this:

SELECT a.id, b.id FROM a LEFT JOIN b ON (a.something=b.something) WHERE b.type IN ('1', '2', '3')

The problems is that the where makes the left join useless.

Is there a way to force the condition on the discriminator column to be placed in the join? At least that would make it...

Should I fill a bug report?

Answer 1

Have you tried an inner join instead a left join? The query that you need probably could

SELECT a.id, 
       b.id 
FROM   a 
       INNER JOIN b 
               ON ( a.something = b.something ) 
WHERE  b.type IN ( '1', '2', '3' ) 

You can get that changing the function leftJoin by join, or creating the query with the createQuery method for customs queries.

Answer 2

This bug is fixed in Doctrine 2.4

https://github.com/doctrine/doctrine2/issues/2934

Answer 3

try this

SELECT a.id, 
       b.id 
FROM   a 
       INNER JOIN b ON ( a.something = b.something ) and b.type IN ( '1', '2', '3' )