CakePHP sub query SQL in HABTM relation

Question

I want to suggest related products by tags and sort order by the most matched.

the HABTM model association between Product and Tag

class Product extends AppModel {
//..
var $hasAndBelongsToMany = array("Tag");
//..
}

and vice versa in Tag model. also join-table name is "products_tags".

for Ex.sample..

//just sample of Product contain Tag data
$products[0]['Tag'] = array('touch', 'phone', '3G', 'apple'); //iPhone
$products[1]['Tag'] = array('phone', '3G', 'BB'); //BB
$products[2]['Tag'] = array('touch', '3G', 'apple'); //iPad
$products[3]['Tag'] = array('3G', 'air card'); //3G air card

in this sample the most related to iPhone sort by priority are..

1. ipad (found in 3 tags)
2. BB (found in 2 tags)
3. aircard (only 1 matched)

How Cake's way using Model find() to get sub-query like this:

SELECT DISTINCT (product_id) AS id, (
/* sub-query counting same tags*/
SELECT COUNT(*) FROM tags as Tag
LEFT JOIN products_tags AS ProductTag ON ( Tag.id = ProductTag.tag_id )
WHERE product_id = id
AND Tag.name IN ( 'touch', 'phone', '3G', 'apple' )

) AS priority /*this is weight count by how many same tags*/
FROM  `tags` as Tag
LEFT JOIN products_tags AS ProductTag ON ( Tag.id = ProductTag.tag_id )
WHERE Tag.name
IN ( 'touch', 'phone', '3G', 'apple' ) 
ORDER BY priority DESC

SQL query above return exactly what I needed. but I can't find the way to parse params to CakePHP's AppModel->find() method.

Answer 1

If the result of this sql does not use pagination (assuming that related products in a normal webpage are 3-5 entries) why don't you use this complex SQL instead?

i.e. create a function relatedProducts() in your Product model

then use $this->query($sql)

sample code will be:

class Product extends AppModel {
  //..
  var $hasAndBelongsToMany = array("Tag");
  //..
  function relatedProducts($parameters){
    $sql = "select..... where ....$parameters...";
    return $this->query($sql);
  }
}

Then you can use it in the controller by

$this->Product->relatedProducts($some_parameters);