Doctrine nest multiple joins

I am trying to optimize a symfony application with Doctrine. I've stumbled upon the following problem:

In the view I used $project->getProgress(); but by doing this the number of queries increase as there are more projects. So I tried to put the progress into the projects query(overriding the findAll method) with a left join, but this didn't work out. The number of queries actually increased.

public function findAll($hydrationMode = null)
{
    $q = $this->createQuery('p')
                    ->leftJoin('p.progress pr')
                    ->leftJoin('pr.sfGuardUser u')
                    ->leftJoin('p.raws r')
                    ->leftJoin('p.series');
    return $q->execute(array(), $hydrationMode);
}

This is the result I'm trying to create with the least possible amount of queries:

array() {
  [0]=>
  array() {
    ["id"]=> string(1) "1"
    ...
    ["progress"]=>
    array() {
        array() {
            array() { progress1 }
            array() { progress2 }
            array() { progress3 }
            ....
        }
    }
  }

I've looked through the documentation of both symfony and Doctrine and couldn't find my answer. I've also searched on google for quite a while. (I've been looking for a solution for over a few months now) I hope I've described my problem good enough.

schema.yml:

projects:
  actAs:
    Timestampable: ~
  columns:
    user_id: integer(4)
    series_id: bigint
    pages: int
    chapter: string
    translators_id: bigint
    proofreaders_id: bigint
    cleaners_id: bigint
    typesetters_id: bigint
    raws_id: bigint
    hide_project: bool
    complete: bool
  relations:
    sfGuardUser:
      local: user_id
      foreign: id
      onDelete: CASCADE
    series:
      local: series_id
      foreign: id
   开发者_StackOverflow中文版   type: one
      foreignType: many
      onDelete: CASCADE
    progress:
      local: id
      foreign: projects_id
      onDelete: CASCADE
      type: one
      foreignType: many

projectsProgress:
  actAs: [Timestampable]
  columns:
    projects_id: bigint
    user_id: integer(4)
    job:
      type: enum
      notnull: true
      values: [tl,pr,cl,ts]
    beginpage: int
    endpage: int
    complete: bool
    file: string
    url: clob
  relations:
    sfGuardUser:
      local: user_id
      foreign: id
      onDelete: CASCADE
    projects:
      local: projects_id
      foreign: id
      onDelete: CASCADE
      foreignAlias: progress

The problem of using hydrate mode is that you could fetch data that wont use. I don't really know how doctrine fetch all data but assume that for each project brings all required data adding, at least, one more query...

For complex query's (and optimization) i prefer doing a manual query and manage to bring all needed data in one query, for example:

$query = "SELECT ... FROM projects p LEFT JOIN progress pr (LEFT JOIN sfGuardUser u (LEFT JOIN...) ON pr.user_id = u.id ) ON p.id = pr.projects_id"

$rs = Doctrine_Manager::getInstance()->getCurrentConnection()->fetchAssoc($query);

being $rs an array with selected data. Having all data you can make an array as you like, but remember that this way you do one query (reducing the db load) but add some time machine parsing the array.

You need to modify the query that fetches the Project object to include the joins. Conventionally, queries that fetch Project objects should be placed in ProjectTable.

If you want to modify all queries or subsets of queries of Project, look into Doctrine Query Execution Listeners.