Incorrect SQL generated for unidirectional many-to-many indexed extra lazy associations #6590

New Issue

admin · 2026-01-22T15:35:27+01:00

admin commented

2026-01-22 15:35:27 +01:00

Originally created by @gjdanis on GitHub (Dec 18, 2020).

I believe there's a bug in unidirectional many-to-many indexed extra lazy associations.

Specifically making the following change to tests/Doctrine/Tests/Models/ECommerce/ECommerceCart.php

    /**
        Update fetch to be EXTRA_LAZY and add indexing

     * @ManyToMany(targetEntity="ECommerceProduct", cascade={"persist"}, fetch="EXTRA_LAZY", indexBy="name")
     * @JoinTable(name="ecommerce_carts_products",
            joinColumns={@JoinColumn(name="cart_id", referencedColumnName="id")},
            inverseJoinColumns={@JoinColumn(name="product_id", referencedColumnName="id")})
     */
    private $products;

And modifying a test to force a SQL query of the association:

    public function testGetProductFromDatabase() {
        $products = $this->firstCart->getProducts();

        $firstProduct = $products['Doctrine 1.x Manual'];
        self::assertNotNull($firstProduct);
    }

Produces the following invalid SQL:

Exception : [Doctrine\DBAL\Exception\SyntaxErrorException] An exception occurred while executing 'SELECT t0.id AS id_1, t0.name AS name_2, t0.shipping_id AS shipping_id_3 FROM ecommerce_products t0 INNER JOIN ecommerce_carts_products ON t0.id = ecommerce_carts_products.product_id WHERE  = ? AND t0.name = ? LIMIT 1' with params [1, "Doctrine 1.x Manual"]:

SQLSTATE[HY000]: General error: 1 near "=": syntax error

I believe a fix might be found in this PR: https://github.com/doctrine/orm/pull/8396

Currently it doesn't seem like the case where $mapping['inversedBy'] is null is accounted for. I'm not that familiar with Doctrine's internals but getting the inverse column from the association's mapping seemed to produce the correct SQL.

        $persister = $this->uow->getEntityPersister($mapping['targetEntity']);
        if ($mapping['isOwningSide']) {
            if ($mapping['inversedBy'] === null) {
                // unidirectional case
                foreach ($mapping['joinTableColumns'] as $columnName) {
                    $targetColumn = $mapping['relationToTargetKeyColumns'][$columnName] ?? null;
                    if ($targetColumn !== null) {
                        $mappedKey = $targetColumn;
                        break;
                    }
                }
            } else {
                $mappedKey = $mapping['inversedBy'];
            }
        } else {
            $mappedKey = $mapping['mappedBy'];
        }

Originally created by @gjdanis on GitHub (Dec 18, 2020). I believe there's a bug in unidirectional many-to-many indexed extra lazy associations. Specifically making the following change to `tests/Doctrine/Tests/Models/ECommerce/ECommerceCart.php` ``` /** Update fetch to be EXTRA_LAZY and add indexing * @ManyToMany(targetEntity="ECommerceProduct", cascade={"persist"}, fetch="EXTRA_LAZY", indexBy="name") * @JoinTable(name="ecommerce_carts_products", joinColumns={@JoinColumn(name="cart_id", referencedColumnName="id")}, inverseJoinColumns={@JoinColumn(name="product_id", referencedColumnName="id")}) */ private $products; ``` And modifying a test to force a SQL query of the association: ``` public function testGetProductFromDatabase() { $products = $this->firstCart->getProducts(); $firstProduct = $products['Doctrine 1.x Manual']; self::assertNotNull($firstProduct); } ``` Produces the following invalid SQL: ``` Exception : [Doctrine\DBAL\Exception\SyntaxErrorException] An exception occurred while executing 'SELECT t0.id AS id_1, t0.name AS name_2, t0.shipping_id AS shipping_id_3 FROM ecommerce_products t0 INNER JOIN ecommerce_carts_products ON t0.id = ecommerce_carts_products.product_id WHERE = ? AND t0.name = ? LIMIT 1' with params [1, "Doctrine 1.x Manual"]: SQLSTATE[HY000]: General error: 1 near "=": syntax error ``` I believe a fix might be found in this PR: https://github.com/doctrine/orm/pull/8396 Currently it doesn't seem like the case where `$mapping['inversedBy']` is null is accounted for. I'm not that familiar with Doctrine's internals but getting the inverse column from the association's mapping seemed to produce the correct SQL. ``` $persister = $this->uow->getEntityPersister($mapping['targetEntity']); if ($mapping['isOwningSide']) { if ($mapping['inversedBy'] === null) { // unidirectional case foreach ($mapping['joinTableColumns'] as $columnName) { $targetColumn = $mapping['relationToTargetKeyColumns'][$columnName] ?? null; if ($targetColumn !== null) { $mappedKey = $targetColumn; break; } } } else { $mappedKey = $mapping['inversedBy']; } } else { $mappedKey = $mapping['mappedBy']; } ```

admin commented

2026-01-22 15:35:27 +01:00

@gjdanis commented on GitHub (Dec 22, 2020):

@beberlei would you be able to comment on whether this is a known problem and whether or not the proposed solution is in the right direction? Happy to contribute a PR but would be helpful to know if the problem/solution could be confirmed.

@gjdanis commented on GitHub (Dec 22, 2020): @beberlei would you be able to comment on whether this is a known problem and whether or not the proposed solution is in the right direction? Happy to contribute a PR but would be helpful to know if the problem/solution could be confirmed.

admin referenced this issue

2026-01-22 16:06:16 +01:00

[PR #6590] The collection is deleted twice at the post flush event #10051

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#6590