[CakePHP] How to join multiple tables within pagination without repetition in CakePHP?

If you have multiple tables and you want them all to be shown in relation with one another without unnecessary repetition, then this post is surely for you.

In the index action, I have $this->paginate() which lists the entries of one table successfully, then I added a second table relating to first one. It works fine in add,edit and view section but on index action, I didn’t get the both arrays side by side as required. For example the desired pattern to fetch is

Array
(
[0] => Array
        (
            [First] => Array
                (
                    [id] => 16
                )

            [Second] => Array
                (
                    [0] => Array
                        (
                            [id] => 12
                            [first_id] => 16
                        )
                    [0] => Array
                        (
                            [id] => 11
                            [first_id] => 16
                        )
                )

 ),
 [1] => Array
        (
            [First] => Array
                (
                    [id] => 17
                )

            [Second] => Array
                (
                    [0] => Array
                        (
                            [id] => 3
                            [first_id] => 17
                        )
                )
        )
)...

Using join inside the $this->paginate array to build conditions didn’t work. As it neither listed the Second table entries nor the unique results rather instead of showing the Second table entries, it listed only those entries having association with Second table (no such entries get listed that have no related records in Second), secondly it repeats the first table records to the number of its related entries in second table (2 repetitions for 1st record in sample array).

Can’t go with an option of fetching First table entries and looping over the second table records to increase the select sql queries manifold and resulting in timeout.

Following are the 3 steps that did the magic.

1) In First Model, simply add the $hasMany relation of First with Second

public $hasMany = array(
‘Second’ => array(
‘className’ => ‘Second’,
‘foreignKey’ => ‘first_id’,
‘dependent’ => false,
‘conditions’ => ”,
‘fields’ => ”,
‘order’ => ”,
‘limit’ => ”,
‘offset’ => ”,
‘exclusive’ => ”,
‘finderQuery’ => ”,
‘counterQuery’ => ”
)
);

2) In First Controller, add the Second Model usage as follows:

public $uses = array(‘First’,’Second’);

3) Finally print $this->paginate() and you will get the desired array.

NTB: In the $hasMany array of First Model, add as many secondary tables as wanted.

All the best with Cake 🙂