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

New Issue

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

admin commented

2026-01-22 12:33:11 +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:11 +01:00

admin closed this issue

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

admin commented

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

@doctrinebot commented on GitHub (Dec 22, 2009):

Comment created by mzach:

A possible quick fix - at least in my test, have not checked about other situations though - would be to change line 727 in lib/ORM/Persisters/StandardEntityPersister.php (function: _getSelectColumnList) to

$columnList .= ', ' . $tableName . '.' . $assoc->getQuotedJoinColumnName($srcColumn, $this->_platform);

Note that the $tableName variable is added - thereby the resulting fields are prefixed and the query works fine.

Please check if this has any other influence!

@doctrinebot commented on GitHub (Dec 22, 2009): Comment created by mzach: A possible quick fix - at least in my test, have not checked about other situations though - would be to change line 727 in lib/ORM/Persisters/StandardEntityPersister.php (function: _getSelectColumnList) to ``` $columnList .= ', ' . $tableName . '.' . $assoc->getQuotedJoinColumnName($srcColumn, $this->_platform); ``` Note that the $tableName variable is added - thereby the resulting fields are prefixed and the query works fine. Please check if this has any other influence!

admin commented

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

@doctrinebot commented on GitHub (Jan 29, 2010):

Comment created by romanb:

The class setup/mapping looks broken (at least this is not how it is supposed to be, thus you may encounter strange issues), it should rather be:

PropertyGroup one-to-many (bidirectional) PropertyGroupMember
PropertyGroupMember one-to-one (unidirectional) Property

In the case where all you need is ordering of a many-to-many association, I can say that this will be simpler in the future once Doctrine supports ordered collections natively.
Then you could remove PropertyGroupMember completely. Once you introduce an association class though, using @ManyToMany to "jump over" the association class is not really supposed to be done.

Nevertheless the issue with the ambiguous column name is valid and should be fixed for the next release. Your suggestion to prepend the table name is one option but I'm not yet sure its the best.

Thanks for reporting.

@doctrinebot commented on GitHub (Jan 29, 2010): Comment created by romanb: The class setup/mapping looks broken (at least this is not how it is supposed to be, thus you may encounter strange issues), it should rather be: - PropertyGroup one-to-many (bidirectional) PropertyGroupMember - PropertyGroupMember one-to-one (unidirectional) Property In the case where all you need is ordering of a many-to-many association, I can say that this will be simpler in the future once Doctrine supports ordered collections natively. Then you could remove PropertyGroupMember completely. Once you introduce an association class though, using @ManyToMany to "jump over" the association class is not really supposed to be done. Nevertheless the issue with the ambiguous column name is valid and should be fixed for the next release. Your suggestion to prepend the table name is one option but I'm not yet sure its the best. Thanks for reporting.

admin commented

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

@doctrinebot commented on GitHub (Jan 29, 2010):

Comment created by romanb:

By "supports ordered collections natively", I mean the automatic persistence of the order, of course, since on-the-fly ordering is easy with DQL.

@doctrinebot commented on GitHub (Jan 29, 2010): Comment created by romanb: By "supports ordered collections natively", I mean the automatic **persistence** of the order, of course, since on-the-fly ordering is easy with DQL.

admin commented

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

@doctrinebot commented on GitHub (Feb 2, 2010):

Comment created by @beberlei:

Attached is a test-case, I can't reproduce it. Can you extend on the Test-Case until it fails for the reason you specified?

@doctrinebot commented on GitHub (Feb 2, 2010): Comment created by @beberlei: Attached is a test-case, I can't reproduce it. Can you extend on the Test-Case until it fails for the reason you specified?

admin commented

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

@doctrinebot commented on GitHub (Feb 2, 2010):

Issue was closed with resolution "Cannot Reproduce"

@doctrinebot commented on GitHub (Feb 2, 2010): Issue was closed with resolution "Cannot Reproduce"

admin commented

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

@doctrinebot commented on GitHub (Feb 2, 2010):

Comment created by @beberlei:

Btw it may be i got this wrong, however where does property group member come into play in your query builder / mapping? Its nowhere accessible, however it comes up in your failing query.

@doctrinebot commented on GitHub (Feb 2, 2010): Comment created by @beberlei: Btw it may be i got this wrong, however where does property group member come into play in your query builder / mapping? Its nowhere accessible, however it comes up in your failing query.

admin commented

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

@doctrinebot commented on GitHub (Feb 4, 2010):

Comment created by mzach:

It appeared when executing the query above and gave me the error mentioned, however uppgrading to latest svn-trunk seems to solve the issue (checked with my solution described above and without, unit tests of our project are all passing)

@doctrinebot commented on GitHub (Feb 4, 2010): Comment created by mzach: It appeared when executing the query above and gave me the error mentioned, however uppgrading to latest svn-trunk seems to solve the issue (checked with my solution described above and without, unit tests of our project are all passing)

admin commented

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

@doctrinebot commented on GitHub (Feb 4, 2010):

Comment created by mzach:

Btw. just to extend your TestCase: the problem was not the "id" field, but rather "createdAt", "modifiedAt" and "deletedAt" - id always worked fine (those fields are not reflected in the test provided, however I added them to my test and it worked)

@doctrinebot commented on GitHub (Feb 4, 2010): Comment created by mzach: Btw. just to extend your TestCase: the problem was not the "id" field, but rather "createdAt", "modifiedAt" and "deletedAt" - id always worked fine (those fields are not reflected in the test provided, however I added them to my test and it worked)

admin commented

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

@doctrinebot commented on GitHub (Dec 13, 2015):

Imported 1 attachments from Jira into https://gist.github.com/d5cc419d0862fd0a1fd5

10339_Ticket220Test.php

@doctrinebot commented on GitHub (Dec 13, 2015): Imported 1 attachments from Jira into https://gist.github.com/d5cc419d0862fd0a1fd5 - [10339_Ticket220Test.php](https://gist.github.com/d5cc419d0862fd0a1fd5#file-10339_Ticket220Test-php)

admin referenced this issue

2026-01-22 13:28:21 +01:00

DDC-1471: Unable to read mySQL "DATE" field from mysql db #1844

admin referenced this issue

2026-01-22 14:36:43 +01:00

DDC-3377: DateTime columns cannot be used with @Id #4174

admin referenced this issue

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

[PR #275] [CLOSED] fixes DDC-1471 #7950

admin referenced this issue

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

[PR #275] fixes DDC-1471 #7955