What Is The Equivalent Method For Mysql "on Duplicate Key Update" In Yii's Ar Model

Yii 1.1 General Discussion
1

Hi, i’m trying to insert data in a table using Yii’s AR model. I was wondering what is the equivalent method for MySQL’s ON DUPLICATE KEY UPDATE command in Yii’s AR.

Here is the method sample which inserts data from a huge xml feed.




public function insertData($name, $category, $brand, $price_unit, $weight_unit, $providers, $review_text, $flavor, $imageurl) {


        $this->productModel = new Products;

        $this->brandModel = new Brand;

        

        $this->brandModel->name = $brand;

        

        

        $this->brandModel->save();

        

        $this->productModel->name = $name;

        $this->productModel->category = $category;

        $this->productModel->brand = $brand;

        $this->productModel->weight_unit = $weight_unit;

        $this->productModel->price_unit = $price_unit;

        $this->productModel->flavors = $flavor;

        $this->productModel->providers = $providers;

        $this->productModel->review_text = $review_text;

        $this->productModel->image_path = $imageurl;

        

        

        $this->productModel->save();

        

    }

As you can see i’m doing a simple insertion using save(). But i would like to implement the ON DUPLICATE KEY command of mysql.

Here is the table in question:




+-------------+---------------+------+-----+---------+----------------+

| Field       | Type          | Null | Key | Default | Extra          |

+-------------+---------------+------+-----+---------+----------------+

| id          | int(11)       | NO   | PRI | NULL    | auto_increment |

| name        | varchar(100)  | YES  | MUL | NULL    |                |

| category    | varchar(50)   | YES  | MUL | NULL    |                |

| brand       | varchar(50)   | NO   | MUL | NULL    |                |

| weight      | decimal(10,2) | YES  |     | NULL    |                |

| weight_unit | varchar(30)   | YES  |     | NULL    |                |

| price       | decimal(15,2) | YES  |     | NULL    |                |

| price_unit  | varchar(30)   | YES  |     | NULL    |                |

| flavors     | varchar(45)   | YES  |     | NULL    |                |

| providers   | varchar(45)   | YES  | MUL | NULL    |                |

| review_text | text          | YES  |     | NULL    |                |

| image_path  | varchar(100)  | YES  |     | NULL    |                |

+-------------+---------------+------+-----+---------+----------------+

So, id is the primary key here, name has a index set on it for faster lookups. Category and brand are foreign keys.

The query which i want to replicate using Yii’s ar would be something like this ::




INSERT INTO tbl_products (name, category, brand, weight_unit, price_unit, flavors, providers, review_text, image_path) values ('.$this->parseXML[0] .', '. 

$this->parseXML[1] .',' 

.$this->parseXML[2] .','

. $this->parseXML[3] .','

. $this->parseXML[4] .','

. $this->parseXML[5] .','

.$this->parseXML[6] .','

.$this->parseXML[7] .','

.$this->parseXML[8].') 

ON DUPLICATE KEY UPDATE 

name = '.$this->parseXML[1].',

category = '.$this->parseXML[2].',

brand = '.$this->parseXML[3].'

So, what i the efficient method to do this using Yii’s AR. I’m trying to avoid using brute force methods, as much as i can.

Thanks, in advance.I would really appreciate any help that i can get on this.

thanks,

Maxx

2

I think,you are not taking auto-increment in a insertData funcation,so you have each time new entry for record, so you can insert multiple records with, $productModel->setIsNewRecord(true);

than your code to save.

so,every time new record will be generated,still you want to check,there is two way,

1)try_catch

try{

your insert code

}

catch{

$this->Yourmodel::model()->findByPk($key);

put fiels to change.

}

so,if insert fails,it will update your record.

  1. $this->productModel::model()->findByPk($key);

if($model){

  $model->value = $newvalue;

}else{

  $model = new Yourmodel();


  $model->value = $newvalue;

}

here,your record check for exist,with findBypk,if not found new record will be generated.Hope this will helpful to you.

3

Ok, thanks for you response. Yes i already had this method in mind before posting the question here. I know this method will work nicely, but is there a more direct way provided by Yii’s AR classes.