Image Image Image Image Image

© Copyright 2012 ABCoder | Email | RSS

Scroll to Top

To Top

CakePHP

25

Jan
2010

In CakePHP

By abcoder

CakePHP Advanced Pagination – sort by derived field

On 25, Jan 2010 | In CakePHP | By abcoder

This is my second post on cake. Yesterday I was trying to implement pagination on my cakephp application and got frustrated as it can’t(?) sort by derived fields like SUM(), AVG(). I ransacked for the solution and got many alternate ways. Here is one from Andy Dawson: http://bakery.cakephp.org/articles/view/pagination. He has done a great job. But I wanted to do it by the core pagination method and didn’t feel interested to add a new component as cake has already provided with this feature.

Here comes struggling – I spent a couple of hours to find out a way… and finally got it! yes it is possible to sort by derived fields using the built-in pagination feature of cakephp! Let’s talk about the solution a little later, first I’d like to introduce you with the exact problem I faced.

I was trying to show the list of products in a tabular format with product name and lowest price. The product title is from products table (Product model) and the lowestprice is the MIN of price field from products_of_merchants table(ProductsOfMerchant model). Each Product belongsTo a certain SubCategory and hasMany ProductsOfMerchant selling them on different price(s).

Controller code segment

$this->paginate = array(
	'conditions' => array('Product.sub_category_id' => $subcategory['SubCategory']['id']),
	'fields' => array(
		'Product.id',
		'Product.title',
		'MIN(ProductsOfMerchant.price) AS lowestprice'
	),
	'order' => array('lowestprice' => 'asc'),
	'limit' => 10,
	'group' => 'Product.id'
);
$products = $this->paginate('Product');
$this->set(compact('products'));

It works fine for the first time as default order by lowestprice asc. In the view, I wanted to sort the lowestprice (derived field) column interactively asc/desc.

View code segment

<?php echo $paginator->sort('Lowest Price', 'lowestprice'); ?>

Also tried with

<?php echo $paginator->sort('Lowest Price', 'ProductsOfMerchant.lowestprice'); ?>

I clicked the ‘Lowest Price’ link to sort by desc, no luck! cos it’s a derived field and in debug mode there is no “ORDER BY” clause at the end of the query. I found a lot of ppl looking for the solution of this same issue and here is the solve (+backstage scenes) I’m gonna share with you.

From the cake book I found this Custom Query Pagination.
First I added this function in the Product model for debugging.

Product Model code segment

function paginate($conditions, $fields, $order, $limit, $page = 1, $recursive = null, $extra = array()) {
	print_r(func_get_args()); // for debugging
	$group = $extra['group'];
	return $this->find('all', compact('conditions', 'fields', 'order', 'limit', 'page', 'recursive', 'group'));
}

Here is the output when I tried to sort by ‘lowestprice’ desc by clicking the ‘Lowest Price’ column header link:

Array
(
    [0] => Array
        (
            [Product.sub_category_id] => 5
        )

    [1] => Array
        (
            [0] => Product.id
            [1] => Product.title
            [2] => MIN(ProductsOfMerchant.price) AS lowestprice
        )

    [2] => Array
        (
        )

    [3] => 10
    [4] => 1
    [5] => 1
    [6] => Array
        (
            [group] => Product.id
        )
)

You can see when you try to sort by a derived field the order by array (array key=2) is empty. This is definitely a bug of the pagination method, but for now we need a patch/fix to get it work and you know it’s never a good idea to modify the core of any framework. The interesting part of the paginate function is it takes an $extra argument which contains an array with ‘group’ key. I added a dummy key-value array to the $paginate array in the controller and wow! it’s automagically appended with the $extra argument of the paginate function in the model. Now all I need is to pass the sort key somehow via the $extra array. This is exactly what I did:

Controller code segment

$this->paginate = array(
	'conditions' => array('Product.sub_category_id' => $subcategory['SubCategory']['id']),
	'fields' => array(
		'Product.id',
		'Product.title',
		'MIN(ProductsOfMerchant.price) AS lowestprice'
	),
	'order' => array('lowestprice' => 'asc'),
	'limit' => 10,
	'group' => 'Product.id',
	'passit' => $this->passedArgs // pass via $extra
);
$products = $this->paginate('Product');
$this->set(compact('products'));

I hope you know what does $this->passedArgs do. If not, no worry, you can see the output of pr($this->passedArgs) from any controller/view.

Here is the patch I applied to the paginate function in the model:

Product Model code segment

function paginate($conditions, $fields, $order, $limit, $page = 1, $recursive = null, $extra = array()) {
	if(empty($order)){
		// great fix!
		$order = array($extra['passit']['sort'] => $extra['passit']['direction']);
	}
	$group = $extra['group'];
	return $this->find('all', compact('conditions', 'fields', 'order', 'limit', 'page', 'recursive', 'group'));
}

