Looking for help with pagination and sorting in associated models

Need Help
1

Hey folks,

I’ve managed to get basic pagination working in some of my views and models where it is operating directly on the main data table but I can’t for the life of me work out where I’m going wrong trying to get it working on associated data. I’ve been at it for a few days now and read many of the existing topics and the manual and I assure you I only raise a new topic on such a common issue as a last resort.

What I’m looking to do is have a collection database, where a user can have many collections and collections contain many collection items (not sure if it factors in but a collection item can also belong to many collections and a collection to many users). The index page for collections works fine, I can see, page through and sort all the user’s collections as I would hope, the problem comes in the view.

In the view I’m trying to display the data about the collection at the top of the page in a tall table and then each of the collection items it contains in a fat table underneath. I’ve got the basic layout and iteration through collection items working fine, it’s just the sorting and pagination on them that doesn’t seem to work.

The problem I’m having that although the URL generated looks right (I think) collections/view/1?sort=collection_items.id&direction=asc it doesn’t actually sort anything.

Here is the current iteration of my code (relevant parts only), it may be a little inconsistent because I’ve been trying quite a few changes to get this going. If anyone can walk me through where I’m going wrong and how to fix it it would be greatly appreciated.

CollectionsController.php

<?php
namespace App\Controller;

use App\Controller\AppController;
use Cake\Event\Event;

/**
 * Collections Controller
 *
 *
 * @property \App\Model\Table\CollectionsTable $Collections
 */
class CollectionsController extends AppController
{

    public $paginate = [
        'Collections' => ['limit' => 10,
                          'contain' => ['CollectionItems', 'CollectionItems.CollectableItems',
                                        'CollectionItems.CollectableItems.Games',
                                        'CollectionItems.CollectableItems.Consoles',
                                        'Users'],
                           'sortWhitelist' => ['name', 'start_date', 'CollectionItems.id']]
    ];

    /**
     *
     * View all the items in a collection.
     *
     * @param string|null $id Collection id.
     * @return \Cake\Network\Response|null
     * @throws \Cake\Datasource\Exception\RecordNotFoundException When record not found.
     */
    public function view($id = null)
    {
        $collection = $this->Collections->find('all',
                         ['contain' => ['Users', 'CollectionItems', 'CollectionItems.CollectableItems',                                       'CollectionItems.CollectableItems.Games',                                        'CollectionItems.CollectableItems.Consoles']])
                                        ->where(['Collections.id =' => $id]);
        
        $collection = $this->paginate($collection)->first();    

        if (is_null($collection)){
            $this->Flash->error(__('This collection does not exist!'));
            return $this->redirect($this->referer(['action' => 'index']));
        }

        $this->set('collection', $collection);
        $this->set('_serialize', ['collection']);
    }

Collections/view.ctp

<div class="collections view large-12 columns content">
    <h3><?= h($collection->name) ?></h3>
    <table class="vertical-table">
        <tr>
            <th><?= __('Id') ?></th>
            <td><?= $this->Number->format($collection->id) ?></td>
        </tr>
        <tr>
            <th><?= __('Name') ?></th>
            <td><?= h($collection->name) ?></td>
        </tr>
        <tr>
            <th><?= __('Date Started') ?></th>
            <td><?= $this->Time->format($collection->start_date, 'dd-MMM-yyyy HH:MM') ?></td>
        </tr>
        <?php $rowNumber = 1; ?>
        <?php foreach ($collection->users as $users): ?>
        <tr>
            <th>
            <?php if ($rowNumber++ === 1) {
                    if(count($collection->users) === 1){
                        echo __('Owner');
                    } else {
                        echo __('Owners');
                    }
                  } else {} ?></th>
            <td><?= h($users->screen_name) ?></td>
        </tr>
        <?php endforeach; ?>
    </table>
    <div class="related">
        <h4><?= __('Collection Items') ?></h4>
        <?php if (!is_null($collection)): ?>
        <table cellpadding="0" cellspacing="0" id="collection-items">
            <tr>
            <!--
                TODO - Sorting isn't working here.--> 
                <th id="id" class="centre"><?= $this->Paginator->sort('collection_items.id', __('Id')) ?></th>
                <th id="name"><?= $this->Paginator->sort('name', __('Name')) ?></th>
               <!--
                <th id="id" class="centre"><?= __('Id') ?></th>
                <th id="name"><?= __('Name') ?></th>
                -->
                <th class="centre"><?= __('Boxed') ?></th>
                <th class="centre"><?= __('Manual') ?></th>
                <th class="centre"><?= __('Virtual') ?></th>                
                <th><?= __('Condition') ?></th>
                <th class="alignRight"><?= __('Purchase Price') ?></th>
                <th id="purchase-date" class="centre"><?= __('Purchase Date') ?></th>                                
                <th class="centre"><?= __('For Sale') ?></th>
            </tr>
            <?php foreach ($collection->collection_items as $collectionItem): ?>
            <tr>
                <td class="centre"><?= h($collectionItem->id) ?></td>
                <td><?php if ($collectionItem->collectable_item->type == 'Game'): ?>
                    <?= $this->Html->link(h($collectionItem->collectable_item->game->name), ['controller' => 'CollectionItems', 'action' => 'view', $collectionItem->id]) ?>
                <?php else : ?>
                    <?= $this->Html->link(h($collectionItem->collectable_item->console->name), ['controller' => 'CollectionItems', 'action' => 'view', $collectionItem->id]) ?>                    
                <?php endif; ?>
                </td>
                <td class="centre"><?= $this->Format->yesNo($collectionItem->boxed) ?></td>
                <td class="centre"><?= $this->Format->yesNo($collectionItem->manual) ?></td>
                <td class="centre"><?= $this->Format->yesNo($collectionItem->virtual) ?></td>
                <td><?= h($collectionItem->condition) ?></td>                                
                <td class="alignRight"><?= $this->Number->currency($collectionItem->purchase_price) ?></td>
                <td class="centre"><?= $this->Time->format($collectionItem->purchase_date, 'dd-MMM-yyyy') ?></td>
                <td class="centre"><?= $this->Format->yesNo($collectionItem->for_sale) ?></td>
            </tr>
            <?php endforeach; ?>
        </table>
        <?php endif; ?>
    </div>
</div>

CollectionsTable.php

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

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

