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.

Related posts:

  1. CakePHP Advanced Pagination – sort by derived field
  2. Simplify your query easily

5 Comments to “HAVING clause in CakePHP find query”

  1. Roy 1 February 2010 at 11:29 pm #

    Neat idea. Found your page after googling for this exact problem and it works like a charm.

    Kinda feels like an SQL injection attack on your own code, no?

  2. adnan 1 February 2010 at 11:55 pm #

    haha, liked your comment :)
    umm.. as long it works fine some sql injection is not bad to make it work in “cake way” :-P

  3. Roy 2 February 2010 at 12:00 am #

    It’s a slight hack, but not too much so because it produces perfect SQL using $this->Model->find. It’s when you start using $this->Model->query that it starts to get hacky!

  4. adnan 2 February 2010 at 12:26 am #

    that’s why I tried so hard to find out the way to do it using find() function. Using query() with cake is the last option. And yes, you are right SQL injection is a big concern too to avoid query() function.

    I checked your site http://www.heybaloo.com. What is it about? can you pls send me an invitation :)

    Thanks

  5. Roy 2 February 2010 at 12:35 am #

    Heybaloo is a site for sharing local news. Users submit news stories about where they live and we tag them with locations, so you can view a news feed for your area (right down to a city suburb or small village). It’s kinda like a hyperlocal Digg or Reddit.

    If you go to http://www.heybaloo.com/account/request and register your name and email, I’ll get an invite sent right out to you.


Leave a Reply