And finally it worked great!

Oh! I forgot, there is a little pain in the view section. I’ve already said this is a patch, the paginate function can’t detect the current sorting direction of the derived fields automatically. The link with the derived field’s sorting column always remains direction:asc no matter you change it manually from the address bar to asc or desc! Here is the fix for view section:

View code segment

<?php
// echo $paginator->sort('Lowest Price', 'lowestprice');
// Use $html->link, not $paginator->sort
echo $html->link(__('Lowest Price', true), array(
	'controller' => 'sub_categories',
	'action' => 'view',
	'page' => $this->passedArgs['page'],
	'sort' => 'lowestprice',
	'direction' => (empty($this->passedArgs['direction']) || $this->passedArgs['direction'] == 'asc')?'desc' : 'asc',
	'limit' => $this->passedArgs['limit']
));
?>

Please turn off debugging by Configure::write(‘debug’, 0) otherwise you may see some annoying warnings and notices of undefined variables. Or you may use isset to check if the vars are set or not and modify the view code accordingly. To me it’s less important and didn’t bother to fix it.

It’s almost perfect now and worked happily ever after.. ;)

If you have any better idea or suggestion please share in the comments.

Become expert web developer with testking JN0-342 online training. Download the testking JN0-532 tutorials and testking 1z0-053 study guide to learn advance php applications.

Tags | , , , , , , , , , ,

Comments

  1. I had a the same problem and managed to solve it without any need to change the links or the pagination process, using a nice tweak and ideas from this tutorial.

    For this to magically work, one needs to set the value of $paginate['order'] and unset $passedArgs['sort']. Simply insert the code below after setting up $paginate nd before activating paginate()

    if(isset($this->passedArgs['sort'])) {
    $dsort = $this->passedArgs['sort'];
    unset($this->passedArgs['sort']);
    if(isset($this->passedArgs['direction'])) {
    $this->paginate['order'] = array($dsort => $this->passedArgs['direction']);
    } else {
    $this->paginate['order'] = array($dsort => ‘ASC’);
    }
    }

    I hope it helps.

    Cheers,
    Omri

  2. Anonymous

    Great article (and with great comments too : using Virtualfields does the trick).

    How would we know we have to use virtual fields if we aren’t aware of them and we are trying to solve a “sorting” issue ? Read this article.

    Thank you !

  3. thiagottjv

    Hi, for me (using JOINS) i had to use the function like this:

    function paginate($conditions, $fields, $order, $limit, $page = 1, $recursive = null, $extra = array()) {
    if(!empty($extra['passit']['sort'])){
    $order = array(@$extra['passit']['sort'] => @$extra['passit']['direction']);
    }
    $group = @$extra['group'];

    if($extra['joins'] != ”){
    $joins = @$extra['joins'];
    }

    return $this->find(‘all’, compact(‘conditions’, ‘fields’, ‘order’, ‘limit’, ‘page’, ‘recursive’, ‘group’, ‘joins’));
    }

  4. great job it saved my efforts and time thanks a lot

  5. I’m impressed, I need to say. Really rarely will i go through a blog that’s both educative and interesting, and without a doubt, you’ve hit the nail on the head. Your current concept is definitely excellent; the thing is something which not enough people are talking intelligently about. I’m very happy that stumbled across it in my try to find something about it.

  6. After I originally commented I clicked the -Notify me when new feedback are added- checkbox and now each time a remark is added I get four emails with the same comment. Is there any means you’ll be able to take away me from that service? Thanks!

  7. hi,
    Thanks for great articular but m getting error,
    “Parse error: syntax error, unexpected T_VARIABLE calendar_accounts_controller.php on line 28″
    any clue it shows error for “passit”
    Controller Code:

    class CalendarAccountsController extends CalendarAppController
    {
    var $name = ‘CalendarAccounts’;
    var $components = array(‘RequestHandler’);
    var $paginate = array(‘order’ => array(
    ‘email’ => ‘asc’
    ),
    ‘fields’ => array(
    ‘if(ISNULL(`PopEmailAlias`.`email_address`) , `ClientUser`.`email_address`, `PopEmailAlias`.`email_address`) as email’,
    //’email’,
    ‘CalendarAccount.*’,
    ‘ClientUser.email_address’,’ClientUser.client_domain_id’,
    ‘ClientDomain.id’,’ClientDomain.name’,
    ‘PopEmailAlias.email_address’,’PopEmailAlias.client_domain_id’,//’PopEmailAlias.id’,
    ‘PopEmailAliasDomain.id’,’PopEmailAliasDomain.name’
    ),
    ‘passit’ => $this->passedArgs
    );