CODEWITHSUNDEEP
cakephpsortingjoinpagination
Sathish Babu
Sathish Babu

Reputation: 285

Cakephp pagination with join table field sort is not working

Cakephp pagination with join table sort is not working for join table fields. But for custom sql join query working fine. Please help me to come out.

See below sample code.. I have Artist.name join table field in order.

$this->paginate = array(
        'fields' => array(
            'id', 
            'Song.title',
            'Song.date',
            'Artist.id AS artist_id',
            'Artist.name AS artist_name',
            'COUNT(SongViews.id) AS views'
        ),
        'group' => array('ArtistsSong.song_id'),
        'recursive' =>  0,
        'limit' =>  20,
        'joins' => array(
            array(
                'table' => 'tbl_artists_songs',
                'alias' => 'ArtistsSong',
                'conditions'=> array('Song.id = ArtistsSong.song_id')
            ),array(
                'table' => 'tbl_artists',
                'alias' => 'Artist',
                'conditions'=> array('Artist.id = ArtistsSong.artist_id')
            ),array(
                'table' => 'tbl_song_views',
                'alias' => 'SongViews',
                'type' => 'left',
                'conditions'=> array('SongViews.song_id = ArtistsSong.song_id')
            ),
        ),
        'order' => array('Artist.name'=>'asc')

    );

Upvotes: 15

Views: 7515

Answers (4)

Irshad Khan
Irshad Khan

Reputation: 6046

Sorting by columns in associated models requires setting sortWhitelist.

$this->paginate['order'] = [ 'Artist.name' => 'desc' ];
$this->paginate['sortWhitelist'] = ['Artist.name', 'Song.title'];
$this->paginate['limit'] = $this->paginate['maxLimit'] = 200;

In HTML you have to set below line in table header:

<?= $this->Paginator->sort('Song.title', __('Title')) ?>

Upvotes: 0

Paul
Paul

Reputation: 699

This question was asked nearly 5 years ago, but I came across the same problem in CakePHP 3. I realised I needed to whitelist the field to allow for sorting:

$this->paginate = array(
    ...
    'sortWhitelist' => array ('Artist.name')
);

The Paginator automatically whitelists fields from the original table but not from the JOINed tables.

Upvotes: 2

Sunil
Sunil

Reputation: 185

$this->paginate = ['fields' => ['id', 'name', 'company_id'],
                'contain' => [
                            'Companies' =>
                                 [
                                    'fields' => ['id', 'name'],
                                     'sort'=>['name'=>'ASC']
                                 ]
                'limit' => 10,

            ];

Upvotes: 0

antoyo
antoyo

Reputation: 11923

It is a bug in CakePHP.

However, there is a trick to do it.

You should add a virtual field in your primary model.

Assuming your primary model is Song, you should add this before calling paginate:

$this->Song->virtualFields = array(
    'artist_name' => 'Artist.name'
);

And now, you can sort by artist_name.

Upvotes: 20

Related Questions