doctrine/archived-orm
1
0
Fork 0
mirror of https://github.com/doctrine/orm.git synced 2026-03-24 06:52:09 +01:00
Code Issues 5.3k Packages Projects Releases 100 Wiki Activity

DDC-698: Many-To-Many Bidirectional #860

New Issue
Closed
opened 2026-01-22 12:52:57 +01:00 by admin · 4 comments
admin commented 2026-01-22 12:52:57 +01:00
Owner
Copy Link

Originally created by @doctrinebot on GitHub (Jul 19, 2010).

Originally assigned to: @guilhermeblanco on GitHub.

Jira issue originally created by user vigor_bg:

When calling many to many bidirectional from the mapped by side returns SQL error

$qb->add('select', 'p, r')
           ->add('from', 'Privileges p')
           ->leftJoin('p.roles', 'r');

        $privileges = $qb->getQuery()
                         ->getArrayResult();

Notice: Undefined index: privilegeID in /home/vis/projects/CRM/trunk/library/Doctrine/ORM/Query/SqlWalker.php on line 758 
Notice: Undefined index: in /home/vis/projects/CRM/trunk/library/Doctrine/ORM/Mapping/ClassMetadata.php on line 220 
Notice: Undefined index: roleID in /home/vis/projects/CRM/trunk/library/Doctrine/ORM/Query/SqlWalker.php on line 782 
Notice: Undefined index: in /home/vis/projects/CRM/trunk/library/Doctrine/ORM/Mapping/ClassMetadata.php on line 220 
SELECT p0*.privilegeID AS privilegeID0, p0_.name AS name1, r1_.roleID AS roleID2, r1_.name AS name3, r1_.shortName AS shortName4 FROM Privileges p0_ LEFT JOIN RolePrivileges r2_ ON p0_. = r2_.privilegeID LEFT JOIN Roles r1_ ON r1_. = r2*.roleID

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= r2*.privilegeID LEFT JOIN Roles r1_ ON r1_. = r2*.roleID' at line 1

And here are my entityes:

/****
 *
 * @Table(name="Roles")
 * @entity
 */

class Role 
{
    /****
     *  @Id  @Column(name="roleID", type="integer")
     *  @GeneratedValue(strategy="AUTO")
     *
     */
    protected $roleID;

    /****
     * @Column(name="name", type="string", length="45")
     *
     *
     */
    protected $name;

    /****
     * @Column(name="shortName", type="string", length="45")
     *
     *
     */
    protected $shortName;



    /****
     * @ManyToMany(targetEntity="Users*Model_Entity*Privileges", inversedBy="roles")
     * @JoinTable(name="RolePrivileges",
     *      joinColumns={@JoinColumn(name="roleID", referencedColumnName="roleID")},
     *      inverseJoinColumns={@JoinColumn(name="privilegeID", referencedColumnName="privilegeID")}
     *      )
     */
    protected $privilege;

}


/****
 *
 * @Table(name="Privileges")
 * @entity(repositoryClass="Users*Model*UserRepo")
 */

class Privileges
{
    /****
     *  @Id  @Column(name="privilegeID", type="integer")
     *  @GeneratedValue(strategy="AUTO")
     *
     */
    protected $privilegeID;

    /****
     * @Column(name="name", type="string", length="45")
     *
     *
     */
    protected $name;

