Specify `ON` column for `leftJoin()`

[AntoniusGolly]

I want to do a query like this

SELECT 
	pm.id AS projectMemberId,	
	a.email,	
	c.id,
	c.account_id
	
FROM project_member pm

INNER JOIN account a ON pm.account_id = a.id

LEFT JOIN contact c ON a.email = c.email

The point is, the LEFT JOIN is not done on the id columns of account and contact. I am aware of WITH to specify further conditions. But in my case, I don't have further conditions to the join on id, but I only have the condition on join on email.

It seems very obvious to do but I can't find the solution.

[SenseException]

@AntoniusGolly Your query seems to fetch scalar values from the database. If these are the 4 columns/properties you try to fetch, why are you using DQL instead of SQL?

[AntoniusGolly]

Sure, I can use SQL. Is the answer that this isn't possible in Doctrine?

[SenseException]

An ORM is used to return objects of the data from the source database. In most cases you have no advantages when you use DQL to fetch scalar values. That's why I was asking why SQL was used.

https://www.doctrine-project.org/projects/doctrine-orm/en/latest/reference/dql-doctrine-query-language.html#dql-select-examples

The arbitrary join is supposed to cover an use case similar to that. A DQL query like

SELECT f, b FROM App\Entity\Foo f JOIN App\Entity\Bar b WITH f.name = b.name

could return results you're looking for, but as mentioned before, if you don't want to fetch the data in form of (entity) objects, you should use SQL if there's no other reason for DQL.

[piotrkardasz]

Sure, I can use SQL. Is the answer that this isn't possible in Doctrine?

It is possible. You can try native query to map entity
https://www.doctrine-project.org/projects/doctrine-orm/en/latest/reference/native-sql.html#resultsetmappingbuilder