For starters, it is NOT a duplicate of Doctrine 2 JOIN ON error. I am indeed getting Expected end of string, got 'ON' but using WITH won't solve my case.
The problem there is similar but different from mine. I don't need to add a condition to my JOIN, I need to substitute the default condition with a different one.
Let's say, we have 2 hypthetical tables: album and artist, with an artist_id FK pointing from album to artist. In my case, I don't want to join artists with their albums. I want to list artists joined with unrelated albums using some arbitrary condition. So each artist will be joined with the exact same small set of albums. Believe me, in my case it does make sense - I don't want to describe it fully because it is too complex and out of the scope of my question.
SELECT * FROM artist
LEFT JOIN album ON album.some_unrelated_property = 'foo'
The example above is raw SQL (I'm using PostgreSQL) and works perfectly fine in this form.
In my code I'm using query builder (hard to avoid it, because my query is way more complex and built step by step by a series of functions). The line that causes an error is this:
$qb->leftJoin('artist.albums', 'aa', Join::ON, 'aa.someUnrelatedProperty = "foo"');
In Doctrine I'm getting the dreaded Expected end of string, got 'ON'. When I use WITH instead of ON it works, but as expected, it appends the standard join condition by artist_id which I do not want.
What's even more confusing for me, in this post: What is the difference between JOIN ON and JOIN WITH in Doctrine2? which explains a difference between ON and WITH in DQL, somebody uses an example equivalent to mine as a correct use of ON in DQL:
Case Two
DQL
FROM Album a LEFT JOIN a.Track t ON t.status = 1Will translate in SQL
FROM Album a LEFT JOIN Track t ON t.status = 1
What am I missing here? Is it possible to achieve what I want at all, using DQL? If not, what the hell is the reason for ON to exist in DQL when there's also WITH which works in more standard cases?
