Cakephp 3.x Sorting of another model is not working

Need Help
1

I have two models Provides & Expenses
Problem : I have list of users with column Expenses.type_of_document, Expenses.change_to,Expenses.date, Provider.rut, Provider.nickname. Each & Every column has sorting but on Provider.rut and Provider.nickname sorting is not working.

ExpensesTable.php:

public function initialize(array $config)
{
parent::initialize($config);

    $this->addBehavior('Timestamp');
    $this->table('expenses');
    $this->displayField('id');
    $this->primaryKey('id');

    $this->belongsToMany('Events', [
        'foreignKey' => 'expense_id',
        'targetForeignKey' => 'event_id',
        'joinTable' => 'events_expenses'
    ]);
    $this->belongsToMany('Providers', [
        'foreignKey' => 'expense_id',
        'targetForeignKey' => 'provider_id',
        'joinTable' => 'expenses_providers'
    ]);
    $this->hasMany('ExpensesProviders', [
        'foreignKey' => 'expense_id'
    ]);
}

ProviderTable.php
public function initialize(array $config)
{
parent::initialize($config);

    $this->table('providers');
    $this->displayField('name');
    $this->primaryKey('id');

    $this->addBehavior('Timestamp');

    $this->hasMany('Products', [
        'foreignKey' => 'provider_id'
    ]);
}

ExpensesProvidersTable.php
public function initialize(array $config)
{
parent::initialize($config);

    $this->table('expenses_providers');
    $this->displayField('id');
    $this->primaryKey('id');

    $this->belongsTo('Providers', [
        'foreignKey' => 'provider_id'
    ]);
    $this->belongsTo('Expenses', [
        'foreignKey' => 'expense_id'
    ]);
}

ExpensesController.php
public function index(){
$this->paginate = [
‘contain’ => [‘Providers’],
‘sortWhitelist’ => [
‘Expenses.type_of_document’,
‘Expenses.charge_to’,
‘Expenses.date_of_issue’,
‘Providers.rut’,
‘Providers.nickname’,
‘Expenses.document_number’,
‘Expenses.details’,
‘Expenses.amount_total’,
‘Expenses.payment_status’,
‘Expenses.way_to_pay’,
‘Expenses.created’,
‘Expenses.paid_by’,
]
];
$expenses = $this->paginate(
$this->Expenses
->find()
->contain([‘Events’, ‘Providers’])
);
//debug($expenses);
$this->set(compact(‘expenses’, ‘opt’));
$this->set(’_serialize’, [‘expenses’]);
}

index.ctp

            <th class="left"><?= $this->Paginator->sort('Expenses.type_of_document', 'Tipo') ?></th>
            <th class="left"><?= $this->Paginator->sort('Expenses.charge_to', 'Cargo') ?></th>
            <th class="left"><?= $this->Paginator->sort('Expenses.date_of_issue', 'Fecha') ?></th>
            <th class="left"><?= $this->Paginator->sort('Providers.rut', 'Rut') ?></th>
            <th class="left"><?= $this->Paginator->sort('Providers.nickname', 'Proveedor') ?></th>
            <th class="left"><?= $this->Paginator->sort('Expenses.document_number', 'N° Dcto.') ?></th>
            <th class="left"><?= $this->Paginator->sort('Expenses.details', 'Detalle') ?></th>
            <th class="right"><?= $this->Paginator->sort('Expenses.amount_total', 'Total') ?></th>
            <th class="left"><?= $this->Paginator->sort('Expenses.payment_status', 'Estado') ?></th>
            <th class="left"><?= $this->Paginator->sort('Expenses.way_to_pay', 'Forma') ?></th>
            <th class="left"><?= $this->Paginator->sort('Expenses.created', 'Ingreso') ?></th>
            <th class="left"><?= $this->Paginator->sort('Expenses.paid_by', 'Pagado por') ?></th>
            <!--<th class="left"><?= $this->Paginator->sort('AccountingMonths.date', 'Mes') ?></th>
            <th scope="col"><?= $this->Paginator->sort('payment_date', 'Fecha de pago') ?></th>
            <th scope="col"><?= $this->Paginator->sort('accounting_statement', 'Estado de cuenta') ?></th>-->
            <th class="actions center"><?= __('Acciones') ?></th>
        </tr>
    </thead>
    <tbody>
        <?php foreach ($expenses as $expense): ?>
        <tr>
            <td class="left"><?= $expense->type_of_document ?></td>
            <td class="left"><?= (strcmp($expense->charge_to, 'evento') == 0 and !empty($expense->events))? $expense->events[0]->code : $expense->charge_to ?></td>
            <td class="left"><?= $this->Utils->chileanTimeZone($expense->date_of_issue, 'DDMMYYYY', 'UTC') ?></td>
            <td class="left"><?= h(!empty($expense->providers)? $expense->providers[0]->rut : '') ?></td>
            <td class="left"><?= h(!empty($expense->providers)? $expense->providers[0]->nickname : '') ?></td>
            <td class="left"><?= h($expense->document_number) ?></td>
            <td class="left"><?= h($expense->detail) ?></td>
            <td class="right"><?= "$ ".$this->Number->format($expense->amount_total, ['locale' => 'es_CL']) ?></td>
            <td class="left"><?= h($expense->payment_status) ?></td>
            <td class="left"><?= h($expense->way_to_pay) ?></td>
            <td class="left"><?= $this->Utils->chileanTimeZone($expense->created, 'DDMMYYYY', 'UTC') ?></td>
            <td class="left"><?= h($expense->paid_by) ?></td>
            <td class="actions center">
                <?= $this->Html->link(__('View'), ['action' => 'view', $expense->id]) ?>
                <?= $this->Html->link(__('Edit'), ['action' => 'edit', $expense->id]) ?>
                <?= $this->Form->postLink(__('Delete'), ['action' => 'delete', $expense->id], ['confirm' => __('Estás segur@ de eliminar # {0}?', $expense->document_number)]) ?>
            </td>
        </tr>
        <?php endforeach; ?>
    </tbody>
</table>
<?= $this->element('pagination') ?>

It does not work to order

<?= $this->Paginator->sort('Providers.rut', 'Rut') ?> <?= $this->Paginator->sort('Providers.nickname', 'Proveedor') ?>

Let me know any solution you have.

2

Maybe you are having the same issue as #11330

Can you try with

$expenses = $this->paginate(
    $this->Expenses
        ->find()
        ->contain([‘Events’, ‘Providers’]),
    $this->paginate
);
3

It is not the same, I have an error, perform the sort and the other person when using paginator. Add the line I recommend not solve the problem.

4

Solución

ExpensesController.php

    function index(){
    $this->paginate = [
                'contain' => ['Providers'],
                'sortWhitelist' => [
                    'Expenses.type_of_document',
                    'Expenses.charge_to',
                    'Expenses.date_of_issue',
                    'Providers.rut',
                    'Providers.nickname',
    ]
    ];
    }

index.ctp

 <thead>
            <tr>
 <th scope="col"><?= $this->Paginator->sort('Expenses.type_of_document', 'Tipo') ?></th>
                <th scope="col"><?= $this->Paginator->sort('Expenses.charge_to', 'Cargo') ?></th>
                <th scope="col"><?= $this->Paginator->sort('Expenses.date_of_issue', 'Fecha') ?></th>
                <th scope="col"><?= $this->Paginator->sort('Providers.rut', 'Rut') ?></th>
                <th scope="col"><?= $this->Paginator->sort('Providers.nickname', 'Proveedor') ?></th>
<th scope="actions center"><?= __('Acciones') ?></th>
            </tr>
        </thead>