How to count the result using find() in CakePHP 2.2.4?

Question

I am using Cakephp 2.2.4 and I need to retrive a list of Lead that belongs to the user (id = 106).

The result of the query is:

array(
    (int) 0 => array(
        'Lead' => array(
            'id' => '6',
            'user_id' => '106',
            'date' => '2012-12-31 22:15:23',
            'ip' => '127.0.0.1',
            'service_id' => '1',
            'location' => 'Rome',
            'message' => 'Message Message',
            'telephone' => null,
            'active' => null
        ),
        'User' => array(
            'id' => '106',
            'email' => 'daje@daje.it',
            'pwd' => '0433c024cb08be13000d59a347e640482843f46f177e95749dc6599c259617fd3491dcb940b47693cbbc7f65a2cc5ef62deca2e600c1be133ad54170f7d1fbd1',
            'role_id' => '3',
            'active' => '1'
        ),
        'Service' => array(
            'id' => '1',
            'name' => 'Primo servizio'
        ),
        'Estimate' => array(
            (int) 0 => array(
                'id' => '1',
                'lead_id' => '6',
                'user_id' => '106'
            )
        )
    )
)

It looks good but I need to count the Estimates (Estimate array), I would like to retrive the number of the estimates, and not the array with all the fields (of estimates table).

How can i do it?

I need :

Lead array as it shown

User array as it shown

Service array as it shown

Estimate (only the total number of the estimates... in this case 1)

The find is very simple:

$options = array('conditions' => array('User.id' => 106));

debug($this->Lead->find('all', $options));

score 3 · Accepted Answer · answered Jan 09 '13 at 17:55

3

Try something like this, not 100% sure it'll work but worth a go if not I'd advise trawling the cakephp docs for retrieving your data:

$options = array(
    'fields' => array('Lead.*', 'User.*', 'Service.*', 'count(Estimate.id)'),
    'conditions' => array('User.id' => 106)
);

answered Jan 09 '13 at 17:55

cowls

24,013
8
48
78

I get the follow error: Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'Estimate.id' in 'field list' SQL Query: SELECT `Lead`.*, `User`.*, `Service`.*, count(`Estimate`.`id`), `Lead`.`id` FROM `tuofisco`.`leads` AS `Lead` LEFT JOIN `tuofisco`.`users` AS `User` ON (`Lead`.`user_id` = `User`.`id`) LEFT JOIN `tuofisco`.`services` AS `Service` ON (`Lead`.`service_id` = `Service`.`id`) WHERE `User`.`id` = 106 – Dail Jan 09 '13 at 18:07
Hmm, actually you may need to set up a join in the find query manually for the Estimate model. Might be helpful -> http://stackoverflow.com/questions/5079908/cakephp-find-method-with-join – cowls Jan 09 '13 at 18:09

DeaconDesperado · Answer 2 · 2013-01-09T18:19:34.880

0

Without diving too far into the internals of the Cake ORM, assuming you don't need to do this immediately at query time, couldn't you just get the count of the estimate array programmatically after the fetch?

http://php.net/manual/en/function.count.php

$leads = $this->Lead->find('all',$options);
foreach($leads as $lead){
  //get the number of estimates for every lead result
  $lead_num = count($lead['Estimate']);
}

Alternatively, you could manually write a join query for this one fetch and execute it using the Cake Model class's query method. Without knowing the specifics of your table schema and model relations its hard to give specifics about how to structure the query, but this shouldn't be too difficult by just look at your table spec and extracting a sql COUNT statement for every Estimate with given id.

edited Jan 09 '13 at 18:19

answered Jan 09 '13 at 18:00

DeaconDesperado

9,977
9
47
77

Yes, but, I would like to retrive it directly without count the estimates of each leads. How can I do it ? – Dail Jan 09 '13 at 18:05
Gonna need to perform a join on the table in SQL query syntax proper, you may want to look into manually writing the query just for this fetch and executing it directly using the `query` method on Cake's Model http://api21.cakephp.org/class/model – DeaconDesperado Jan 09 '13 at 18:14

How to count the result using find() in CakePHP 2.2.4?

2 Answers2