Doctrine : leftJoin on table generates unnecessary queries

Short description of my environment :

• Symfony : 1.4.8
• Doctrine : 1.2.3
• Centos 5.5 (PHP 5.3.3)
• MySQL 5.1.52

I've a new project built in Symfony and here is the project schema :

# Car
RjCar:
  actAs: { Timestampable: ~ }
  columns:
    id: { type: integer(4), unsigned: true, primary: true, autoincrement: true }
    year: { type: integer(2), unsigned: true, notnull: true }
    engine_mod: { type: string(1000) }
    exterior_mod: { type: string(1000) }
    suspension_mod: { type: string(1000) }
    audio_mod: { type: string(1000) }
    vote_pos: { type: integer(4), notnull: true, unsigned: true, default: 0 }
    vote_neg: { type: integer(4), notnull: true, unsigned: true, default: 0 }
    views: { type: integer(4), notnull: true, unsigned: true, default: 0 }
    # Foreign keys
    category_id: { type: integer(1), unsigned: true, notnull: true }
    category_check: { type: boolean, notnull: true, default: 0 }
    user_id: { type: integer(5) }
  relations:
    RjCategory: { onDelete: CASCADE, local: category_id, foreign: id, foreignAlias: RjCars }
    sfGuardUser: { onDelete: CASCADE, local: user_id, foreign: id, foreignAlias: RjCars }

# Category
RjCategory:
  columns:
    id: { type: integer(1), unsigned: true, primary: true, autoincrement: true }
    name: { type: string(255), notnull: true}

# I do not include the sfGuardUser schema, but it's the default one from the plugin

When I want to retrieve the last 10 cars with category name and username, I use the following code in the RjCarTable.class.php :

  $last_cars = $this->createQuery('car')
               ->leftJoin('car.sfGuardUser user')
               ->leftJoin('car.RjCategory categ')
               ->orderBy('car.created_at DESC')
               ->limit(10)
               ->execut开发者_JAVA百科e();

   return $last_cars;

On my page everything looks fine, I've all my results, but in the debug bar, I see 22 queries (instead of 2 as it should be).

Here is the query output for the first one that is normal :

SELECT
/* Everything about the car */
r.id AS r__id, 
r.year AS r__year, 
r.engine_mod AS r__engine_mod, 
r.exterior_mod AS r__exterior_mod, 
r.suspension_mod AS r__suspension_mod, 
r.audio_mod AS r__audio_mod,  
r.vote_pos AS r__vote_pos, 
r.vote_neg AS r__vote_neg, 
r.views AS r__views, 
r.type_id AS r__type_id, 
r.category_id AS r__category_id, 
r.category_check AS r__category_check, 
r.user_id AS r__user_id, 
r.created_at AS r__created_at, 
r.updated_at AS r__updated_at, 
/* ... hidden because irrelevant... retrieve everything about the sfGuardUser and profile... */
/* Everything about the category */
r2.id AS r2__id, 
r2.name AS r2__name, 
FROM rj_car r 
LEFT JOIN sf_guard_user s ON r.user_id = s.id 
LEFT JOIN rj_category r2 ON r.category_id = r2.id 
ORDER BY r.created_at DESC 
LIMIT 10

So until then everything is normal, except this query is followed by 20 others to retrieve informations about each category (2 queries for each result apparently), while you can notice in the previous query these informations are available. I will not put all of them but here is some :

SELECT r.id AS r__id, r.name AS r__name FROM rj_category r WHERE (r.id = '8') LIMIT 1
SELECT r.id AS r__id, r.name AS r__name FROM rj_category r WHERE (r.id = '8') LIMIT 1
SELECT r.id AS r__id, r.name AS r__name FROM rj_category r WHERE (r.id = '9') LIMIT 1
SELECT r.id AS r__id, r.name AS r__name FROM rj_category r WHERE (r.id = '9') LIMIT 1
/* etc.. 20 times */

So my real questions are : - Why is it performing all these unecessary queries while the first query should have these information? - Why is this not happening for the sfGuardUser table? The relation between my RjCar object and sfGuardUser object is apparently the same as the one between RjCar and RjCategory.

If somebody already face the same problem I'll be really glad to ear about it. As I say everything is working fine, but I prefere this module not generate unecessary queries as it should perform on the homepage of my application.

This behavior seems odd and definitely not what is expected. Some things to try:

• Are you caching Doctrine queries or results? Sometimes queries can stay cached after changes to the model with weird effects.
• Those queries definitely look like Doctrine's lazy-fetching behavior. Have you tried narrowing down which calls specifically trigger those queries? Try tracking down which specific lines trigger the queries and see if there's anything special about them.

I slapped myself :

Here is what I found in the RjCar.class.php

/**
* Return the category of the car
*/    
public function getRjCategory(){
  return Doctrine_Core::getTable('RjCategory')->find($this->getCategoryId());
}

So it explains the unecessary queries... I don't even remember writing this piece of code, but considering I'm the only one working on this project I guess it's me.

As usual the problem was between the chair and the keyboard...

Thanks for your help.

When using ORM frameworks like Doctrine and Hibernate, it often generates redundant and ugly queries. The more complicated the query, the uglier it gets on these systems (try adding a 'limit' to your joined query and look at the code generated).
Frameworks in general provide great solution for 80% of your cases. For the remaining 20% you need to work slightly harder. Usually it's a fair trade-off.
If you are concerned about the quality of the output of this query, or think that you can't afford the latency it causes - Doctrine has a very simple method for writing and managing custom queries. Don't be afraid to go down that path...