Yii2 Insert Ignore / Insert duplicates

Question

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.

Solution 1

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 Viewlike.us - Yii2 Insert and Ignore duplicates:

$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();

Solution 2

Since Yii 2.0.14 there is a upsert() method which may be used for this.

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

Solution 3

The key to your question is method $db->createCommand(). Internally it uses $commandClass public property that is by default yii\db\Command. You can change it in your app config to override command methods like batchInsert() and others. Following is a sample from my app:

'db' => [
    'class' => 'yii\db\Connection',
    'charset' => 'utf8',
    'enableSchemaCache' => YII_ENV_PROD,
    'commandClass' => 'app\db\MyCommand'
],

And your command class might look like:

class MyCommand extends yii\db\Command
{
    public function batchInsert($table, $columns, $rows)
    {
        parent::batchInsert($table, $columns, $rows);
        $sql = $this->getRawSql();
        $sql .= ' ON DUPLICATE KEY UPDATE';
        $this->setRawSql($sql);

        return $this;
    }
}

But I would rather introduce separate method, e.g. batchInsertUpdate() or something.