    /****
     * @ManyToMany(targetEntity="Users*Model_Entity*Role", mappedBy="privilege")
     */
    protected $roles;
}
Originally created by @doctrinebot on GitHub (Jul 19, 2010). Originally assigned to: @guilhermeblanco on GitHub. Jira issue originally created by user vigor_bg: When calling many to many bidirectional from the mapped by side returns SQL error ``` $qb->add('select', 'p, r') ->add('from', 'Privileges p') ->leftJoin('p.roles', 'r'); $privileges = $qb->getQuery() ->getArrayResult(); ``` ``` Notice: Undefined index: privilegeID in /home/vis/projects/CRM/trunk/library/Doctrine/ORM/Query/SqlWalker.php on line 758 Notice: Undefined index: in /home/vis/projects/CRM/trunk/library/Doctrine/ORM/Mapping/ClassMetadata.php on line 220 Notice: Undefined index: roleID in /home/vis/projects/CRM/trunk/library/Doctrine/ORM/Query/SqlWalker.php on line 782 Notice: Undefined index: in /home/vis/projects/CRM/trunk/library/Doctrine/ORM/Mapping/ClassMetadata.php on line 220 SELECT p0*.privilegeID AS privilegeID0, p0_.name AS name1, r1_.roleID AS roleID2, r1_.name AS name3, r1_.shortName AS shortName4 FROM Privileges p0_ LEFT JOIN RolePrivileges r2_ ON p0_. = r2_.privilegeID LEFT JOIN Roles r1_ ON r1_. = r2*.roleID ``` ``` SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= r2*.privilegeID LEFT JOIN Roles r1_ ON r1_. = r2*.roleID' at line 1 ``` And here are my entityes: ``` /**** * * @Table(name="Roles") * @entity */ class Role { /**** * @Id @Column(name="roleID", type="integer") * @GeneratedValue(strategy="AUTO") * */ protected $roleID; /**** * @Column(name="name", type="string", length="45") * * */ protected $name; /**** * @Column(name="shortName", type="string", length="45") * * */ protected $shortName; /**** * @ManyToMany(targetEntity="Users*Model_Entity*Privileges", inversedBy="roles") * @JoinTable(name="RolePrivileges", * joinColumns={@JoinColumn(name="roleID", referencedColumnName="roleID")}, * inverseJoinColumns={@JoinColumn(name="privilegeID", referencedColumnName="privilegeID")} * ) */ protected $privilege; } /**** * * @Table(name="Privileges") * @entity(repositoryClass="Users*Model*UserRepo") */ class Privileges { /**** * @Id @Column(name="privilegeID", type="integer") * @GeneratedValue(strategy="AUTO") * */ protected $privilegeID; /**** * @Column(name="name", type="string", length="45") * * */ protected $name; /**** * @ManyToMany(targetEntity="Users*Model_Entity*Role", mappedBy="privilege") */ protected $roles; } ```
admin added the Bug label 2026-01-22 12:52:57 +01:00
admin closed this issue 2026-01-22 12:52:58 +01:00
admin commented 2026-01-22 12:53:00 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Jul 19, 2010):

Comment created by vigor_bg:

I hope that is all the info you need. By the way i did fix it for my self but probably it will be nice to be done for the next version

Here is what i did hopefully it will be useful for you. :)
I am using 2.0.0BETA2 Package
Old code in SqlWalker:

from line 739:

} else if ($assoc->isManyToMany()) {
            // Join relation table
            $joinTable = $assoc->joinTable;
            $joinTableAlias = $this->getSqlTableAlias($joinTable['name'], $joinedDqlAlias);
            $sql .= $assoc->getQuotedJoinTableName($this->_platform) . ' ' . $joinTableAlias . ' ON ';

            $first = true;
            if ($relation->isOwningSide) {
                foreach ($assoc->relationToSourceKeyColumns as $relationColumn => $sourceColumn) {
                    if ( ! $first) $sql .= ' AND '; else $first = false;

                    $sql .= $sourceTableAlias . '.' . $sourceClass->getQuotedColumnName($sourceClass->fieldNames[$sourceColumn], $this->_platform)
                          . ' = '
                          . $joinTableAlias . '.' . $relationColumn;
                }
            } else {
                foreach ($assoc->relationToTargetKeyColumns as $relationColumn => $targetColumn) {
                    if ( ! $first) $sql .= ' AND '; else $first = false;

                    $sql .= $sourceTableAlias . '.' . $targetClass->getQuotedColumnName($targetClass->fieldNames[$targetColumn], $this->_platform)
                          . ' = '
                          . $joinTableAlias . '.' . $relationColumn;
                }
            }

            // Join target table
            $sql .= ($joinType == AST\Join::JOIN*TYPE_LEFT || $joinType == AST\Join::JOIN_TYPE*LEFTOUTER)
                ? ' LEFT JOIN ' : ' INNER JOIN ';
            $sql .= $targetTableName . ' ' . $targetTableAlias . ' ON ';

            $first = true;
            if ($relation->isOwningSide) {
                foreach ($assoc->relationToTargetKeyColumns as $relationColumn => $targetColumn) {
                    if ( ! $first) $sql .= ' AND '; else $first = false;

                    $sql .= $targetTableAlias . '.' . $targetClass->getQuotedColumnName($targetClass->fieldNames[$targetColumn], $this->_platform)
                          . ' = '
                          . $joinTableAlias . '.' . $relationColumn;
                }
            } else {
                foreach ($assoc->relationToSourceKeyColumns as $relationColumn => $sourceColumn) {
                    if ( ! $first) $sql .= ' AND '; else $first = false;

                    $sql .= $targetTableAlias . '.' . $sourceClass->getQuotedColumnName($sourceClass->fieldNames[$sourceColumn], $this->_platform)
                          . ' = '
                          . $joinTableAlias . '.' . $relationColumn;
                }
            }
        }

