symfony pagination with join

Question

How do you paginate a query that has a join in symfony? I am trying this code but it times out:

$query=Doctrine_Query::create()
->select('c.name, d.phone')
->from('company c, companyDetails d')
->where('c.companyId=d.companyId');

$pager = new sfDoctrinePager('company',10);
$pager->setQuery($query);
$pager->setPage(1);
$pager->init();
$this->companys=$pager->getResults();

Answer 1

Your paginator code seems fine. I thk the problem is with your query.

Try running it without the pager and see the result.

If you continue facing issues with the query,Give this a try.

$query=Doctrine_Query::create()
->select('c.name, d.phone')
->from('company c')
->innerJoin('c.companyDetails d');

I havent tried it myself but it should work if your schema relations are defined the way i think.

One more thing, probably u already know that.

$pager->setPage(1); 

should be something like

$pager->setPage($request->getParameter('page', 1));

Answer 2

I needed to change the count query in the pagination. To do this:

$pager = new Doctrine_Pager($query,$request->getParameter('page',1),10);
$pager->setCountQuery('SELECT COUNT(id) FROM items WHERE city_id='.$city.' AND category_id='.$category);
$this->pager=$pager;

The only thing you have to do is in your pagination helper, use:

$pagerRange = $pager->getRange('Sliding',array('chunk' => 5));
$pages = $pagerRange->rangeAroundPage();