Cakephp pagination with joined tables

Question

I want to join the "sectors" table to my pagination results. Here is what happens:

I set the paginate variable so that it will join Sector:

$this->paginate = array(
        'joins' => array(
            array(
            'table' => 'sectors',
            'alias' => 'Sector',
            'type' => 'left',
            'foreignKey' => false,
            'conditions' => array('Company.sector_id = Sector.id'))),
        'conditions' => $conditions);

and then go

$jobs = $this->paginate('Job');

The query generated by the paginator is corrupted because it joins Sector before Company. Here is the part of the sql output:

SELECT COUNT(*) AS `count` FROM `jobs` AS `Job` left JOIN sectors AS `Sector` ON (`Company`.`sector_id` = 'Sector.id') LEFT JOIN `companies` AS `Company` ON (`Job`.`company_id` = `Company`.`id`)

and it should be:

SELECT COUNT(*) AS `count` FROM `jobs` AS `Job` LEFT JOIN `companies` AS `Company` ON (`Job`.`company_id` = `Company`.`id`) left JOIN sectors AS `Sector` ON (`Company`.`sector_id` = 'Sector.id')

I paginate the Job model and it is related to the Company model but not the Sector model. Thats why I need to join the Sector with pagination.

How can I fix this?

@DIDIERC without the 'conditions' specified in the 'joins' I will not be able to join this table. — Piotr Chabros
let me rephrase that: try putting "conditions" => $conditions before "joins" => ... in the outer array. Note that this is just a guess, I don't know if this is the source of your problem, but it doesn't cost much to try. — didierc

thaJeztah thaJeztah · Accepted Answer · 2013-02-22T23:30:09

'Joins' conditions in CakePHP should not be specified as an associative array, but as a string, otherwise CakePHP will handle 'Sector.id' as a literal string not as a reference to another Model/field.

So, replace this:

'conditions' => array('Company.sector_id' => 'Sector.id')

With this

'conditions' => array('Company.sector_id = Sector.id')

And your query should be ok

Cakephp pagination with joined tables

2 Answers