Cakephp 3 Dynamic limit parameter when using contain

Question

CakePHP Version 3.5.5

My end goal is to provide the user the functionality to change the amount of results displayed via a select list on the index view. Also I need the initial page load to be sorted by area_name asc.

// WHAT I'VE DONE

I changed where I was stipulated the limit parameter which can be seen below.

// AREAS CONTROLLER 

public $paginate = [
    'sortWhitelist' => [
       'Areas.area_name', 'Users.first_name', 'Users.last_name'
    ]
    //'limit' => 1, // REMOVED FROM HERE
    //'order' => [ // REMOVED FROM HERE
        //'Areas.area_name' => 'asc'
    //]
];

public function index()
{
    $query = $this->Areas->find('all')        
        ->contain([
            'Users'
        ])
        ->where(['Areas.status' => 1]);

    $limit = 1;
    $this->paginate = [
        'order' => ['Areas.area_name' => 'asc'], // ADDED HERE
        'limit' => $limit // ADDED HERE
     ];        

     $this->set('areas', $this->paginate($query));
}

And I declare the pagination sort links like:

// AREAS INDEX VIEW

<?= $this->Paginator->sort('Areas.area_name', __('Area Name')) ?>
<?= $this->Paginator->sort('Users.first_name', __('First Name')) ?>
<?= $this->Paginator->sort('Users.last_name', __('Last Name')) ?>

// RESULT

The above code works on all index methods within the application that don't use contain but when I implemented this solution here everything worked except I cannot sort on the associated data - IE: Users first and last name?

=========================================================================

WHAT I'VE TRIED

// Attempt 1

I added an initialize method above the public $paginate class like:

public function initialize()
{
    $limit = 1;      
}

public $paginate = [
    'sortWhitelist' => [
        'Areas.area_name', 'Users.first_name', 'Users.last_name'
    ]
    'limit' => $limit,
    'order' => [
        'Areas.area_name' => 'asc'
    ]
];

public function index()
{
    $query = $this->Areas->find('all')        
        ->contain([
            'Users'
        ])
       ->where(['Areas.status' => 1]);

    $this->set('areas', $this->paginate($query));
}

And the view I left the same.

// Result for Attempt 1

syntax error, unexpected ''limit'' (T_CONSTANT_ENCAPSED_STRING), expecting ']' on line 36 which is 'limit' => $limit,

=========================================================================

// Attempt 2

I tried to add the limit parameter and order array to the query like:

public function index()
{
    $limit = 1;
    $query = $this->Areas->find('all')        
        ->contain([
            'Users'
        ])
        ->where(['Areas.status' => 1])
        ->order(['Areas.area_name' => 'asc'])            
        ->limit($limit);

     $this->set('areas', $this->paginate($query));

}

// Result for Attempt 2

The result set was not ordered by the area_name and not limited to 1 result.

=========================================================================

// Attempt 3

I then changed the query and tried the following just to see if I could get a dynamic limit working:

$limit = 1;
$query = $this->Areas->find('all') 
    ->contain('Users', function ($q) {
        return $q
        //->order('Areas.area_name' => 'asc'),
        ->limit($limit);
    })
    ->where(['Areas.status' => 1]);

    $this->set('areas', $this->paginate($query));

// Result for Attempt 3

The result set was not limited to 1 result.

=========================================================================

ADDITIONAL INFORMATION

// USERS TABLE

$this->hasOne('Areas', [
    'foreignKey' => 'user_id'
]);

// AREAS TABLE

$this->belongsTo('Users', [
    'foreignKey' => 'user_id',
    'joinType' => 'INNER'
]);

I searched through the following cookbook sections (Pagination, Query Builder, Retrieving Data & Result Sets and Associations - Linking Tables Together) but I can't find a way to get this working so any help would be much appreciated.

Many thanks. Z.

Answer 1

You are overwriting the $paginate property in your index() method, so your settings including the whitelist are being lost.

Set the keys directly instead:

$this->paginate['order'] = ['Areas.area_name' => 'asc'];
$this->paginate['limit'] = $limit;