0

Here is the SQL equivalent of what I expect to get from Doctrine:

SELECT c.* FROM comments c
  LEFT JOIN articles a
    ON a.id = c.articles_id OR a.translation = c.articles_id
WHERE c.published = 1 AND c.language = a.language

The problem is that I cannot make Doctrine to generate the JOIN operation with OR as it is supposed to be. If we execute query from the following QueryBuilder object:

$qb->select('c')
   ->from('Project:Comment', 'c')
   ->leftJoin('c.article', 'a', 'WITH', 'a = c.article OR a.translation = c.article')
   ->where('c.published = true AND c.language = a.language');

we receive the following SQL statement:

SELECT
  ...
FROM comments c0_ 
LEFT JOIN articles a0_ ON c0_.articles_id = a0_.id 
AND (
  a0_.id = c0_.articles_id OR
  a0_.translation = c0_.profiles_id
)
WHERE c0_.published = 1 AND c0_.language = a0_.language

which is obviously not the same as the initial query, as WITH operator seems to add additional conditions to the basic one instead of replacing the whole condition.

Is there any way to force Doctrine to output exactly what I need? I know that I may use native SQL but I doubt that it will be as convenient as QueryBuilder. Maybe there is a way to extend Doctrine with normal JOIN ON implementation instead of this odd JOIN WITH?

VisioN
  • 143,310
  • 32
  • 282
  • 281

1 Answers1

1

Doctrine doesn't implement this because it is (as far as I understand at least) not considered optimized enough for SQL.

See this SO post for precisions.

What you intend to do could appearantly be done using Union and other types of Join.

Community
  • 1
  • 1
Jivan
  • 21,522
  • 15
  • 80
  • 131
  • Thanks for the answer. It seems that neither `Join..OR` nor `Union` can be considered optimised enough to be implemented in Doctrine. Does that mean that there are no other options except native SQL? – VisioN Feb 04 '14 at 08:22
  • @VisioN I do think so. In a general way, I think native SQL is more optimized 90% of the time, the only condition is that one's actually knows how to write proper optimized SQL (which is far from being my case). – Jivan Feb 04 '14 at 09:28