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.

Tags | cake, cake way, CakePHP, find, framework, HAVING, ingredients, PHP, sql