CakePHP pagination - order by virtual field

Question

Contributors have songs and songs have contributors. I want to be able to sort by the number of songs that a contributor has.

In my Controller:

public $paginate = array(
    'fields' => array(
        'Contributor.id',
        'Contributor.name',
        'COUNT(DISTINCT ContributorsSong.song_id) AS Contributor__TotalSongs',
    ),

    'joins' => array(
        array(
            'alias' => 'ContributorsSong',
            'table' => 'contributors_songs',
            'type' => 'LEFT',
            'conditions' => 'ContributorsSong.contributor_id = Contributor.id'
        ),
        array(
            'alias' => 'Song',
            'table' => 'songs',
            'type' => 'LEFT',
            'conditions' => 'ContributorsSong.song_id = Song.id'
        )
    ),

    'group'  => array('ContributorsSong.contributor_id')

);

And in my index method.

    $this->Contributor->recursive = 0;
    $this->Paginator->settings = $this->paginate;
    $this->Contributor->virtualFields['TotalSongs'] = 0;

    $items = $this->paginate();

    echo '<pre>';print_r($items);echo '</pre>';

I'm trying to sort by the number of songs by using a virtual field, so when I go to

localhost/site/contributors/index/sort:TotalSongs/

I get this error:

Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column '0' in 'order clause'

SQL Query: SELECT Contributor.id, Contributor.name, COUNT(DISTINCT ContributorsSong.song_id) AS Contributor__TotalSongs FROM db_songs2.contributors AS Contributor LEFT JOIN db_songs2.contributors_songs AS ContributorsSong ON (ContributorsSong.contributor_id = Contributor.id) LEFT JOIN db_songs2.songs AS Song ON (ContributorsSong.song_id = Song.id) WHERE 1 = 1 GROUP BY ContributorsSong.contributor_id ORDER BY (0) desc LIMIT 18

I thought that TotalSongs would get turned into Contributor__TotalSongs in the query but it gets turned into 0. What is going on here? Thanks.

James F James F · Accepted Answer · 2014-02-16T22:40:39

I replaced:

$this->Contributor->virtualFields['TotalSongs'] = 0;

with:

$this->Contributor->virtualFields['TotalSongs'] = 'COUNT(DISTINCT ContributorsSong.song_id)';

And deleted: 'COUNT(DISTINCT ContributorsSong.song_id) AS Contributor__TotalSongs',

And it works for ordering now, but I can't use it in the conditions. I think this is part of the Limitations of virtualFields? I'm trying to select only tables where TotalSongs > 0, but I think I can get this another way, by changing the join from LEFT to INNER.

CakePHP pagination - order by virtual field

1 Answers