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!

Do you have anything in the afterFind method of any of your models? AppModel, or the current model? — Travis Leleu
– Travis Leleu, Commented Sep 13, 2010 at 16:36

RaScoop · Accepted Answer · 2010-09-14 10:48:36Z

1

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!

answered Sep 14, 2010 at 10:48

RaScoop

535 bronze badges

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

Comments

Leo · Accepted Answer · 2010-09-13 17:31:02Z

0

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, 
                      )
                 );

edited Sep 13, 2010 at 17:31

answered Sep 13, 2010 at 16:44

Leo

6,5712 gold badges32 silver badges49 bronze badges

3 Comments

RaScoop Over a year ago

var $belongsTo = array( 'Status' => array( 'className' => 'Status', 'foreignKey' => 'status_id', 'conditions' => '', 'fields' => '', 'order' => '' ), 'User' => array( 'className' => 'User', 'foreignKey' => 'user_id', 'conditions' => '', 'fields' => '', 'order' => '' ) ); var $hasMany = array( 'Transaction' => array( 'className' => 'Transaction', 'foreignKey' => 'contract_id', 'dependent' => false, 'conditions' => '', 'fields' => '', 'order' => '', 'limit' => '', 'offset' => '', 'exclusive' => '', 'finderQuery' => '', 'counterQuery' => '' ) );

RaScoop Over a year ago

Thank you for your prompt reply. The method is indeed in the contract.php in models. Changing the recursive to 2 did not help either. What bugs me more than anything is that Cake is requesting result with the correct SQL from the server. But its still returning empty result set.

Leo Over a year ago

That code is in the Contract model, I guess. What does the Status model look like?

Collectives™ on Stack Overflow

Cakephp returns empty but sql query has results

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related