如何从Cakephp中使用containable从不同的相关表获取数据？ [英] How to get data from different associated tables using containable in Cakephp?

问题描述

我有3张表：订单，折扣和产品以产品有许多折扣（折扣时间）的方式。 折扣有许多订单。换句话说，它看起来像：产品> 折扣> 订单



我想从下面的原始mySQL查询中获取3个表中的数据：

  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 
在products.product_id = discounts.product_id 
上的内部连接折扣discounts.discount_id = orders.discount_id 
的内部连接订单discounts.product_id，products.product_name 
  

这是我做的：

  $ 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（'contains'=> array（'Discount'=> array（'Product'））），
 array（'limit'=> 20，
'fields'=> array（'benefit'，'number'），
'group'=& 'Product.product_name'）））; 
 Debugger :: dump（$ products）; 
 $ this-> set（'products'，$ products）; 
  

但我遇到了一个错误：

 数据库错误
 
错误：SQLSTATE [42S22]：未找到列：1054字段列表中的未知列'Product.product_price'
 
 SQL查询：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`.`countcount` = `Discount`.`discount_id`）WHERE 1 = 1 
  

看起来，

编辑：根据Dave的建议，我使用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）; 
  

但是，$ products包含的只是：

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

但是，我想要的是：

如何解决这个问题？提前感谢。

解决方案

Containable 与 JOIN 。

可容纳不会将查询加入单一查询，大部分会创建完全独立的查询，然后将结果合并为您的观看乐趣。

因此，在您的错误中，在订单表上，没有 Product.product_price 字段，因为这些字段仅在完全独立的查询中可用。

尝试使用 JOINs 。

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.

解决方案

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.

这篇关于如何从Cakephp中使用containable从不同的相关表获取数据？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！