How do I count records inside a query in CakePHP?

Question

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?

mensch · Accepted Answer · 2012-04-19 10:51:38Z

1

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')

answered Apr 19, 2012 at 10:51

mensch

4,4113 gold badges31 silver badges50 bronze badges

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

Collectives™ on Stack Overflow

How do I count records inside a query in CakePHP?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related