CakePHP Advanced Pagination – sort by derived field

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.

HAVING clause in CakePHP find query

Recently I’m in love with Cake. Yes it’s true cake is too much addictive. ;) As a novice it’s not that comfortable to start with cakephp framework, cos most of the stuffs from the cake book hardly work perfectly, it’s good they update their code too frequently, but not the cake book. I’ll write several posts on cakephp issues for beginners with solutions in the coming days. Still a lot of ingredients of the cake are mysterious to me, but there is no doubt, I won’t hesitate to give them two thumbs up with 5 star rating :)

Right now I’m working on a price-comparison project using cakephp. This is the first time I’m using cake. I know you’d say why didn’t I try a smaller project first, actually I like to take risk (once my astrologist friend Imon told me this).

Whatever, lets get back to the “title” of this post. One of my queries needed HAVING clause, but did not find any way to use HAVING clause with cakephp find() function. I searched a lot on google, didn’t find anything, even very few people asked for it on popular groups/forums like google/nabble! The SQL query I wanted to run is:

SELECT `Product`.`id` , `Product`.`title` , `Product`.`image_link` , MIN( `ProductsOfMerchant`.`price` ) AS lowest_price, AVG( `ProductReview`.`rating` ) AS average_rating, COUNT( DISTINCT (
`ProductReview`.`id`
) ) AS total_reviews
FROM `products` AS `Product`
LEFT JOIN `products_of_merchants` AS `ProductsOfMerchant` ON ( `ProductsOfMerchant`.`is_active` =1
AND `ProductsOfMerchant`.`product_id` = `Product`.`id` )
LEFT JOIN `product_reviews` AS `ProductReview` ON ( `ProductReview`.`is_approved` =1
AND `ProductReview`.`product_id` = `Product`.`id` )
WHERE `Product`.`is_active` =1
GROUP BY `Product`.`id`
HAVING total_reviews & gt ; =2
ORDER BY `average_rating` DESC
LIMIT 4 

I added the “HAVING” in ‘group’ key of the find() function’s parameters. This is the cakephp code I used and it worked great!

$highRatedProducts = $this->find('all', array(
	'fields' => array('Product.id', 'Product.title', 'Product.image_link', 'MIN(ProductsOfMerchant.price) AS lowest_price', 'AVG(ProductReview.rating) AS average_rating', 'COUNT(DISTINCT(ProductReview.id)) AS total_reviews'),
	'conditions' => array('Product.is_active' => 1),
	'order' => 'average_rating DESC',
	'group' => 'Product.id HAVING total_reviews >= 2',
	'limit' => 4)
);

Please share in comments if you know any better(proper) “Cake way” to accomplish this.