CODEWITHSUNDEEP
sqlyii
Zabs
Zabs

Reputation: 14142

Working with Yii relations - add additional condition

I have a table with the following structure:

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

I have an existing relation as follows

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

I want to modify this relation to basically include rows where refunded is NULL, I presumed the condition is all the is needed to be altered. Currently the SQL query returned is as follows:

  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

However I need to amend this slightly using the relation so that the WHERE clause adds another condition which will say the following:

WHERE shopPurchasesCollection.refunded IS NULL

Below is the hand coded SQL that works - I just want to amend the relations above to generate the query below:

  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

Can anyone suggest how I amend the Yii relation in the model to achieve this?

Upvotes: 0

Views: 328

Answers (2)

Kai
Kai

Reputation: 39641

Try adding a new condition to your CDbCriteria in your search function:

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

You can also define the conditions which you already have like this. It will make the conditions a bit more obvious to the reader of your code.

Upvotes: 3

topher
topher

Reputation: 14860

@user714965's answer is correct. However, if you want to alter the relation itself, you can alter its condition. The column names should be disambiguated.

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

Upvotes: 0

Related Questions