new code :

} else if ($assoc->isManyToMany()) {

            // Join relation table
            $joinTable = $assoc->joinTable;
            $joinTableAlias = $this->getSqlTableAlias($joinTable['name'], $joinedDqlAlias);
            $sql .= $assoc->getQuotedJoinTableName($this->_platform) . ' ' . $joinTableAlias . ' ON ';

            // fix done to be able to call many to many relation from the inverse said
            $first = true;
            if ($relation->isOwningSide) {
                foreach ($assoc->relationToSourceKeyColumns as $relationColumn => $sourceColumn) {
                    if ( ! $first) $sql .= ' AND '; else $first = false;

                    $sql .= $sourceTableAlias . '.' . $sourceClass->getQuotedColumnName($sourceClass->fieldNames[$sourceColumn], $this->_platform)
                          . ' = '
                          . $joinTableAlias . '.' . $relationColumn;
                }
            } else {
                foreach ($assoc->relationToTargetKeyColumns as $relationColumn => $targetColumn) {
                    if ( ! $first) $sql .= ' AND '; else $first = false;

                    $sql .= $sourceTableAlias . '.' . $sourceClass->getQuotedColumnName($sourceClass->fieldNames[$targetColumn], $this->_platform)
                          . ' = '
                          . $joinTableAlias . '.' . $relationColumn;
                }
            }

            // Join target table
            $sql .= ($joinType == AST\Join::JOIN*TYPE_LEFT || $joinType == AST\Join::JOIN_TYPE*LEFTOUTER)
                ? ' LEFT JOIN ' : ' INNER JOIN ';
            $sql .= $targetTableName . ' ' . $targetTableAlias . ' ON ';

            // fix done to be able to call many to many relation from the inverse said
            $first = true;
            if ($relation->isOwningSide) {
                foreach ($assoc->relationToTargetKeyColumns as $relationColumn => $targetColumn) {
                    if ( ! $first) $sql .= ' AND '; else $first = false;

                    $sql .= $targetTableAlias . '.' . $targetClass->getQuotedColumnName($targetClass->fieldNames[$targetColumn], $this->_platform)
                          . ' = '
                          . $joinTableAlias . '.' . $relationColumn;
                }
            } else {
                foreach ($assoc->relationToSourceKeyColumns as $relationColumn => $sourceColumn) {
                    if ( ! $first) $sql .= ' AND '; else $first = false;

                    $sql .= $targetTableAlias . '.' . $targetClass->getQuotedColumnName($targetClass->fieldNames[$sourceColumn], $this->_platform)
                          . ' = '
                          . $joinTableAlias . '.' . $relationColumn;
                }
            }
        }
