DDC-220: 'Ambiguous column: 7 ERROR: column reference "created_by_person_id" is ambiguous at character 466' if ManyToMany through a mapping class is used which extends a base class #273

New Issue

admin · 2026-01-22T12:33:08+01:00

admin commented

2026-01-22 12:33:08 +01:00

Originally created by @doctrinebot on GitHub (Dec 21, 2009).

Originally assigned to: @beberlei on GitHub.

Jira issue originally created by user mzach:

Using a many-to-many mapping with a separate class where each class extends a common base class, loading of the primary class succeeds whereas fetching related records results in a 'SQLSTATE[42702]: Ambiguous column: 7 ERROR: column reference "created_by_person_id" is ambiguous at character 466' error.

Scenario:

/*** @MappedSuperclass **/
class AbstractBase
{
    /**** @Id @Column(type="integer")
     * @GeneratedValue(strategy="AUTO")
     */
    protected $id;

    /*** @Version @Column(type="integer") **/
    protected $version;

    /*** @Column(name="created_at", type="datetime") **/
    protected $createdAt;

    /*** @Column(name="modified_at", type="datetime") **/
    protected $modifiedAt;

    /*** @Column(name="deleted_at", type="datetime") **/
    protected $deletedAt;

    /****
     * @OneToOne(targetEntity="Person")
     * @JoinColumn(name="created*by_person*id", referencedColumnName="id")
     */
    protected $PersonCreate;

    /****
     * @OneToOne(targetEntity="Person")
     * @JoinColumn(name="modified*by_person*id", referencedColumnName="id")
     */
    protected $PersonModify;

    /****
     * @OneToOne(targetEntity="Person")
     * @JoinColumn(name="deleted*by_person*id", referencedColumnName="id")
     */
    protected $PersonDelete;
}

/*** @Entity **/
class Property 
    extends AbstractBase
{
    /*** @Column(name="property_name", type="string") **/
    protected $propertyName;

    /*** @Column(name="display_type", type="string") **/
    protected $displayType;
}

/***@Entity **/
class PropertyGroup 
    extends AbstractBase
{
    /*** @Column(name="group_name", type="string") **/
    protected $groupName;

    /****
     * @ManyToMany(targetEntity="Property")
     * @JoinTable(name="property*group*member",
     *      joinColumns={@JoinColumn(name="property*group*id", referencedColumnName="id")},
     *      inverseJoinColumns={@JoinColumn(name="property_id", referencedColumnName="id")}
     *      )
     */
    protected $Properties;
}

/***@Entity **/
class PropertyGroupMember extends AbstractBase
{
    /*** @Column(name="order_number", type="integer") **/
    protected $orderNumber;

    /****
     * Previous: (At)OneToOne(targetEntity="PropertyGroup")
     * @ManyToOne(targetEntity="PropertyGroup")
     * @JoinColumn(name="property*group*id", referencedColumnName="id")
     */
    protected $PropertyGroup;

    /****
     * http://www.doctrine-project.org/documentation/manual/2_0/en/association-mapping#one-to-many,-self-referencing
     * Previous: (At)OneToOne(targetEntity="Property")
     * @ManyToOne(targetEntity="Property")
     * @JoinColumn(name="property_id", referencedColumnName="id")
     */
    protected $Property;
}

As shown above, the class PropertyGroupMember is used to map Groups and Properties using an orderNumber as additional fields and all classes extends AbstractBase which holds the id, version number as relations to a Person class (not described here, consists of the usual fields firstname, lastname and so on).

Now, when using a simple DQL like the following:

        $qb = $em->createQueryBuilder()
                ->select("g")
                ->from('PropertyGroup', "g")
                ->leftJoin("g.Properties", "p");

the data is retrieved just fine, however iterating over the resulting PropertyGroups and accessing their $Properties results in the following SQL (which tries to lazy-load the data):

SELECT property.property*name, property.display_type, property.id, property.version,  property.created_at,  property.modified_at,  property.deleted_at,  created_by_person_id, modified_by_person_id, deleted_by_person*id  
FROM property INNER JOIN property*group_member ON property.id =  property_group_member.property_id WHERE  property_group_member.property_group*id = ?

