Cakephp pagination with join table field sort is not working

Question

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')

    );

Answer 1

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.

Answer 2

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.