How to sort a column with foreign key according to arbitrary associated table column

Need Help
1

Hello,
please can anybody help me how to do this?

I have two tables - Subjects and Workplaces:

tables
tables919×318 6.27 KB

Workplaces contain foreign key “subject_id”, i.e. Subject has many Workplaces and Workplaces belongs to Subject.

The default Index template allows to sort the data by subject_id column in workplaces table.

The question is. How can I sort the data in the index view table not by the Workplaces.subject_id (the default option) but rather by the Subjects.subject_name column from the associated table.

I would like to achieve functionality like this:

Default baked way of sorting:

<?= $this->Paginator->sort('subject_id') ?>

I would like to achieve this:

<?= $this->Paginator->sort('workplace->subject->subject_name') ?>

Is it possible? How to do it?

otazka
otazka1061×599 26.5 KB

Thank you for any hint.

For completion, this is the corresponding part of my view controller:

<div class="table-responsive">
    <table>
        <thead>
            <tr>
                <th><?= $this->Paginator->sort('id') ?></th>
                <th><?= $this->Paginator->sort('workplace_name') ?></th>
	   <th><?= $this->Paginator->sort('subject_id') ?></th>
                <th><?= $this->Paginator->sort('street') ?></th>
                <th><?= $this->Paginator->sort('city') ?></th>
                <th><?= $this->Paginator->sort('zip') ?></th>
                <th><?= $this->Paginator->sort('country_id') ?></th>
                <th class="actions"><?= __('Actions') ?></th>
            </tr>
        </thead>
        <tbody>
            <?php foreach ($workplaces as $workplace): ?>
            <tr>
                <td><?= $this->Number->format($workplace->id) ?></td>
                <td><?= h($workplace->workplace_name) ?></td>
                <td><?= $workplace->has('subject') ? $this->Html->link($workplace->subject->subject_name, ['controller' => 'Subjects', 'action' => 'view', $workplace->subject->id]) : '' ?></td>
                <td><?= h($workplace->street) ?></td>
                <td><?= h($workplace->city) ?></td>
                <td><?= h($workplace->zip) ?></td>
                <td><?= $workplace->has('country') ? $this->Html->link($workplace->country->name, ['controller' => 'Countries', 'action' => 'view', $workplace->country->id]) : '' ?></td>
                <td class="actions">
                    <?= $this->Html->link(__('View'), ['action' => 'view', $workplace->id]) ?>
                    <?= $this->Html->link(__('Edit'), ['action' => 'edit', $workplace->id]) ?>
                    <?= $this->Form->postLink(__('Delete'), ['action' => 'delete', $workplace->id], ['confirm' => __('Are you sure you want to delete # {0}?', $workplace->id)]) ?>
                </td>
            </tr>
            <?php endforeach; ?>
        </tbody>
    </table>
</div>
2

You can do something like this

WorkplaceController.php:

$this->paginate = [
    'fields' => [
        'Workplaces.id',
        'Workplaces.workplace_name',
        'Subjects.id',
        'Subjects.subject_name',
    ],
    'contain' => [
        'Subjects',
    ],
    'order' => ['Workplaces.workplace_name' => 'ASC'],
    'sortableFields' => [
        'Workplaces.workplace_name',
        'Subjects.subject_name',
    ]
];

index.php:

<th><?= $this->Paginator->sort('Workplaces.workplace_name', ['label' => __('Workplace')]) ?></th>
<th><?= $this->Paginator->sort('Subjects.subject_name', ['label' => __('Subjects')]) ?></th>

Reference: Paginator - 4.x

3

Thank you very much @mvojwe. I will try to make it your way.