php - How to get data from different associated tables using containable in Cakephp?

Question

I have 3 tables: orders, discounts and products in the way that product has many discounts (discount times). Discount has many orders. in other words, it looks like: Product > Discount > Order.

I want to get data from 3 tables as the raw mySQL query below:

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:

$this->Order->virtualFields['benefit']='SUM(Product.product_price - Discount.product_discount)';
    $this->Order->virtualFields['number']='COUNT(Order.order_id)';
    $products = $this->Order->find('all',array('contain'=>array('Discount'=>array('Product'))),
        array( 'limit'=>20,
       'fields'=>array('benefit','number'), 
       'group'=>array('Discount.product_id','Product.product_name')));
    Debugger::dump($products);
    $this->set('products',$products);

But I got an error:

Database Error

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

 SQL Query: SELECT `Order`.`order_id`, `Order`.`user_id`
`Order`.`order_date`, `Order`.`payment`, `Order`.`discount_id`, `Order`.`total`, 
 (SUM(`Product`.`product_price` - `Discount`.`product_discount`)) AS `Order__benefit`, 
 (COUNT(`Order`.`order_id`)) AS `Order__number`, `Discount`.`discount_id`,
 `Discount`.`product_id`, `Discount`.`product_discount`,
 `Discount`.`start_time`, `Discount`.`end_time` 
 FROM `project`.`orders` AS `Order` 
 LEFT JOIN `project`.`discounts` AS `Discount` 
 ON (`Order`.`discount_id` = `Discount`.`discount_id`) WHERE 1 = 1

It seems that containable didnt work as it didnt not contain products table in the query.

EDIT: according to Dave's suggestion, I used JOIN:

$this->Order->recursive=-1;

    $this->Order->virtualFields['benefit']='SUM(Product.product_price - Discount.product_discount)';
    $this->Order->virtualFields['number']='COUNT(Order.order_id)';
    $option['joins'] = array(
        array('table'=>'discounts',
            'alias'=>'Discount',
            'type'=>'INNER',
            'conditions'=>array(
                'Order.discount_id = Discount.discount_id',
            )
        ),
        array('table'=>'products',
            'alias'=>'Product',
            'type'=>'INNER',
            'conditions'=>array(
                'Discount.product_id = Product.product_id'
            )
        )
    );
    $products = $this->Order->find('all',$option,
    array( 'limit'=>20,
       'fields'=>array('Discount.product_id','Product.product_name'), 
       'group'=>array('Discount.product_id','Product.product_name')));
    Debugger::dump($products);
    $this->set('products',$products);

However, what $products contains is only:

array(
(int) 0 => array(
    'Order' => array(
        'order_id' => '23567636',
        'user_id' => '1',
        'order_date' => '2013-11-16 16:03:00',
        'payment' => 'mc',
        'discount_id' => '2',
        'total' => '599',
        'benefit' => '7212',
        'number' => '19'
    )
)

)

but, what I want is:

How can I fix that? thanks in advance.

Answer 1

Containable is not the same as JOIN.

Containable does not join the queries into a single query, but for the most part creates completely separate queries, then combines the results for your viewing pleasure.

So - per your error, in the query that's being run on the orders table, there IS no Product.product_price field because those fields are available only in a completely separate query.

Try using JOINs instead.