How to get data from a database using count, sum and group by in Cakephp?

Question

I have 3 tables: products, discounts and orders. Im trying to get data by using this query:

SELECT discounts.product_id, products.product_name,
    sum(products.product_price - discounts.product_discount) as total_Amount,
    count(orders.order_id) as total_Number
    FROM products 
    inner join discounts on products.product_id = discounts.product_id
    inner join orders on discounts.discount_id = orders.discount_id
    group by discounts.product_id,products.product_name

this is what I did in Orderscontroller:

$this->Order->recursive=2;
    $hotest_products = $this->Order->find('all',
        array( 'limit'=>20,
       'fields'=>array('Discount.product_id', 'Product.product_name',
                       'SUM(Discount.product_discount) AS total_Amount',
                       'COUNT(Order.order_id) AS total_Number'
                       ), 
       'group'=>array('Discount.product_id','Product.product_name'), 
       'order'=>array('Discount.product_id ASC')));

    $this->set('products',$hotest_products);

and in view:

<?php foreach ($products as $product): ?>

        <tr>    
            <td><?php echo $product ['Discount']['product_id']; ?></td>
            <td><?php echo $this->Html->link($product['Product']['product_name'], array('controller'=>'products', 'action' => 'view', $product['Discount']['product_id'])); ?></td>
            <td><?php echo $product ['Product']['total_Amount']; ?></td>
            <td><?php echo $product ['Product']['total_Number']; ?></td>
            </td>
        </tr>

        <?php endforeach; ?>

However, I got an error:

Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'Product.product_name' in 'field list'

Another problem is that in the view, I dont know how to get total_Number and total_Amount as I dont know which model they belongs to.

EDIT: I found out that the code above didnt join the products table as its result was:

SELECT `Discount`.`product_id`, `Product`.`product_name`,
SUM(`Discount`.`product_discount`) AS total_Amount,
COUNT(`Order`.`order_id`) AS total_Number 
FROM `project`.`orders` AS `Order`
LEFT JOIN `project`.`users` AS `User` 
ON (`Order`.`user_id` = `User`.`user_id`) 
LEFT JOIN `project`.`discounts` AS `Discount` 
ON (`Order`.`discount_id` = `Discount`.`discount_id`) 
WHERE 1 = 1 GROUP BY `Discount`.`product_id`, `Product`.`product_name` 
ORDER BY `Discount`.`product_id` ASC LIMIT 20

But why didnt it include product table while I set recursive=2? the association between 3 talbes is: products > discounts > orders. (> means has many).

Please help me. Thanks in advance.

Can you make sure you have product_name column in the Product table? — Sam
– Sam, Commented Dec 1, 2013 at 3:08

Guillermo Mansilla · Accepted Answer · 2013-12-01 03:36:02Z

1

Change the Product.product_name to Products.product_name so from this

'fields'=>array('Discount.product_id', 'Product.product_name',
                       'SUM(Discount.product_discount) AS total_Amount',
                       'COUNT(Order.order_id) AS total_Number'
                       ),

To

'fields'=>array('Discount.product_id', 'Product.product_name',
                'SUM(Discount.product_discount) AS total_Amount',
                 'COUNT(Order.order_id) AS total_Number'),

edited Dec 1, 2013 at 3:36

Guillermo Mansilla

3,8842 gold badges32 silver badges34 bronze badges

answered Dec 1, 2013 at 3:16

Sam

2,7613 gold badges22 silver badges30 bronze badges

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

5 Comments

Tung Pham Over a year ago

I thought, Product in Product.product_name is the name of a Model. Even I change to Products.product_name, I get an error: Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'Products.product_name' in 'field list'

Guillermo Mansilla Over a year ago

Use "Product" in singular, not "Products"

Tung Pham Over a year ago

@GuillemoMansilla Do you mean Product.product_name? it still didnt work.

Guillermo Mansilla Over a year ago

Is Product associated with Order?

Tung Pham Over a year ago

@GuillemoMansilla no, it's not. Please take a look at the post above as I just edited it.

Collectives™ on Stack Overflow

How to get data from a database using count, sum and group by in Cakephp?

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related