双内连接的奇怪主义行为（）

Question

I have a database schema like this: My database schema: http://i.stack.imgur.com/vFKRk.png

To explain the context: One user writes one message. He can send it to one or more users.

I succeeded to get the title of message, the author for one user. However Doctrine, which I use for this project, do it with 2 queries. It's a little bit strange for me and I'm looking to understand, why. Normally, we can do it with one SQL query.

My DQL query:

$q = Doctrine_Query::create()->select('id_me, users_id_us, state_me, type_me, mc.title_mc, us.login_us') ->from('messages m')->innerJoin('m.messages_content mc')->innerJoin('mc.Users us') ->where('users_id_us = ?', $user)->limit($opt['limit'])->offset($opt['offset'])->orderBy($opt['order']);return $q->fetchArray();

SQL queries returned by Doctrine:

SELECT DISTINCT m3.id_me FROM messages m3  INNER JOIN messages_content m4 ON m3.messages_content_id_mc = m4.id_mc  INNER JOIN users u2 ON m4.users_id_us = u2.id_us WHERE m3.users_id_us = '6' ORDER BY m3.id_me DESC LIMIT 2

SELECT m.id_me AS m__id_me, m.users_id_us AS m__users_id_us, m.state_me AS m__state_me, m.type_me AS m__type_me, m2.id_mc AS m2__id_mc, m2.title_mc AS m2__title_mc, u.id_us AS u__id_us, u.login_us AS u__login_us FROM messages m INNER JOIN messages_content m2 ON m.messages_content_id_mc = m2.id_mc INNER JOIN users u ON m2.users_id_us = u.id_us WHERE m.id_me IN ('11') AND (m.users_id_us = '6') ORDER BY m.id_me DESC

Why my Doctrine query doesn't return the query like this:

SELECT m.id_me, m.users_id_us, m.state_me, m.type_me, mc.title_mc, u.login_us FROM messages m JOIN messages_content mc ON mc.id_mc = m.messages_content_id_mc JOIN users u ON u.id_us = mc.users_id_us WHERE m.users_id_us = 6;

Any idea to transform my DQL query and execute it one time ?

Answer 1

The solution which works:

I changed the relation alias and specified the columns participating in ON joint between messages_content and users.

The right Doctrine query is:

$q = Doctrine_Query::create()
->select('id_me, users_id_us, state_me, type_me, mc.title_mc, us.login_us') 
->from('messages m')
->innerJoin('m.messages_content mc')
->innerJoin('m.Users us ON mc.users_id_us=us.id_us')    
->where('users_id_us = ?', $user)
->limit($opt['limit'])
->offset($opt['offset'])
->orderBy($opt['order']);

It gives a SQL query like this:

SELECT m.id_me AS m__id_me, m.users_id_us AS m__users_id_us, m.state_me AS m__state_me, m.type_me AS m__type_me, m2.id_mc AS m2__id_mc, m2.title_mc AS m2__title_mc, u.id_us AS u__id_us, u.login_us AS u__login_us FROM messages m INNER JOIN messages_content m2 ON m.messages_content_id_mc = m2.id_mc INNER JOIN users u ON (m2.users_id_us = u.id_us) WHERE (m.users_id_us = '7') ORDER BY m.id_me DESC LIMIT 2

Tom and Pelle ten Cate, thanks for your participation.