Cakephp returns empty but sql query has results

Question

I have been fighting with this code:

function getNextActionFObyBalance($when) {

  $theQuery = $this->find('first', array(
   'fields' => array(
     'Contract.id',
     'Contract.start_balance'
    ),
    'conditions' => array(
    'AND' => array(
      'Status.next_action_by' => 'frontoffice',
      'Status.status_type' => 'active',
      'Status.visibility' => 'frontoffice',
      'OR' => array(
        'Contract.next_action_on' => null,
    'Contract.next_action_on <=' => $when
      )
    )),
    'order' => 'Contract.start_balance DESC',
    'recursive' => 0,
  ));
  return $theQuery;
}

I have enabled logging on the MySQL server at this is what the server indicates that CakePHP is requesting:

SELECT `Contract`.`id`, `Contract`.`start_balance` FROM `contracts` AS `Contract` LEFT JOIN `statuses` AS `Status` ON (`Contract`.`status_id` = `Status`.`id`) LEFT JOIN `users` AS `User` ON (`Contract`.`user_id` = `User`.`id`)  WHERE ((`Status`.`next_action_by` = 'frontoffice') AND (`Status`.`status_type` = 'active') AND (`Status`.`visibility` = 'frontoffice') AND (((`Contract`.`next_action_on` IS NULL) OR (`Contract`.`next_action_on` <= '2010-09-13 10:13:04'))))   ORDER BY `Contract`.`start_balance` DESC  LIMIT 1

if I use that in the phpmyadmin tool, I get exactly what I was expecting 1 record with two fields. BUT CakePHP just gives me an empty result set. Can anyone enlighten me?

PS the code was working but I can figure out what changed!

Answer 1

Presumably this method is defined in models/contract.php?

The recursive = 0 statement looks a bit suspect to me. Are the models correctly related in their respective model files?

Have you tried loadModel in case the associations aren't working properly?

It would be useful to see the relationship definitions from the respective models.

--EDIT-- I've formatted the code from your comment here as I can't edit your OP

var $belongsTo = array(
    'Status' => array( 
        'className' => 'Status', 
        'foreignKey' => 'status_id', 
                     ), 
    'User' => array( 
        'className' => 'User', 
        'foreignKey' => 'user_id', 
                     )
                 ); 

var $hasMany = array( 
    'Transaction' => array( 
        'className' => 'Transaction', 
        'foreignKey' => 'contract_id', 
        'dependent' => false, 
                      )
                 );

Answer 2

The problem was with a stub to do some post processing afterFind. The problem is that I have completely forgotten to return $results; I found the error by doing a step by step debugging down the find method in model.php. Found that the after find was called at some point and went to check my afterFind. Took my about 4 hours for a simple error but I am learning!