Can I force Doctrine ORM to split up a complicated query?

Question

I'm making a query with Doctrine that contains lots of joins. Some are hasOne relationships, and some are hasMany.

I think CakePHP makes separate queries for each hasMany relationship, but Doctrine seems to make one huge query. Both can hydrate the data and return a nice array into your php script, but they seem to make different sets of queries to do so. With Doctrine, as soon as your query contains several hasMany joins, performance can become pretty terrible.

With CakePHP, the default is to split the query, but I can force it to join (http://book.cakephp.org/view/872/Joining-tables). Is there a way to do the reverse in Doctrine: to force it to split the hasMany joins into different queries? I've tried the docs and API but not found anything yet.

What happens if you hydrate it as an object? Shouldn't Doctrine's lazy loading come into play then? — Mike B
is your problem not poor indexes on the table(s) you are querying? if i was you i would start by running DESCRIBE on the queries and find out if there is any indexes even being used. — dogmatic69

Pelle Pelle · Accepted Answer · 2010-11-22T08:37:59

Wrong solution: querying with many joins at once should always be faster than using fragmented queries in multiple SQL statements, provided that your query is correct.

If your query is terribly slow as soon as you add n:n or 1:n joins, in Doctrine, there are several causes for it.

One of the most frequent mistakes in queries with multiple joins, is the use of the LEFT JOIN + WHERE construction where you could use INNER JOIN with ON. Consider this DQL example:

SELECT a.*, c.*
  FROM Article a
  LEFT JOIN a.Buyer b
  LEFT JOIN b.Creditcard c
  LEFT JOIN c.digitalinfo d
  WHERE b.id = 2 AND d.info LIKE 'TEST%'

This is a very slow query if all tables have 10000 records. It will first join the entire table b with table a, resulting in 10000 ^ 2 rows, whereas in your WHERE clause you throw away pretty much 99,9% of them all.

SELECT a.*, c.*
  FROM Article a
  INNER JOIN a.Buyer b WITH b.id=2
  LEFT JOIN b.Creditcard c
  INNER JOIN c.Digitalinfo d WITH d.info LIKE 'TEST%'

Here, the INNER JOIN a.Buyer b does not end up with 100 000 000 rows, but since it uses an extended ON clause (Doctrine calles this WITH), it will only leave a small set. Therefore the other two joins will go lightning fast compared to what they performed like in the other statement.

Also, make sure you ALWAYS have indices

On columns that you search on. (If you search on full name, as in FirstName+' '+LastName, create an index on that sequence!)
On columns that you do specific joins on
On foreign keys and the fields they have the reference set to.

If you want to know what your database is doing behind the scenes, you could for example in MySQL type EXPLAIN followed by your query, and it tells you exactly why it is taking so long.

Can I force Doctrine ORM to split up a complicated query?

3 Answers