CODEWITHSUNDEEP
phpmysqlsymfony1doctrinesymfony-1.4
Shon Nocolis
Shon Nocolis

Reputation: 65

Too many query to database in Symfony and Doctrine

schema.yml:

JobeetCategory:
  actAs: { Timestampable: ~ }
  columns:
    name: { type: string(255), notnull: true, unique: true }

JobeetJob:
  actAs: { Timestampable: ~ }
  columns:
    category_id:  { type: integer, notnull: true }
    name:         { type: string(255) }
  relations:
    JobeetCategory: { onDelete: CASCADE, local: category_id, foreign: id, foreignAlias: JobeetJobs }

action.class:

public function executeIndex(sfWebRequest $request)
  {
    $this->jobeet_job_list = Doctrine::getTable('JobeetJob')
      ->createQuery('a')
      ->execute();
  }

and template:

<table>
    <?php foreach ($jobeet_job_list as $jobeet_job): ?>
    <tr>
      <td><?php echo $jobeet_job->getcategory_id() ?></td>
      <td><?php echo $jobeet_job->getName() ?></td>
    </tr>
    <?php endforeach; ?>
</table>

These template generated only 2 queries to database. This is ok. But if i do:

<table>
    <?php foreach ($jobeet_job_list as $jobeet_job): ?>
    <tr>
      <td><?php echo $jobeet_job->getJobeetCategory()->getName() ?></td>
      <td><?php echo $jobeet_job->getName() ?></td>
    </tr>
    <?php endforeach; ?>
</table>

And i have in database 100 JobeetJob then this generated 102 query to database! This is too many! Is possible reduce this?

Upvotes: 4

Views: 3001

Answers (1)

lonesomeday
lonesomeday

Reputation: 237905

You are currently "lazy-loading" the JobeetCategory objects. This is inefficient if you know you are going to have to do it lots of times. You should do a join on the initial query:

$this->jobeet_job_list = Doctrine::getTable('JobeetJob')
  ->createQuery('a')
  ->leftJoin('a.JobeetCategory c')
  ->execute();

This means that all the relevant JobeetCategory objects will be retrieved from the database and hydrated straight away, so you don't have to lazy-load them later. This should get you back down to 2 queries.

Upvotes: 5

Related Questions