Propel adds CROSS JOIN to query when using an alias to JOIN tables

Trying to do a fairly simple query in Propel 2. I have a Person table and a Possession table – persons can have many possessions but only one of each possession type. So a person can have 1 book, 1 car, etc. I’m trying to write a query in Propel that will return all persons along with their car name provided they have a car. Here’s the code and resulting query:

$x = PersonQuery::create()
  ->groupById()
  ->leftJoinPossession()
  ->addJoinCondition('Possession','Possession.possession_type = ?', 'car')
  ->withColumn('Possession.possession_name', 'CarName')
  ->where('Possession.possession_name IS NOT NULL')
  ->find()
  ->toString();

//resulting sql
SELECT person.id, possession.possession_name as CarName
FROM person
LEFT JOIN possession  ON (person.id=possession.person_id AND possession.possession_type = 'car')
WHERE possession.possession_name IS NOT NULL;

This works as expected. However, I need to do multiple joins to the possession table (get the books for each person for example) so I need to use an alias. Here’s what happens when I modify the previous query just to use an alias for the possession table (and to also get the book for each person):

$x = PersonQuery::create()
  ->groupById()
  ->leftJoinPossession('p')
  ->addJoinCondition('p','p.possession_type = ?', 'car')
  ->leftJoinPossession('p2') 
  ->addJoinCondition('p2','p2.possession_type = ?', 'book')
  ->withColumn('p.possession_name', 'CarName')
  ->withColumn('p2.possession_name', 'BookName')
  ->where('p.possession_name IS NOT NULL')
  ->find()
  ->toString();

//resulting sql
SELECT person.id, p.possession_name as CarName, p2.possession_name as BookName
FROM person
CROSS JOIN possession
LEFT JOIN possession p ON (person.id=p.person_id AND p.possession_type = 'car')
LEFT JOIN possession p2 ON (person.id=p2.person_id AND p2.possession_type = 'book')
WHERE p.possession_name IS NOT NULL;

As you can see, for some reason Propel adds “CROSS JOIN possession” to the query. This doesn’t change the result of the query but it makes it extremely slow. Any ideas on how I can tell Propel not to use a CROSS JOIN while still using an alias for my joined table? (the CROSS JOIN also disappears if I take out the ‘where’ clause)

Answer

Found a workaround for this issue. The problem was that when I tried to do multiple left joins to the same table (by using aliases) Propel would also include a cross join to that table. However, I found that if the first join did not use an alias, Propel would not add the cross join.

So, to sum up, this was not working:

$x = PersonQuery::create()
  ->groupById()
  ->leftJoinPossession('p')
  ->addJoinCondition('p','p.possession_type = ?', 'car')
  ->leftJoinPossession('p2') 
  ->addJoinCondition('p2','p2.possession_type = ?', 'book')
  ->withColumn('p.possession_name', 'CarName')
  ->withColumn('p2.possession_name', 'BookName')
  ->where('p.possession_name IS NOT NULL')
  ->find()
  ->toString();

//resulting sql
SELECT person.id, p.possession_name as CarName, p2.possession_name as BookName
FROM person
CROSS JOIN possession
LEFT JOIN possession p ON (person.id=p.person_id AND p.possession_type = 'car')
LEFT JOIN possession p2 ON (person.id=p2.person_id AND p2.possession_type = 'book')
WHERE p.possession_name IS NOT NULL;

But this worked as expected:

$x = PersonQuery::create()
  ->groupById()
  ->leftJoinPossession() //changed
  ->addJoinCondition('Possession','Possession.possession_type = ?', 'car') //changed
  ->leftJoinPossession('p2') 
  ->addJoinCondition('p2','p2.possession_type = ?', 'book')
  ->withColumn('p.possession_name', 'CarName')
  ->withColumn('p2.possession_name', 'BookName')
  ->where('p.possession_name IS NOT NULL')
  ->find()
  ->toString();

//resulting sql
SELECT person.id, p.possession_name as CarName, p2.possession_name as BookName
FROM person
LEFT JOIN possession ON (person.id=posession.person_id AND posession.possession_type = 'car')
LEFT JOIN possession p2 ON (person.id=p2.person_id AND p2.possession_type = 'book')
WHERE p.possession_name IS NOT NULL;