        $this->belongsToMany('CollectionItems', [
            'foreignKey' => 'collection_id',
            'targetForeignKey' => 'collection_item_id',
            'joinTable' => 'collection_items_collections'
        ]);
        $this->belongsToMany('Users', [
            'foreignKey' => 'collection_id',
            'targetForeignKey' => 'user_id',
            'joinTable' => 'collections_users'
        ]);
    }

CollectionItems.php

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

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

        $this->belongsTo('CollectableItems', [
            'foreignKey' => 'collectable_item_id',
            'joinType' => 'INNER'
        ]);
        $this->belongsToMany('Collections', [
            'foreignKey' => 'collection_item_id',
            'targetForeignKey' => 'collection_id',
            'joinTable' => 'collection_items_collections'
        ]);
    }

CollectionItemsCollectionsTable.php

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

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

$this->belongsTo('CollectionItems', [
    'foreignKey' => 'collection_item_id',
    'joinType' => 'INNER'
]);
$this->belongsTo('Collections', [
    'foreignKey' => 'collection_id',
    'joinType' => 'INNER'
]);

}

All help is really, really appreciated.

1 Like
2

Try change size of the first letter or experiment with camelCase in yours Paginator->sort() especially where parameter is from related tables.

I make quick check in my working app and:
$this->Paginator->sort('Clients.short_name',"Klient")
above is working,

and below not, also it doesn’t report any errors
$this->Paginator->sort('clients.short_name',"Klient")

controller part in my app looks like this:
public function index()
{

	$this->paginate = [
        'contain' => ['Clients'],
		'order' => ['Orders.id' => 'desc'],
		'sortWhitelist' => ['Orders.id','Orders.nazwa_zlecenia','Clients.short_name','Orders.zlecono_dnia','Orders.status',
			'Orders.rodzaj'],			
		'limit' => 40,
        ];
	Time::setToStringFormat('yyyy-MM-dd');
	$this->set('orders', $this->paginate($this->Orders));
    //$this->set('_serialize', ['orders']);
}
1 Like
3

Thanks @jarekgol, this is one of the iterations I’ve tried and it does actually give a different response that may be of more help, or at least show that it is a step in the right direction.

Currently, with $this->Paginator->sort(‘collection_items.id’, __(‘Id’)) in place I don’t get any errors when clicking the link. The page loads fine with the extra sorting information in the URL but there is no signs of sorting (i.e. no change in the field header and no change of order).

When I change it to <?= $this->Paginator->sort('CollectionItems.id', __('Id')) ?> it throws the following exception:

Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘CollectionItems.id’ in ‘order clause’

Which makes sense given the SQL it shows:

SELECT Collections.id AS Collections__id, Collections.name AS Collections__name, Collections.start_date AS Collections__start_date FROM collections Collections WHERE Collections.id = :c0 ORDER BY CollectionItems.id asc LIMIT 10 OFFSET 0

Which shows no sign of the contained fields.

So the next logic step to me is to add Connections into the paginate thus (although I think this is wrong):

<?= $this->Paginator->sort('Collections.CollectionItems.id', __('Id')) ?>

and sure enough, it doesn’t work, reacting the same as the first query above, no error and page looks fine but unsorted.

I’m going to explore the second style further, I have the feeling it is correct and it is elsewhere that the issue lies. This link has a good example of how it is done, even if it is reporting issues itself.

4

I’ve found that changing the query to this:

    $collection = $this->Collections->find('all',
                     ['contain' => ['Users', 'CollectionItems' => ['sort' => ['CollectionItems.id' => 'ASC']],
                                    'CollectionItems.CollectableItems']])
                                    ->where(['Collections.id =' => $id]);

Sorts the data, however it isn’t controllable from the pagination links. Interesting though.

5

And be sure to not forget about:

I saw it on top but not now.

6

Thanks for all the advice @jarekgol, I’ve moved onto other things for now as I was getting a bit burned out on this. Will return to it later and keep you posted.

7

the ‘sortWhitelist’ works for me, can you please show us your model, i think if the sortWhitelist did not work for you, there is a problem with the relationship.