get count of relational table entry in symfony

Question

How can I get the number of entries under each job. Let's take a jobeet tutorial, here I want to show number of resume submits for each job.

Tables

job : id  title      
submit : id job_id file

Result :

 title           submits    
 job a             5
 old job           20

I am using Doctrine. So, How can I do this ?

Answer 1

Something like that:

$job    = Doctrine_Core::getTable('Job')->find(1);

// it will query the database to fetch the relation
$submit = $job->getSubmit()->count();

But if you want something more specific, show us your schema.yml.

If you want to avoid un new query to the database, you can fetch the relation with a leftJoin:

$job    = Doctrine_Core::getTable('Job')
  ->leftJoin('Job.Submit')
  ->find(1);

// the relation is already fetched, so it won't perform a new query
$submit = $job->getSubmit()->count();

By the way, be careful with the ->count() method, if you have a lot of submits, this query won't be really optimized. The best, is to perform a manual query count:

$submit = Doctrine_Core::getTable('Submit')
    ->createQuery('s')
    ->select('COUNT(s.id) as total')
    ->where('s.job_id = ?', array($job->getId()))
    // HYDRATE_NONE won't hydrate the return object 
    ->execute(array(), Doctrine_Core::HYDRATE_NONE);

// I guess the value is here but test yourself
var_dump($submit[0]);