Finding based on MySQL function in CakePHP

Question

I'm trying to select database rows in CakePHP based on the MAX() value of a field from another table. Here is the SQL I am trying to execute:

SELECT `questions`.* FROM `tests`,`questions` GROUP BY `questions`.`id` HAVING  `questions`.`test_id` = MAX(`tests`.`id`);

(Or something equivalent. Basically, I'm trying to just grab all of the rows from the questions table where test_id equals the highest ID value in the tests table.)

The closest thing I have been able to do in CakePHP using find() is something that runs two queries:

$current_test = $this->find('first',array('fields'=>array('MAX(Test.id) as current_test')));

$questions = $this->find('all',array(
    'conditions'=>array('Question.test_id'=>$current_test[0]['current_test'])
));

It gets me the results I need, but it seems unnecessary. Is there any way in CakePHP to put this into a single query?

Personally I think the CakePHP code is better than your MySQL query. In which you're selecting two tables without a JOIN. That seems unnecessary. — Jason McCreary
– Jason McCreary, Commented May 17, 2011 at 21:19

stevecomrie · Accepted Answer · 2011-05-17 22:17:43Z

2

If you're model relationships are setup properly and I understand your database, the following should work (assuming this code is being written INSIDE your Test Model. If it's outside your Test Model (i.e. in a Controller), you'll need to use $this->Test->find.

$test = $this->find( 'first', array(
    'order'     => 'Test.id desc',
    'recursive' => 1,
));

Which should return something like:

$test = array(
    [Test] => array(
        'id'  => X,
        'etc' => '...'
    ),

    [Questions] => array( 
        [0] => array( 'test_id' => X, 'question_id' => 1  ),
        [1] => array( 'test_id' => X, 'question_id' => 7  ),
        [2] => array( 'test_id' => X, 'question_id' => 10 ),
    ),
);

edited May 17, 2011 at 22:17

answered May 17, 2011 at 21:58

stevecomrie

2,48320 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Finding based on MySQL function in CakePHP

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related