doSelectJoin... fails if same table has to be joined several times.

[info@…]

I did not find this bug already, perhaps I did not see it? It seems very obvious to me.

The automatically generated doSelectJoinAll- and doSelectJoinExcept..-methods in Peer-classes will fail if the same table has to be joined several times.

For example:

Given table message with sender_id and recipient_id, both foreign keys of table user with primary key id.

MessagePeer::doSelectJoinAll() should select the message, the user that is the sender and the user that is the recipient of the message. But it fails, because Propel does something like:

SELECT ... FROM message,user WHERE user.id=message.recipient_id AND user.id=message.sender_id

...but it is very seldom that the sender and the reciever of a message is the same user. The correct solution in this case would be:

SELECT ... FROM message, user AS A, user AS B WHERE A.id=message.recipient_id AND B.id=message.sender_id

Used versions: PHP5.0, MySQL 4.1, Propel 1.1

[hans]

This is a known limitation & we would *like* to fix it, but this is not at all trivial to fix. I'm changing the milestone.

[hans]

Issue #14 has been marked as a duplicate of this one, since this one better describes the issue.

[Slick Rick]

This is an important feature. Those of us using Symfony-Project (symfony-project.com) are waiting for this one bigtime!

[synace]

I feel that ALL tables should utilize aliases, that's probably the easiest way to approach this. Just add aliases to all tables, including multiple instances of the same table. Provide interface to pull the aliases.

An alternative would be to just add table aliases for the duplicated tables that are already present within Criteria.

[hans]

We cannot address this issue before we fix the way Criteria & Propel interoperate at a lower level. We are currently in the process of overhauling Criteria for Propel 2.0, so expect that this will be fixed for that release.

[rihad <rihad@…>]

Darn. A big showstopper. The good thing is that Propel mostly works :)

[rihad <rihad@…>]

I'm too using Symfony with Propel and it gave me too much gray hair as I was getting to know it better during the last month, forcing me to rewrite every almost every access to DB as I went on. Donald Knuth was wrong. If "suboptimal" and "ORM" are synonyms, then I apologize, but until then every last bit of Propel 1.2 should be demolished from devs' machines with no backups :( Emotions prevail.

[hans]

I'm closing this as duplicate, since it is also expressed in ticket:438 (and I think a little easier to reproduce from there).