Unexpected result of an arbitrary join #5964

New Issue

admin · 2026-01-22T15:23:17+01:00

admin commented

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

Originally created by @SenseException on GitHub (May 10, 2018).

Originally assigned to: @SenseException on GitHub.

Bug Report

I'm currently evaluating a few documentation bits and need a confirmation, that this issue is truly a bug.

The arbitrary join described at the bottom of the documentation here shows the possibility to make a join with different columns than the ones used in the associations of entities. While the DQL is working, I don't get the expected result. So the questions are

Is this really a bug and should be fixed by a PR?
Is this the expected behaviour?
Does the documentation lack some information and needs to be extended by a PR?

Q	A
BC Break	no
Version	2.6.1

Summary

While a regular join, that is supposed to hydrate the associated entities without lazy loading, already loads the collection (example is OneToMany), this doesn't seem to be the case for the arbitrary joins. Arbitrary joins still keep the collection in a lazy state. Loading the lazy state will still result loading all relations by the OneToMany configuration.

Current behavior

Currently, the associations in the collection are lazy despite having the relational entity listed in SELECT. After it gets accessed, the whole associations will be loaded, not the ones of the arbitrary join.

With the data and entities of "How to reproduce", one Foo entity will be returned with all three Bar relations.

Foo
  - bars (Collection)
     - Bar { id: 1, foo_id: 1, number: 1 }
     - Bar { id: 2, foo_id: 1, number: 2 }
     - Bar { id: 3, foo_id: 1, number: 3 }

How to reproduce

Entities

<?php

namespace App\Entity;

use Doctrine\Common\Collections\ArrayCollection;
use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Entity()
 */
class Foo
{
    /**
     * @ORM\Id
     * @ORM\GeneratedValue
     * @ORM\Column(type="integer")
     */
    private $id;

    /**
     * @ORM\Column(type="integer")
     */
    private $number;

    /**
     * @ORM\OneToMany(targetEntity="Bar", mappedBy="foo")
     */
    private $bars;

    public function __construct()
    {
        $this->bars = new ArrayCollection();
    }

    public function getId()
    {
        return $this->id;
    }

    /**
     * @return ArrayCollection|Bar[]
     */
    public function getBars()
    {
        return $this->bars;
    }

    /**
     * @return int
     */
    public function getNumber()
    {
        return $this->number;
    }
}

<?php

namespace App\Entity;

use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Entity()
 */
class Bar
{
    /**
     * @ORM\Id
     * @ORM\GeneratedValue
     * @ORM\Column(type="integer")
     */
    private $id;

    /**
     * @ORM\ManyToOne(targetEntity="Foo", inversedBy="bars")
     */
    private $foo;

    /**
     * @ORM\Column(type="integer")
     */
    private $number;

    public function getId()
    {
        return $this->id;
    }

    /**
     * @return Foo
     */
    public function getFoo()
    {
        return $this->foo;
    }

    /**
     * @return int
     */
    public function getNumber()
    {
        return $this->number;
    }
}

Data rows of the test

Table bar

+----+--------+--------+
| id | foo_id | number |
+----+--------+--------+
|  1 |      1 |      1 |
|  2 |      1 |      2 |
|  3 |      1 |      3 |
+----+--------+--------+

Table foo

+----+--------+
| id | number |
+----+--------+
|  1 |      1 |
+----+--------+

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

Expected behavior

The expected behaviour would be that only one Bar entity with the number 1 is in the result of the one existing Foo.

Foo
  - bars (Collection)
     - Bar { id: 1, foo_id: 1, number: 1 }

Originally created by @SenseException on GitHub (May 10, 2018). Originally assigned to: @SenseException on GitHub. ### Bug Report I'm currently evaluating a few documentation bits and need a confirmation, that this issue is truly a bug. The _arbitrary join_ described at the bottom of the documentation [here](https://www.doctrine-project.org/projects/doctrine-orm/en/latest/reference/dql-doctrine-query-language.html#dql-select-examples) shows the possibility to make a join with different columns than the ones used in the associations of entities. While the DQL is working, I don't get the expected result. So the questions are * Is this really a bug and should be fixed by a PR? * Is this the expected behaviour? * Does the documentation lack some information and needs to be extended by a PR? | Q | A |------------ | ------ | BC Break | no | Version | 2.6.1 #### Summary While a regular join, that is supposed to hydrate the associated entities without lazy loading, already loads the collection (example is OneToMany), this doesn't seem to be the case for the _arbitrary joins_. Arbitrary joins still keep the collection in a lazy state. Loading the lazy state will still result loading all relations by the OneToMany configuration. #### Current behavior Currently, the associations in the collection are lazy despite having the relational entity listed in `SELECT`. After it gets accessed, the whole associations will be loaded, not the ones of the arbitrary join. With the data and entities of "How to reproduce", one `Foo` entity will be returned with all three `Bar` relations. ``` Foo - bars (Collection) - Bar { id: 1, foo_id: 1, number: 1 } - Bar { id: 2, foo_id: 1, number: 2 } - Bar { id: 3, foo_id: 1, number: 3 } ``` #### How to reproduce ##### Entities ``` php <?php namespace App\Entity; use Doctrine\Common\Collections\ArrayCollection; use Doctrine\ORM\Mapping as ORM; /** * @ORM\Entity() */ class Foo { /** * @ORM\Id * @ORM\GeneratedValue * @ORM\Column(type="integer") */ private $id; /** * @ORM\Column(type="integer") */ private $number; /** * @ORM\OneToMany(targetEntity="Bar", mappedBy="foo") */ private $bars; public function __construct() { $this->bars = new ArrayCollection(); } public function getId() { return $this->id; } /** * @return ArrayCollection|Bar[] */ public function getBars() { return $this->bars; } /** * @return int */ public function getNumber() { return $this->number; } } ``` ``` php <?php namespace App\Entity; use Doctrine\ORM\Mapping as ORM; /** * @ORM\Entity() */ class Bar { /** * @ORM\Id * @ORM\GeneratedValue * @ORM\Column(type="integer") */ private $id; /** * @ORM\ManyToOne(targetEntity="Foo", inversedBy="bars") */ private $foo; /** * @ORM\Column(type="integer") */ private $number; public function getId() { return $this->id; } /** * @return Foo */ public function getFoo() { return $this->foo; } /** * @return int */ public function getNumber() { return $this->number; } } ``` ##### Data rows of the test ###### Table bar ``` +----+--------+--------+ | id | foo_id | number | +----+--------+--------+ | 1 | 1 | 1 | | 2 | 1 | 2 | | 3 | 1 | 3 | +----+--------+--------+ ``` ###### Table foo ``` +----+--------+ | id | number | +----+--------+ | 1 | 1 | +----+--------+ ``` ``` sql SELECT f, b FROM App\Entity\Foo f JOIN App\Entity\Bar b WITH f.number = b.number ``` #### Expected behavior The expected behaviour would be that only one `Bar` entity with the `number` 1 is in the result of the one existing `Foo`. ``` Foo - bars (Collection) - Bar { id: 1, foo_id: 1, number: 1 } ```

admin added the Documentation label 2026-01-22 15:23:17 +01:00

admin closed this issue

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

admin commented

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

@Ocramius commented on GitHub (May 10, 2018):

@SenseException can you clarify on the Expected behavior vs Actual behavior? I don't understand what the result you are getting looks like. IF you can make an example like Table foo, that would be best.

@Ocramius commented on GitHub (May 10, 2018): @SenseException can you clarify on the Expected behavior vs Actual behavior? I don't understand what the result you are getting looks like. IF you can make an example like `Table foo`, that would be best.

admin commented

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

@SenseException commented on GitHub (May 10, 2018):

I've extended the description with a structure containing the current and expected amount of Bar entities. The result is based on the table rows from "Table foo" and "Table bar". Does this make it more understandable?

@SenseException commented on GitHub (May 10, 2018): I've extended the description with a structure containing the current and expected amount of `Bar` entities. The result is based on the table rows from "Table foo" and "Table bar". Does this make it more understandable?

admin commented

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

@Ocramius commented on GitHub (May 10, 2018):

@SenseException I see. The "actual" behavior is correct though, as an arbitrary join has no way to know that you are fetch-joining f.bars.

Instead, I'd expect a result like following (I know it's not like this, but this is what I got off the top of my head):

+------+------+
| f    | b    |
+------+------+
| Foo1 | Bar1 |
| Foo1 | Bar2 |
| Foo1 | Bar3 |
+------+------+

@Ocramius commented on GitHub (May 10, 2018): @SenseException I see. The "actual" behavior is correct though, as an arbitrary join has no way to know that you are fetch-joining `f.bars`. Instead, I'd expect a result like following (I know it's not like this, but this is what I got off the top of my head): ``` +------+------+ | f | b | +------+------+ | Foo1 | Bar1 | | Foo1 | Bar2 | | Foo1 | Bar3 | +------+------+ ```

admin commented

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

@SenseException commented on GitHub (May 14, 2018):

Then it's not a bug but missing documentation. I suggest to update the documentation for the arbitrary join:

The arbitrary join doesn't affect the output of the relations for an entity
The result of such a join has mixed entities. In this issue's example it would be a result of Foo and to the join fitting Bar entities. In the documentation's example User and Blacklist entities.

This would highlight more that DQL isn't some form of SQL like the introduction of DQL mentions in https://www.doctrine-project.org/projects/doctrine-orm/en/2.7/reference/dql-doctrine-query-language.html and makes the arbitrary join more understandable.

@SenseException commented on GitHub (May 14, 2018): Then it's not a bug but missing documentation. I suggest to update the documentation for the arbitrary join: * The arbitrary join doesn't affect the output of the relations for an entity * The result of such a join has mixed entities. In this issue's example it would be a result of Foo and to the join fitting Bar entities. In the documentation's example User and Blacklist entities. This would highlight more that DQL isn't some form of SQL like the introduction of DQL mentions in https://www.doctrine-project.org/projects/doctrine-orm/en/2.7/reference/dql-doctrine-query-language.html and makes the _arbitrary join_ more understandable.

admin commented

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

@SenseException commented on GitHub (Jun 10, 2018):

I've created a PR for extending the documentation. Please feel free to take a look at it.

@SenseException commented on GitHub (Jun 10, 2018): I've created a PR for extending the documentation. Please feel free to take a look at it.

admin commented

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

@Ocramius commented on GitHub (Jun 11, 2018):

Handled in #7255

@Ocramius commented on GitHub (Jun 11, 2018): Handled in #7255

admin referenced this issue

2026-01-22 16:05:27 +01:00