DDC-841: Generated SQL contains non-existen alias #5362

Closed
doctrinebot opened this Issue Oct 16, 2010 · 13 comments

2 participants

@doctrinebot

Jira issue originally created by user jkleijn:

Result:

PDOException: SQLSTATE[HY000]: General error: 1 no such column: e2.ownerid

Generated SQL:

SELECT c0.id AS id0, c0_.createdAt AS createdAt1, c0_.updatedAt AS updatedAt2, c0_.title AS title3, c0_.color AS color4, c0_.owner_id AS owner_id5 FROM calendar c0_ INNER JOIN user u1_ ON e2_.owner_id = u1_.id WHERE u1.username = 'bot'

Notice how the table alias "e2" does not existm yet is used in the join clause.

Meta:

/****
 * @ManyToOne(targetEntity="User")
 * @JoinColumn(name="owner_id", referencedColumnName="id")
 *
 * @var User
 */

DQL:

SELECT c FROM Calendar c JOIN c.owner u WHERE u.username = 'bot'

A workaround would be appreciated.

Thanks!

@doctrinebot

Comment created by jkleijn:

This also happened with BETA1 and also in other scenarios, e.g.

SELECT w0.id AS id0, w0_.createdAt AS createdAt1, w0_.updatedAt AS updatedAt2, w0_.start AS start3, w0_.end AS end4, w0_.owner_id AS owner_id5 FROM worklog w0_ INNER JOIN user u1_ ON e2_.owner_id = u1_.id WHERE u1_.id = ? AND w0.start IS NULL

SQLSTATE[HY000]: General error: 1 no such column: e2.ownerid

This may have to do with the "owner" field being defined in a mapped superclass.

@doctrinebot

Comment created by jkleijn:

Confirmed, when I move the declaration down to the implementation class the exception goes away.

When I add it rather then moving it, I get an exception saying the field was already added, so the declaration in the supertype is not simply omitted (not that that would explain the invalid SQL).

@doctrinebot

Comment created by romanb:

Generall,y mapped superclasses may only define unidirectional associations, because an association can not point back to a mapped superclass (a mapped superclass is not a persistent class). The relevant doc section is here: http://www.doctrine-project.org/projects/orm/2.0/docs/reference/inheritance-mapping/en#mapped-superclasses

Though, in your example the association actually points back to an entity, not the mapped superclass, so this may be a case that can be addressed properly, so we need to investigate this further.

ps: It is not entirely clear from the information you provided, but I assume there is no class that has targetEntity="NameOfMappedSuperclass" ? Because that will by definition never work.

@doctrinebot

Comment created by jkleijn:

No, there are no associations to the mapped superclass.

It is purely for defining common meta data and properties for a certain type, exactly what one would use a mapped superclass for.

It's worth noting that while this is a birectional association between the two types when viewed statically, we're not talking about a single, bidirectional relation:

  • Calendar has an owner, which is a User. This is the user that created the object, a mechanism applicable to most entities in the system, hence the mapped superclass.
    • A User has a Calendar, but he may or may not be the "owner" of that Calendar.

So User has this specified:

/****
 * @ManyToOne(targetEntity=Calendar", fetch="EAGER", cascade={"persist", "remove"})
 * @JoinColumn(name="calendar_id", referencedColumnName="id")
 *
 * @var Calendar
 */
private $calendar;
@doctrinebot

Comment created by jkleijn:

Obviously this means that a User also has an "owner", which may or may not be the object itself.

@doctrinebot

Comment created by @beberlei:

I think this might be fixed with DDC-671 already.

@doctrinebot

Comment created by @beberlei:

relating

@doctrinebot

Comment created by @beberlei:

I need the whole entitiy definitions of Calender and User. Can you please paste them to somewhere?

@doctrinebot

Comment created by @beberlei:

I cannot reproduce it, i am pretty sure its a duplicate of DDC-671 and should therefore be fixed already in trunk.

@doctrinebot

Comment created by jkleijn:

I need the whole entitiy definitions of Calender and User. Can you please paste them to somewhere?

I have since chaned it, but I emailed you what I think are the relevant revisions of the classes.

@doctrinebot

Issue was closed with resolution "Fixed"

@doctrinebot

Comment created by jkleijn:

I've had a similar issue with multiple levels of MappedSuperclasses. It works in RC1, fails in 2.0 stable on non-existent table aliases. Don't have time to do a full bug report right now.

@beberlei beberlei was assigned by doctrinebot Dec 6, 2015
@doctrinebot doctrinebot added this to the 2.0-RC1 milestone Dec 6, 2015
@doctrinebot doctrinebot closed this Dec 6, 2015
@doctrinebot doctrinebot added the Bug label Dec 7, 2015
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment