cakephp find('all') not returning properly

Question

So here is my latest issue: I run this query in my Cakephp controller:

$acctRenewLast2Entries = $this->AccountRenew->find
    (
        'all',
        array
        (
            'conditions' => array('Acc_Id' => $plan["Account"]["Acc_Id"]),
            'order' => array('AccR_Id' => 'DESC')
        )
    );

I am expecting 4 records for this SQL statement. Instead, on running Debug in my controller, this is what I get for each row for above (see first record):

app/controllers/admins_controller.php (line 2584)

1

app/controllers/admins_controller.php (line 2584)

Array
(
    [AccountRenew] => Array
        (
            [AccR_Id] => 470
            [AccR_Date] => 2012-06-23 01:21:11
            [AccR_Hstart_Date] => 2012-06-23 01:21:11
            [AccR_Hend_Date] => 2012-08-23 01:21:11
            [AccR_End_Date] => 2013-08-23 01:21:11
            [AccR_Status] => PAID
            [AccR_Reason] => RENEWAL
            [Inv_Id] => 467
            [Inac_Id] => 
            [Acc_Id] => 196
            [AccT_Id] => 44
            [Amount] => 16
            [AccP_Id] => 0
        )

)

app/controllers/admins_controller.php (line 2584)

Array
(
    [AccountRenew] => Array
        (
            [AccR_Id] => 465
            [AccR_Date] => 2012-06-23 01:17:35
            [AccR_Hstart_Date] => 2012-06-23 01:17:35
            [AccR_Hend_Date] => 2012-07-23 01:17:35
            [AccR_End_Date] => 2012-07-23 01:17:35
            [AccR_Status] => PAID
            [AccR_Reason] => RENEWAL
            [Inv_Id] => 462
            [Inac_Id] => 
            [Acc_Id] => 196
            [AccT_Id] => 41
            [Amount] => 16
            [AccP_Id] => 0
        )

)

app/controllers/admins_controller.php (line 2584)

Array
(
    [AccountRenew] => Array
        (
            [AccR_Id] => 269
            [AccR_Date] => 2012-06-06 10:15:56
            [AccR_Hstart_Date] => 2012-06-06 17:15:56
            [AccR_Hend_Date] => 2012-06-20 17:15:56
            [AccR_End_Date] => 2012-06-20 10:15:56
            [AccR_Status] => TRIAL
            [AccR_Reason] => 
            [Inv_Id] => 0
            [Inac_Id] => 
            [Acc_Id] => 196
            [AccT_Id] => 0
            [Amount] => 0
            [AccP_Id] => 0
         )

)

Now, when I run sql_dump, I get the following query that was run :

SELECT `AccountRenew`.`AccR_Id`, `AccountRenew`.`AccR_Date`, `AccountRenew`.`AccR_Hstart_Date`, `AccountRenew`.`AccR_Hend_Date`, `AccountRenew`.`AccR_End_Date`, `AccountRenew`.`AccR_Status`, `AccountRenew`.`AccR_Reason`, `AccountRenew`.`Inv_Id`, `AccountRenew`.`Inac_Id`, `AccountRenew`.`Acc_Id`, `AccountRenew`.`AccT_Id`, `AccountRenew`.`Amount`, `AccountRenew`.`AccP_Id` FROM `account_renews` AS `AccountRenew` WHERE `Acc_Id` = 196 ORDER BY `AccR_Id` DESC       4   4   

And when I run the above query in MySQL, I do get all my 4 records, including the first one which in the array appears as 1 (way up top of my write-up).

I sincerely hope someone out there can help, because I spent the last 1.5 days without any luck as to why MySQL pulls up the complete set, but Cake only seems to retrieve the last 3, and replace the first record with an array of "1".

Thanks in advance!

Answer 1

@user996302 this issue seems remotely connected to one of my Cake Lighthouse tickets you pointed out.

@GMOAdmin I suspect that there could be a problem with the name of your model as the word "renew" is a verb and since it has no plural form this could somehow be obstructing the CakePHP conventions as the Inflector class may not be able to translate this.
The correct noun is renewal and it's plural form: renewals. You can try renaming (according to the convetions) the DB table, Model - name and classname, Controller - name and classname and see if that works.

You can test if Inflector is handling this correctly via the following Inflector methods:

Inflector::pluralize($singular), Inflector::classify($tableName), Inflector::tableize($camelCase)

A quick fix would be to issue the working query with $this->ModelName->query($queryToRun); this way you can go around this since, as you say, the query runs correctly when ran over the DB. Overall this is truly and interesting issue and I suggest you have the CakeCore team look at it - if it is reproducable then this is a BUG and it need fixin.

Answer 2

Okay, I think I have found the answer. At least, this was causing the problems for me.

The CakePHP counterCache

What I had:

public $hasMany = array(
    'Flight' => array(
        'className' => 'Flight',
        'foreignKey' => 'user_plane_id',
        'counterCache' => true
     )
);

Very stupid, but it did cause the problems. So perhaps you are having a counterCache as well? Or maybe you have set another key in the relationships wrong?

If you don't see the problem at my code snippet. I should have added the counterCache to the $belongsTo instead of the $hasMany. So it would be something like this:

public $belongsTo = array(
    'UserPlane' => array(
        'className' => 'UserPlane',
        'foreignKey' => 'user_plane_id',
        'counterCache' => true
    )
);

note: I am running CakePHP 2.x and not 1.3!

Answer 3

Very strange problem.

I don't know what's happening, but I read recently a bug report which sounds a lot like your problem: ticket.

You may try as suggested by the author: put the param $showHTML (cake docs) as true:

debug($var, true, true);

Hope that helps.