Using CakePHP pagination to sort on a calculated field

Question

I'm trying to get a list of paginated results, but I would like the option to sort them on a calculated field.

I have this:

$this->Paginator->settings = array(
        'fields' => 'COUNT(Invoice.id) as totalInvoices,
                 SUM(Invoice.price_paid) as totalPaid,
                 MAX(Invoice.invoice_date) as latestInvoice',
        'group' => array('Invoice.supplier_id'),
        'order' => array($order => $direction)
    );

$order is the name of the field to sort on, and $direction is just 'asc' or 'desc'.

This is fine if I sort on a field which exists in the database, like 'price_paid' to find the Invoices in order of how much each is for or whatever, but it doesn't seem to cope with sorting on totalPaid or latestInvoice - I just get the unsorted results back.

I tried creating a virtual field, by doing, e.g.

$this->Invoice->virtualFields['latestInvoice'] = 'MAX(Invoice.invoice_date)';
$order = "Invoice.latestInvoice";

but that also doesn't seem to make any difference - the sort order is still ignored.

I looked for solutions online, but they all seemed to resolve the problem by using Virtual Fields, which obviously hasn't worked for me!

Bharat Maheshwari Bharat Maheshwari · Accepted Answer · 2014-05-23T13:31:45

I tried it with Virtual Fields and it is working fine for me below is my code may it help :-

$this->Invoice->virtualFields['latestInvoice'] = 'MAX(Invoice.invoice_date)';
$order = "Invoice.latestInvoice";
$options = array(               
                'fields' => 'COUNT(Invoice.id) as totalInvoices,
                      SUM(Invoice.price_paid) as totalPaid,
                      MAX(Invoice.invoice_date) as latestInvoice',
                'order' => array($order =>'asc'),
            );
$this->paginate = $options;
$list = $this->paginate('Invoice');
pr($list);die;

Also check in array that virtual field is in $list or not.

Using CakePHP pagination to sort on a calculated field

1 Answers