nunorbatista nunorbatista - 6 days ago 6
PHP Question

Yii2 Insert Ignore / Insert duplicates

I'm relatively new to Yii2 and so far I'm enjoying it very much.
I'm however finding an issue related with Database Inserts, more specifically MySQL.

The question is fairly simple: I'm inserting values into a table and I need to check for duplicates so my idea was to use an INSERT IGNORE or ON DUPLICATE KEY UPDATE.

This is the code I'm using for the insert:

$db->createCommand()
->insert('sdg_fb_apps', [
'fb_id' => $app->id,
'page_id' => $page_id,
'name' => $app->name,
'app_id' => $app_id,
'app_name' => $app_name,
])
->execute();


I have to unique keys: id and app_id and if I run this code I'll obviously be thrown an Exception from Yii.

There's this extension but as far as I understood only works with Yii 1.0: http://www.yiiframework.com/extension/rdbcommand

I also found this code snippet on GitHub that looks like this:

class MyQueryBuilder extends yii\db\mysql\QueryBuilder
{
public function batchInsert($table, $columns, $rows)
{
$sql = parent::batchInsert($table, $columns, $rows);
$sql .= 'ON DUPLICATE KEY UPDATE';
return $sql;
}
}


I didn't understand however how to extend the Querybuilder, can it be through a component?

I kind of accomplished what I was looking for by doing a query to see if the app_id was already in the database before and then only insert if they don't exist, however this is supposed to have to scale to huge numbers and this method might not be the best.

Any guidance will be appreciated, thanks in advance.

Answer

More than one year passed and I found this open question here. I see this has some traction, so it's good to add a proper answer.

In Yii2 the idea is to find the model, check if exists and then save. The framework will automatically update or insert, here's a snippet I got from here.

$model::YourModel::findOne(ID); // findOne() searches for the table key, you can use find()->where('=', 'column', 'name')->one(); to identify the model.

if(!$model):
    $model = new YourModel;
endif;

$model->column_1 = 'text';
$model->column_2 = 123; // integer
$model->save();