CakePHP model associations - Random order on retrieve of associated model for HABTM

Question

I am retrieving data:

$mydata = $this->ProductList->find('all', array('order' => 'rand()', 'conditions' => array('name' => 'we love')));

I have set up a HABTM relationship to the Product model. As you can see, I am fetching all products in the 'we love'-list. Now, I want those Products I am retrieving to be randomised. But they are not, instead the MySQL is randomised on the ProductList model as you can see in the SQL. Why is that? How can I get the random fetch on the Products instead?

Resulting MySQL query:

SELECT `ProductList`.`id`, `ProductList`.`name` FROM `database`.`product_lists` AS `ProductList` WHERE `name` = 'we love' ORDER BY rand() ASC

SELECT `Product`.`id`, `Product`.`category_id`, `Product`.`name`, `Product`.`price`, `Product`.`description`, `ProductListsProduct`.`product_list_id`, `ProductListsProduct`.`product_id` FROM `database`.`products` AS `Product` JOIN `database`.`product_lists_products` AS `ProductListsProduct` ON (`ProductListsProduct`.`product_list_id` = 3 AND `ProductListsProduct`.`product_id` = `Product`.`id`)

Answer 1

EDIT:

There are so many different ways to approach this; to get a random product from a user's product list. You could do it with PHP - just find all of the products and then use rand() to pick on from the returned array. You could set a Model query condition. The list goes on...

I would probably create an alias to the Product model in ProductList called RandomProduct. You could set the query for the retrieved product when you set the relationship:

public $hasMany = array(
    'RandomProduct' => array(
        'className'     => 'Product',
        'foreignKey'    => 'product_list_id',
        'order'         => 'Rand()',
        'limit'         => '1',
        'dependent'     => true
    )
);

You can then use the containable behavior so that this model is only retrieved when you need it. (You wouldn't need to do this if recursive finds are greater than -1, but I usually do that as best practice so that my models only query for the data that they need.) The following would return any ProductList called 'we love' and a "random" product associated with that list.

$mydata = $this->ProductList->find(
    'all', 
    array(
        'conditions' => array(
            'name' => 'we love'
            )
        ),
        'contain' => array(
            'RandomProduct'
        )
    );