5

I'm aware that Cake HABTM associations are tricky at the best of times, but I seem to be making life even harder for myself...

If I want to return a random Item from the db, I can do it as follows in the Item model:

$random = $this->find('first', array(
    'order' => 'rand()'
));

and if I want to find all the Items that are in a certain Category (where Item has a HABTM relationship to Categories), I know I can get a result set through $this->Categories->find.

My question is: how can I combine the two, so I can return a random Item that belongs to a specified Category? Is there any easy way? (If not, I'll gladly take any suggestions for a laborious way, as long as it works ;)

ETA: I can get some of the way with Containable, maybe: say I add the line

'contain' => array('Categories'=>array('conditions'=>array('Categories.id'=>1))),

then the Item results that I don't want come back with an empty Categories array, to distinguish them from "good" items. But really I don't want said Item results to be returned at all...

ETA(2): I can get a workaround going by unsetting my results in the afterFind if the Categories array is empty (thanks to http://nuts-and-bolts-of-cakephp.com/2008/08/06/filtering-results-returned-by-containable-behavior/ for the tip), and then having my random find function not give up until it gets a result:

while (!is_array($item)) {
    $item = $this->random($cat);
}

but ugh, could this be any clunkier? Anyway, time for me to stop editing my question, and to go away and sleep on it instead!

thesunneversets
  • 2,560
  • 3
  • 28
  • 46
  • 1
    ORDER BY rand() is an antipattern. It will result in an utterly slow query! I guess you just want the first X rows returned: rand() will be generated for all rows, then all rows will be sorted according to it, whereby rand() is not an indexed field. Just thereafter will be the first X of it returned. – sibidiba Nov 08 '10 at 09:22
  • Yeah, I saw some red flags being raised over ORDER BY rand(). It works fine for my tiny test database, I can imagine it being impossible to bear as the db grows. Still, finding a better way to retrieve a random record seemed like the next problem to tackle, after dealing with my current one! – thesunneversets Nov 08 '10 at 17:07

1 Answers1

5

Try this:

<?php
$this->Item->bindModel(array('hasOne' => array('ItemsCategory')));
$random = $this->Item->find('all', array(
  'order' => 'rand()',
  'conditions' => array('ItemsCategory.category_id' => '1')
  ));
?>
pawelmysior
  • 3,190
  • 3
  • 28
  • 37
  • 1
    Good call, creating an association on the fly, exactly what I needed. Of course it took me half an hour to get it to work... because I'd forgotten to properly undo my changes to AfterFind. Thanks! – thesunneversets Nov 09 '10 at 07:12