代码之家 › 专栏 › 技术社区 › Zabs

使用Yii关系-添加附加条件

yii sql

Zabs · 技术社区 · 10 年前

我有一张具有以下结构的表格:

shop_purchases(purchase_id*, user_id, price, refunded) // primary key*

我的现有关系如下

'shopPurchasesCollection'   => array(self::HAS_MANY, 'ShopPurchases', 'user_id', 'with'=>'shopProduct', 'condition'=>'shopProduct.product_type_id=11 OR shopProduct.product_type_id=12', 'order'=>'purchase_time DESC'),

我想修改这个关系,基本上包括退款为NULL的行,我假设条件是所有需要更改的行。当前返回的SQL查询如下:

  SELECT `shopPurchasesCollection`.`purchase_id` AS `t1_c0`,`shopPurchasesCollection`.`user_id` AS `t1_c1`, `shopPurchasesCollection`.`price` AS `t1_c2`, `shopPurchasesCollection`.`purchase_time` AS `t1_c3`, `shopPurchasesCollection`.`GUID` AS `t1_c4`, `shopPurchasesCollection`.`refunded` AS `t1_c5`, `shopPurchasesCollection`.`fulfilled` AS `t1_c6`, `shopProduct`.`product_id` AS `t2_c0`, `shopProduct`.`colour_id` AS `t2_c1`, `shopProduct`.`organisation_id` AS `t2_c2`, `shopProduct`.`product_name` AS `t2_c3`, `shopProduct`.`product_description` AS `t2_c4`, `shopProduct`.`flash_name` AS `t2_c5`, `shopProduct`.`flash_zone` AS `t2_c6`, `shopProduct`.`flash_option` AS `t2_c7`, `shopProduct`.`product_type_id` AS `t2_c8`, `shopProduct`.`price` AS `t2_c9`, `shopProduct`.`display_image` AS `t2_c10`, `shopProduct`.`Guid` AS `t2_c11`, `shopProduct`.`active` AS `t2_c12`, `shopProduct`.`emailNotification` AS `t2_c13`, `shopProduct`.`url_value` AS `t2_c14`, `shopProduct`.`quantity` AS `t2_c15`, `shopProduct`.`instant_win` AS `t2_c16`, `shopProduct`.`multi_buy` AS `t2_c17`, `shopProduct`.`availability` AS `t2_c18`, `shopProduct`.`hat` AS `t2_c19`, `shopProduct`.`category` AS `t2_c20`, `shopProduct`.`email_user` AS `t2_c21`, `shopProduct`.`email_content` AS `t2_c22`
    FROM `shop_purchases` `shopPurchasesCollection`
         LEFT OUTER JOIN `shop_products` `shopProduct` 
              ON (`shopPurchasesCollection`.`GUID`=`shopProduct`.`Guid`)
   WHERE (   shopProduct.product_type_id=11 
          OR shopProduct.product_type_id=12
         ) AND (`shopPurchasesCollection`.`user_id`=5702079181)
ORDER BY purchase_time DESC

但是,我需要使用关系稍微修改一下,以便WHERE子句添加另一个条件,即:

WHERE shopPurchasesCollection.refunded IS NULL

下面是运行的手工编码SQL-我只想修改上面的关系以生成下面的查询:

  SELECT `shopPurchasesCollection`.`purchase_id` AS `t1_c0`, `shopPurchasesCollection`.`user_id` AS `t1_c1`, `shopPurchasesCollection`.`price` AS `t1_c2`, `shopPurchasesCollection`.`purchase_time` AS `t1_c3`, `shopPurchasesCollection`.`GUID` AS `t1_c4`, `shopPurchasesCollection`.`refunded` AS `t1_c5`, `shopPurchasesCollection`.`fulfilled` AS `t1_c6`, `shopProduct`.`product_id` AS `t2_c0`, `shopProduct`.`colour_id` AS `t2_c1`, `shopProduct`.`organisation_id` AS `t2_c2`, `shopProduct`.`product_name` AS `t2_c3`, `shopProduct`.`product_description` AS `t2_c4`, `shopProduct`.`flash_name` AS `t2_c5`, `shopProduct`.`flash_zone` AS `t2_c6`, `shopProduct`.`flash_option` AS `t2_c7`, `shopProduct`.`product_type_id` AS `t2_c8`, `shopProduct`.`price` AS `t2_c9`, `shopProduct`.`display_image` AS `t2_c10`, `shopProduct`.`Guid` AS `t2_c11`, `shopProduct`.`active` AS `t2_c12`, `shopProduct`.`emailNotification` AS `t2_c13`, `shopProduct`.`url_value` AS `t2_c14`, `shopProduct`.`quantity` AS `t2_c15`, `shopProduct`.`instant_win` AS `t2_c16`, `shopProduct`.`multi_buy` AS `t2_c17`, `shopProduct`.`availability` AS `t2_c18`, `shopProduct`.`hat` AS `t2_c19`, `shopProduct`.`category` AS `t2_c20`, `shopProduct`.`email_user` AS `t2_c21`, `shopProduct`.`email_content` AS `t2_c22`
    FROM `shop_purchases` `shopPurchasesCollection`
          LEFT OUTER JOIN `shop_products` `shopProduct` 
               ON (`shopPurchasesCollection`.`GUID`=`shopProduct`.`Guid`)
   WHERE (   shopProduct.product_type_id=11 
          OR shopProduct.product_type_id=12) 
          AND (`shopPurchasesCollection`.`refunded` IS NULL) 
          AND (`shopPurchasesCollection`.`user_id`=5702079181)
ORDER BY purchase_time DESC

有人能建议我如何修改模型中的Yii关系来实现这一点吗?

2 回复 | 直到 10 年前

Kai 10 年前

尝试将新条件添加到 CDbCriteria 在您的 search 功能:

$criteria = new CDbCriteria;
$criteria->addCondition('shopPurchasesCollection.refunded IS NULL');

你也可以这样定义你已经具备的条件。这将使代码的读者更容易理解这些条件。

topher Rushow Khan 10 年前

@user714965的答案是正确的。然而,如果你想改变关系本身, 你可以改变它 condition 。列名称应消除歧义。

'shopPurchasesCollection'   => array(
    self::HAS_MANY,
    'ShopPurchases',
    'user_id',
    'with'=>'shopProduct',
    'condition'=>'(shopProduct.product_type_id=11 OR shopProduct.product_type_id=12) '.
         'AND shopPurchasesCollection.refunded IS NULL',
    'order'=>'purchase_time DESC'),