Join & On override

Users & Books are linked by CompoUsersBooks. So, It's a m:m relationship.

Here, a crazy DQL query to fetch users & specifics books.

[code]
$query = Doctrine_Query::create ()
->select ("users., books.")
->from ("Users users")
->leftJoin ("users.alternateBooks alternateBooks")
->leftJoin ("users.books books ON books.id_book = alternateBooks.id_book");
[/code]

The SQL query
[code]
SELECT `s`.`id_show` AS `s__id_show`
FROM `users` `u`
LEFT JOIN `compo_users_books` `c1`
ON ( `u`.`id_user` = `c2`.`id_user` )
LEFT JOIN `books` `b1`
ON `c1`.`id_book` = `b1`.`id_book`
LEFT JOIN `compo_users_books` `c2`
ON `u`.`id_user` = `c2`.`id_user`
LEFT JOIN `books` `b2`
ON `b1`.`id_book` = `b2`.`id_book`
[/code]

As you can see,
The SQL query is correctly build.
But, this SQL Query isn't optimized at all.
Because the following part of code isn't use :
[code]
LEFT JOIN `compo_users_books` `c2`
ON `u`.`id_user` = `c2`.`id_user`
[/code]

And also, the left join is a cartesian product ! The number of row return is really big for nothing...

That really sux because It's performance killer.