@doctrinebot commented on GitHub (Jul 19, 2010): Comment created by vigor_bg: I hope that is all the info you need. By the way i did fix it for my self but probably it will be nice to be done for the next version Here is what i did hopefully it will be useful for you. :) I am using 2.0.0BETA2 Package Old code in SqlWalker: from line 739: ``` } else if ($assoc->isManyToMany()) { // Join relation table $joinTable = $assoc->joinTable; $joinTableAlias = $this->getSqlTableAlias($joinTable['name'], $joinedDqlAlias); $sql .= $assoc->getQuotedJoinTableName($this->_platform) . ' ' . $joinTableAlias . ' ON '; $first = true; if ($relation->isOwningSide) { foreach ($assoc->relationToSourceKeyColumns as $relationColumn => $sourceColumn) { if ( ! $first) $sql .= ' AND '; else $first = false; $sql .= $sourceTableAlias . '.' . $sourceClass->getQuotedColumnName($sourceClass->fieldNames[$sourceColumn], $this->_platform) . ' = ' . $joinTableAlias . '.' . $relationColumn; } } else { foreach ($assoc->relationToTargetKeyColumns as $relationColumn => $targetColumn) { if ( ! $first) $sql .= ' AND '; else $first = false; $sql .= $sourceTableAlias . '.' . $targetClass->getQuotedColumnName($targetClass->fieldNames[$targetColumn], $this->_platform) . ' = ' . $joinTableAlias . '.' . $relationColumn; } } // Join target table $sql .= ($joinType == AST\Join::JOIN*TYPE_LEFT || $joinType == AST\Join::JOIN_TYPE*LEFTOUTER) ? ' LEFT JOIN ' : ' INNER JOIN '; $sql .= $targetTableName . ' ' . $targetTableAlias . ' ON '; $first = true; if ($relation->isOwningSide) { foreach ($assoc->relationToTargetKeyColumns as $relationColumn => $targetColumn) { if ( ! $first) $sql .= ' AND '; else $first = false; $sql .= $targetTableAlias . '.' . $targetClass->getQuotedColumnName($targetClass->fieldNames[$targetColumn], $this->_platform) . ' = ' . $joinTableAlias . '.' . $relationColumn; } } else { foreach ($assoc->relationToSourceKeyColumns as $relationColumn => $sourceColumn) { if ( ! $first) $sql .= ' AND '; else $first = false; $sql .= $targetTableAlias . '.' . $sourceClass->getQuotedColumnName($sourceClass->fieldNames[$sourceColumn], $this->_platform) . ' = ' . $joinTableAlias . '.' . $relationColumn; } } } ``` new code : ``` } else if ($assoc->isManyToMany()) { // Join relation table $joinTable = $assoc->joinTable; $joinTableAlias = $this->getSqlTableAlias($joinTable['name'], $joinedDqlAlias); $sql .= $assoc->getQuotedJoinTableName($this->_platform) . ' ' . $joinTableAlias . ' ON '; // fix done to be able to call many to many relation from the inverse said $first = true; if ($relation->isOwningSide) { foreach ($assoc->relationToSourceKeyColumns as $relationColumn => $sourceColumn) { if ( ! $first) $sql .= ' AND '; else $first = false; $sql .= $sourceTableAlias . '.' . $sourceClass->getQuotedColumnName($sourceClass->fieldNames[$sourceColumn], $this->_platform) . ' = ' . $joinTableAlias . '.' . $relationColumn; } } else { foreach ($assoc->relationToTargetKeyColumns as $relationColumn => $targetColumn) { if ( ! $first) $sql .= ' AND '; else $first = false; $sql .= $sourceTableAlias . '.' . $sourceClass->getQuotedColumnName($sourceClass->fieldNames[$targetColumn], $this->_platform) . ' = ' . $joinTableAlias . '.' . $relationColumn; } } // Join target table $sql .= ($joinType == AST\Join::JOIN*TYPE_LEFT || $joinType == AST\Join::JOIN_TYPE*LEFTOUTER) ? ' LEFT JOIN ' : ' INNER JOIN '; $sql .= $targetTableName . ' ' . $targetTableAlias . ' ON '; // fix done to be able to call many to many relation from the inverse said $first = true; if ($relation->isOwningSide) { foreach ($assoc->relationToTargetKeyColumns as $relationColumn => $targetColumn) { if ( ! $first) $sql .= ' AND '; else $first = false; $sql .= $targetTableAlias . '.' . $targetClass->getQuotedColumnName($targetClass->fieldNames[$targetColumn], $this->_platform) . ' = ' . $joinTableAlias . '.' . $relationColumn; } } else { foreach ($assoc->relationToSourceKeyColumns as $relationColumn => $sourceColumn) { if ( ! $first) $sql .= ' AND '; else $first = false; $sql .= $targetTableAlias . '.' . $targetClass->getQuotedColumnName($targetClass->fieldNames[$sourceColumn], $this->_platform) . ' = ' . $joinTableAlias . '.' . $relationColumn; } } } ```
admin commented 2026-01-22 12:53:01 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Jul 21, 2010):

Comment created by @beberlei:

Formatted

@doctrinebot commented on GitHub (Jul 21, 2010): Comment created by @beberlei: Formatted
admin commented 2026-01-22 12:53:02 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Jul 26, 2010):

Comment created by @guilhermeblanco:

Fixed. Thanks for report and patch. I added a test case to improve our coverage suppport.

@doctrinebot commented on GitHub (Jul 26, 2010): Comment created by @guilhermeblanco: Fixed. Thanks for report and patch. I added a test case to improve our coverage suppport.
admin commented 2026-01-22 12:53:03 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Jul 26, 2010):

Issue was closed with resolution "Fixed"

@doctrinebot commented on GitHub (Jul 26, 2010): Issue was closed with resolution "Fixed"
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
BC Break
Bug
Can't Fix
Deprecation
Documentation
DQL
Duplicate
Failing Test
Hacktoberfest
Improvement
Improvement
Incomplete
Invalid
Missing Tests
New Feature
PR Created
pull-request
Mirrored from GitHub Pull Request
 Question
Regression
Status: Waiting feedback
Waiting feedback
WIP
Won't Fix
No Label Bug
Milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
admin
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: doctrine/archived-orm#860
Delete Branch "%!s()"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?