Strange Doctrine behaviour with double innerJoin()

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 mes开发者_StackOverflowsages 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 ?

The ORM Limit issue

This has to do with the LIMIT part. :) Doctrine LIMIT works a bit different than MySQL limit does.

MySQL LIMIT just issues the query, and stops searching as soon as n rows are found that matches your SQL query. Since in ORM, this is really unexpected behaviour (it might well be that in a scalar layout the SQL SELECT * FROM myModel LEFT JOIN someOtherModel ON someCondition LIMIT 3 actually only returns one myModel instance rather than three, since a left join can result 3 rows.

What does Doctrine do?

If your DQL query is FROM school s INNER JOIN s.students LIMIT 15, it means: give me 15 instances of school that have at least one student associated (hence INNER JOIN), with ALL of their student associates. To do this, Doctrine first asks for DISTINCT school with the exact same query parameters and a LIMIT part, to figure out which 15 school IDs should be returned. After this is done, these IDs are queried next, without the LIMIT part.

How to solve your issue

If you are not having an actual problem, huzzah, find the explaination of this behaviour above. If your query output is other than you expected, make sure you take these steps into consideraton. If for instance your DQL is FROM school s INNER JOIN s.students LIMIT 15, and you are wondering why you get more than 15 students, try: FROM students s INNER JOIN s.school LIMIT 15. In MySQL this means basically the same (disregarding the order of the result), though in Doctrine, this means you will get 15 students instead of 15 schools.

This bothered me too with some of the more complex queries. The only solution that I found was to bypass the sillyness altogether:

$q = Doctrine_Manager::getInstance()->getCurrentConnection();
$my_result = $q->fetchAssoc(" ... PUT SQL HERE ... ");

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.