2 Joins on the same table

[bernhard.slominski@…]

I have the following challenge: I have a table with 2 joins on the same table. The tables look like this:

<table name="orders">
 <column name="id" type="INTEGER" primaryKey="true"/>
 <column name="status_lookup_id" type="INTEGER" />
 <column name="type_lookup_id" type="INTEGER" />
 <foreign-key foreignTable="lookup">
   <reference
     local="status_lookup_id"
     foreign="id"/>
 </foreign-key>
 <foreign-key foreignTable="lookup">
   <reference
     local="type_lookup_id"
     foreign="id"/>
 </foreign-key>
</table>

<table name="lookup">
 <column name="id" type="INTEGER" required="true" primaryKey="true"/>
 <column name="name" type="VARCHAR" size="40" required="true"/>
 <column name="description" type="VARCHAR" size="200" />
</table>

When using a doSelectJoinAll(...) Propel creates the following sql:

SELECT orders.id, orders.status_lookup_id, orders.type_lookup_id, lookup.id,
       lookup.name, lookup.description
FROM   orders, lookup
WHERE  orders.status_lookup_id = lookup.id
AND    orders.type_lookup_id = lookup.id

This is of course not working because the two columns status_lookup_id and type_lookup_id have (normally) two different values, so the query always returns an empty resultset.

The right query would be to use aliases thus joining the lookup table twice:

SELECT orders.id, orders.status_lookup_id, orders.type_lookup_id, 
       l_status.id, l_status.name, l_status.description,
       l_type.id, l_type.name, l_type.description
FROM   orders, lookup l_status, lookup l_type
WHERE  orders.status_lookup_id = l_status.id
AND    orders.type_lookup_id = l_type.id

This is of course a bit tricky. The key point is also that I need two different Lookup Objects for the status and the type.

[bernhard.slominski@…]

This is a duplicate with Ticket #157 can be closed.

[hans]

We had pushed off #157 for 2.0, because introducing aliasing for tables is not necessarily trivial here. I will look at this for 1.3, but no promise that it'll get fixed for this release.

[hans]

In thinking more about this, I think it makes the most sense for 2.0. Here's why: Even if we added an aliasing feature, the API for specifying conditions on the joined (aliased) tables would be completely unintuitive (an unobvious). This needs a better architected Criteria + query system.

[wittstruck@…]

I doubt that it's very hard to solve this problem, when using

$c->addAlias("alias1", TablePeer::TABLE_NAME);
$c->addJoin(TablePeer::alias("alias1", TablePeer::PRIMARY_KEY_COLUMN), TablePeer::PRIMARY_KEY_COLUMN);

the resulting query is almost perfect, only the where clause does not use the aliases, the join itself works.

SELECT ... 
FROM (a, b) 
INNER JOIN b b1 ON (a.firstValue = b1.ID) 
INNER JOIN b b2 ON (a.secondValue = b2.ID) 
WHERE a.firstValue = b.ID AND a.secondValue = b.ID

Only the where clause needs to be adjusted so that it's using b1 and b2. i'm using 1.2.1, though.

[guilherme@…]

I'm having the same problem in version 1.3

the same table is loaded more than once, then someone might help me customize the file PHP5PeerBuilder.php?

[v-dogg]

I'd like to open this for discussion again. This is a serious limitation and Criteria 2 seems so far away still.

Can we find a solution - even a non-perfect one - to enable doSelectJoinAll* queries?

[francois]

as of r1341, this is very easily achieved with a ModelCriteria (see #813).

[francois]

well, actually, it's not, because BasePeer does some black magic with joins that need to be fixed...

[francois]

now it is, in r1342