Specify ON column for leftJoin() #6341

New Issue

admin · 2026-01-22T15:31:21+01:00

admin commented

2026-01-22 15:31:21 +01:00

Originally created by @AntoniusGolly on GitHub (Nov 6, 2019).

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.

Originally created by @AntoniusGolly on GitHub (Nov 6, 2019). 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.

admin added the Question label 2026-01-22 15:31:21 +01:00

admin closed this issue

2026-01-22 15:31:21 +01:00

admin commented

2026-01-22 15:31:22 +01:00

@SenseException commented on GitHub (Nov 11, 2019):

@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?

@SenseException commented on GitHub (Nov 11, 2019): @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?

admin commented

2026-01-22 15:31:23 +01:00

@AntoniusGolly commented on GitHub (Nov 12, 2019):

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

@AntoniusGolly commented on GitHub (Nov 12, 2019): Sure, I can use SQL. Is the answer that this isn't possible in Doctrine?

admin commented

2026-01-22 15:31:23 +01:00

@SenseException commented on GitHub (Nov 14, 2019):

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.

@SenseException commented on GitHub (Nov 14, 2019): 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 ``` sql 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.

admin commented

2026-01-22 15:31:23 +01:00

@piotrkardasz commented on GitHub (Nov 22, 2019):

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

@piotrkardasz commented on GitHub (Nov 22, 2019): > > > 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

Sign in to join this conversation.

Branches Tags

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: doctrine/archived-orm#6341

Specify ON column for leftJoin() #6341

Specify `ON` column for `leftJoin()` #6341