CODEWITHSUNDEEP
mysqlcakephp
Tung Pham
Tung Pham

Reputation: 599

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

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.

Upvotes: 0

Views: 864

Answers (1)

Sam
Sam

Reputation: 2761

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

Upvotes: 1

Related Questions