CODEWITHSUNDEEP
cakephp-2.0
mickburkejnr
mickburkejnr

Reputation: 3690

How do I count records inside a query in CakePHP?

I have quite a complicated problem (for me anyway) and I'm stuck as to what to do next.

First, I will give you some background to the project. This project is for me to keep a record of customers and to list all of the services that are linked to these customers. I have three tables: Customer Products CustomerRecordHub

The Customer table and the Products table hold information on their respective parties. The CustomerRecordHub table basically links these two tables together. So the table will hold the Customer ID and the Product that is associated with that customer.

So if a customer has an ID of 1, and that customer has three products, in the Products UD field they would have 1, 2 and 3. So the table would be:

ID | CompanyID | ProductID
1  |          1         |        1
2  |          1         |        2
3  |          1         |        3

I use the CustomerRecordHub table as the main reference table, and from this table I then query the Customer and Product tables. I have the following relationships set up for CustomerRecordHub:

<?php
public $belongsTo = array(
    'Customer' => array(
        'className' => 'Customer',
        'foreignKey' => 'id',
    ),
    'Product' => array(
        'className' => 'Product',
        'foreignKey' => 'id',
    )
);
?>

I can query this table and retrieve all the records I want from the other two linked tables without any issue.

Now, the problem I do have is that on one page I want to list all of the customers I have, and next to that I want to show how many products each customer has. So if we use the example I gave, for the Customer with an ID of one, there are three products linked to this customer. So on the page I want to have the Customer details displayed, and under the heading "Products" I want to get the number of products displayed (in this case the number 3 should be displayed).

Ordinarily I would use a COUNT command in SQL, but I'm trying to keep the code as strict to CakePHP's own conventions as possible and not use raw SQL inside the code.

Currently, the code I'm using is this:

<?php

$this->loadModel('Customerrecordhub');

$customers = $this->Customerrecordhub->find('all', 'recursive' => 2));
$this->set('customers', $customers);

?>

In the HTML, I'm pumping all of the data in to a table inside a foreach loop.

What should I use to count the records?

Upvotes: 0

Views: 2191

Answers (1)

mensch
mensch

Reputation: 4411

There is a special count parameter for the find method, but that probably won't fit your needs, as you want to show more than just the counted products.

It's best to define COUNT in the fields parameter of the find method. So: 

'fields' => array('Model.field1', 'COUNT(Model.field2) AS products_count')

Upvotes: 1

Related Questions