Symfony Jobeet: Need to refactor index page DB queries

Question

I use Symfony 1.4 with Doctrine, and I noticed that my project has similar issue with Jobeet project at index page (http://www.symfony-project.org/jobeet/1_4/Doctrine/en/06).

The problem is on index page the system shows to user jobs per category and we have 1+2*N database queries, where N - categories count.

This isn't very good, if you have > 10 categories.

At the index action:

$this->categories = Doctrine::getTable('JobeetCategory')->getWithJobs();

And in view for each category

$category->getActiveJobs(15), 15 here - is active jobs count.

Due to Doctrine relations I can use this

$category->getJobeetJobs()

and I'll get only one query per page, but this command gives me all active jobs for category, not 15 as I need. Of course I can limit results via array_slise or similar trick, but can anyone give an advise how to do this?

Basicaly I need mysql query something like that:

SElECT * FROM category s LEFT JOIN job s2 ON s.id = s2.category_id WHERE (s2.expires_at > '2010-02-17' AND s2.status = 'published') LIMIT 15

But limit only jobs in each category, NOT all the results of this query.

Please help with any ideas about this. Thank you!

Answer 1

The below should execute the mysql query you gave in your example

$this->categories = Doctrine::getTable('JobeetCategory')->createQuery('c')->leftJoin('c.JobeetJobs j')->where('j > ?',$the_date)->andWhere('j.status = ?',$the_status)->limit(15);