Image Image Image Image Image

© Copyright 2012 ABCoder | Email | RSS

Scroll to Top

To Top

CakePHP

25

Jan
2010

20 Comments

In CakePHP

By abcoder

CakePHP Advanced Pagination – sort by derived field

On 25, Jan 2010 | 20 Comments | 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. kkruit

    You can overwrite the hasField function and just do something like:

    function hasField($fieldName){
         if(parent::hasField($fieldName))
              return true;
         else
              switch($fieldName){
                   case "lowestprice":
                   case "Custom.field_name1":
                   case "Custom.field_name2":
                   case "Custom.field_name3":
                        return true;
                   default:
                        return false;
              }
         return false; // for good measure
    }
    • @kkruit, thanks for your hint. I’ll try it.

    • Kadek

      Hi, it’s work but make my sql error when I try to click sortable link, because unknown colum “Product.lowestprice” in table Products.
      How I can solved this problem? Thanks before

  2. kkruit

    oops forgot to mention that code would be in the model you are paginating.

  3. bernhard

    I liked the hasField idea, and improved it a bit.
    In the AppModel class I added
    additionalFields);
    }
    ?>

    This allowes you to use additional fields in your models by just adding

    As far as I can see it from here, no modifications in the views are needed for asc / desc.

  4. bernhard

    shoot. do not know how to add code snippets here. One more try:

    Add this

    // redefine this in your model for additional fields
    var $additionalFields = array();

    function hasField($name) {
    // handle fields
    if(parent::hasField($name)) {
    return true;
    }
    // maybe this model has a field that is added by a custom query
    return in_array($name, $this->additionalFields);
    }

    to AppModel

    and
    var $additionalFields = array(‘count_stn’, ‘sum_stn’);
    to your Models

  5. why not just upgrade to 1.3 and use virtualFields http://book.cakephp.org/view/1608/Virtual-fields

  6. sid

    Thanks buddy for ur valuable article.

  7. lucas

    thank dudes you rules
    i took your ideas and pack them into

    function hasField($fieldName) {
    return parent::hasField($fieldName) || in_array($fieldName, $this->additional_fields);
    }

  8. ffyall

    @dogmatic69 -> life saver! tks

  9. wpolscemamymocneseo

    Great article, but a frustrating read, due to the lack of proper punctuation in a lot of spots. Please close your quotations! If this article is following some sort of nouveau grammatical style, of which I’m not aware, I remain frustrated; however, I apologize.

  10. Hi.. Thanks a lot for this explanation. This helped me to fix another problem I was having. I was trying to get this to work with some complex model associations.

    I had Model 1 belongs to Model 2 belongs to Model 3. I was paginating model 1, but I wanted to sort on Model 3.

    I was using bindModel in my controller and joins in my paginator variable to get out my data like this

    [] => array(
    [Model 1] => array(),
    [Model 2] => array(),
    [Model 3] => array()
    )

    but it still wasn’t working. So I tried your method, but the paginate override was screwing up the associations.

    So I took the joins out of the variable, and moved my bindModel call into the paginate function to get my associations and it worked!

    Maybe I missed something with your tutorial, but this was the only way I could get it working with my set up. Thanks again!

  11. 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

  12. 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 !

  13. 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’));
    }

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

  15. 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.

  16. 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!

  17. 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
    );

Submit a Comment