Now, the fields "created_by_person_id", "modified_by_person_id" and "deleted_by_person_id" are not prefixed with the corresponding table name and therefore ambiguous as both the property as well as the property_group_member table contain these fields.

Originally created by @doctrinebot on GitHub (Dec 21, 2009). Originally assigned to: @beberlei on GitHub. Jira issue originally created by user mzach: Using a many-to-many mapping with a separate class where each class extends a common base class, loading of the primary class succeeds whereas fetching related records results in a 'SQLSTATE[42702]: Ambiguous column: 7 ERROR: column reference "created_by_person_id" is ambiguous at character 466' error. Scenario: ``` /*** @MappedSuperclass **/ class AbstractBase { /**** @Id @Column(type="integer") * @GeneratedValue(strategy="AUTO") */ protected $id; /*** @Version @Column(type="integer") **/ protected $version; /*** @Column(name="created_at", type="datetime") **/ protected $createdAt; /*** @Column(name="modified_at", type="datetime") **/ protected $modifiedAt; /*** @Column(name="deleted_at", type="datetime") **/ protected $deletedAt; /**** * @OneToOne(targetEntity="Person") * @JoinColumn(name="created*by_person*id", referencedColumnName="id") */ protected $PersonCreate; /**** * @OneToOne(targetEntity="Person") * @JoinColumn(name="modified*by_person*id", referencedColumnName="id") */ protected $PersonModify; /**** * @OneToOne(targetEntity="Person") * @JoinColumn(name="deleted*by_person*id", referencedColumnName="id") */ protected $PersonDelete; } /*** @Entity **/ class Property extends AbstractBase { /*** @Column(name="property_name", type="string") **/ protected $propertyName; /*** @Column(name="display_type", type="string") **/ protected $displayType; } /***@Entity **/ class PropertyGroup extends AbstractBase { /*** @Column(name="group_name", type="string") **/ protected $groupName; /**** * @ManyToMany(targetEntity="Property") * @JoinTable(name="property*group*member", * joinColumns={@JoinColumn(name="property*group*id", referencedColumnName="id")}, * inverseJoinColumns={@JoinColumn(name="property_id", referencedColumnName="id")} * ) */ protected $Properties; } /***@Entity **/ class PropertyGroupMember extends AbstractBase { /*** @Column(name="order_number", type="integer") **/ protected $orderNumber; /**** * Previous: (At)OneToOne(targetEntity="PropertyGroup") * @ManyToOne(targetEntity="PropertyGroup") * @JoinColumn(name="property*group*id", referencedColumnName="id") */ protected $PropertyGroup; /**** * http://www.doctrine-project.org/documentation/manual/2_0/en/association-mapping#one-to-many,-self-referencing * Previous: (At)OneToOne(targetEntity="Property") * @ManyToOne(targetEntity="Property") * @JoinColumn(name="property_id", referencedColumnName="id") */ protected $Property; } ``` As shown above, the class PropertyGroupMember is used to map Groups and Properties using an orderNumber as additional fields and all classes extends AbstractBase which holds the id, version number as relations to a Person class (not described here, consists of the usual fields firstname, lastname and so on). Now, when using a simple DQL like the following: ``` $qb = $em->createQueryBuilder() ->select("g") ->from('PropertyGroup', "g") ->leftJoin("g.Properties", "p"); ``` the data is retrieved just fine, however iterating over the resulting PropertyGroups and accessing their $Properties results in the following SQL (which tries to lazy-load the data): ``` SELECT property.property*name, property.display_type, property.id, property.version, property.created_at, property.modified_at, property.deleted_at, created_by_person_id, modified_by_person_id, deleted_by_person*id FROM property INNER JOIN property*group_member ON property.id = property_group_member.property_id WHERE property_group_member.property_group*id = ? ``` Now, the fields "created_by_person_id", "modified_by_person_id" and "deleted_by_person_id" are not prefixed with the corresponding table name and therefore ambiguous as both the property as well as the property_group_member table contain these fields.

admin added the Bug label 2026-01-22 12:33:08 +01:00

admin referenced this issue

2026-01-22 15:57:42 +01:00

[PR #273] [MERGED] No unique join column fields for Single Table inheritance type. #7954

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