CODEWITHSUNDEEP
phpdoctrine-ormdoctrine
mayconfsbrito
mayconfsbrito

Reputation: 2433

What is the difference between WITH and ON in Doctrine Join condition type?

I'm searching for a better explanation about the difference between WITH and ON condition type for the doctrine clausure.

I'm finding the documentation about this very poor and I'm still in doubt.

Upvotes: 16

Views: 7939

Answers (3)

GuyPaddock
GuyPaddock

Reputation: 2517

EDIT: This information may only apply to Doctrine 1.x. YMMV for later versions.

I didn't see any answers here that made intuitive sense, so I re-read the docs a few times, had some back and forths with ChatGPT about it, and came away with this understanding:

ON in Join Condition

The ON keyword is used to specify a custom join condition that overrides the default automatic condition that Doctrine generates. You use ON to define how the tables are joined, similar to regular SQL, and this changes how the tables are matched together in that query.

For example:

$queryBuilder
  ->select('a')
  ->from(Fruit::class, 'f')
  ->leftJoin(Aisle::class, 'a', 'ON', 'o.type = "produce"')
  ->where('f.name = "Pear"')

Should be equivalent to:

SELECT a.*
FROM fruit f
LEFT JOIN aisle a ON f.type = "produce"
WHERE f.name = "Pear";

WITH as Additional Filtering:

The WITH keyword is used to add custom filtering conditions to the join. It's used to filter the results of the join after the join condition has been applied. The conditions specified with WITH are appended to the automatically generated condition.

For example:

$queryBuilder
  ->select('a')
  ->from(Fruit::class, 'f')
  ->leftJoin(Aisle::class, 'a', 'WITH', 'o.type = "produce"')
  ->where('f.name = "Pear"')

Should be equivalent to:

SELECT a.*
FROM fruit f
LEFT JOIN aisle a ON a.id = f.aisle_id AND f.type = "produce"
WHERE f.name = "Pear";

Upvotes: 5

majid noureddine
majid noureddine

Reputation: 27

Using the ON condition type requires having a relation between joined entities.
WITH can be used if no relation exists between entities, but we should satisfy some conditions using the other side of join.
In some contexts, where ON and WITH condition types are possible, I think that ON is interpreted faster and executed by almost DBMS.

Upvotes: 1

SBH
SBH

Reputation: 1918

In my opinion it's kind of a left over from doctrine 1.

Back then ON was used to redefine the join conditions of relations, whereas WITH was used to add more join conditions to the default one.

Now in doctrine 2 I have never seen a situation where ON can be used. In fact using ON always ends up in an exception saying you should use WITH.

Right now WITH can be used to add join conditions to the default one defined in a relation or, if no relation exists between two entities, define the join condition.

Upvotes: 29

Related Questions