Conditional column update

[webbby]

Hey there @kfirba first let me thank you for your effort to bring such an useful tool.

May I suggest to add basic support for conditional updated columns? I've started using your tool but soon I realised that I had to edit the generated query myself. Here's the case:

My table had updated_at column so in my insertOrUpdate values I had to set it with the current time before the db request. The problem is the query treats it as an update value when it actually should be set only when another of the columns is updated. On the other hand you can't excluded it because you need it to show when the update has occurred.

So I had to add the following line

on duplicate key update 
    updated_at=IF(name=VALUES(name), updated_at, VALUES(updated_at) )

right after the on duplicate key update clause before all other updating columns so that it checks the value before name is set in order to compare the values between the new value and the one of the table record. As you see the updated_at receives the new value only when name is different than the current value of the table. Which brings the idea for conditional update columns where a conditional column is checked against change of value among other given columns like

[ 'updated_at' => ['name', 'color', ....] ] 

where it generates the following line in the query

updated_at=IF(
   name=VALUES(name) && color=VALUES(color) && ...., 
   updated_at, VALUES(updated_at) 
)

[webbby]

OK I've played with your class and gave a hand

public function generate($table, $rows, array $exclude = [], array  $dependentlyUpdating = []) {
    $columns = array_keys($rows[0]);
    $columnsString = implode('`,`', $columns);
    $values = $this->buildSQLValuesStringFrom($rows);
    $updates = $this->buildSQLUpdatesStringFrom($columns, $exclude, $dependentlyUpdating);

    $query = vsprintf('insert into `%s` (`%s`) values %s on duplicate key update %s', [
        $table, $columnsString, $values, $updates,
    ]);

    return new QueryObject($query, $this->extractBindingsFrom($rows));
}

// ....... 

protected function buildSQLUpdatesStringFrom($cols, $exclude, $dependentlyUpdating) {
    $dependentColumns = array_keys($dependentlyUpdating);
    $notDependentCols = array_diff($cols, $dependentColumns);
    $str = '';

    foreach ($dependentlyUpdating as $key => $value) {
        if(!is_array($value) || empty($value)) {
            /* The case when not specific depending on columns are given so
            make it depend on all except itself and excluded */
            $index = array_search($key, $exclude);
            if(is_bool($index)) {
                $value = is_bool($index) ? array_merge($exclude,[$key]) : $exclude;
                $value = array_diff($cols, $value);
            }
        }
        $str .= "{$key}=IF(".(
            trim(
                array_reduce($value, function ($result, $column) {
                    return $result."`{$column}`=VALUES(`{$column}`)&&";
                }, ''), '&&'
            )
        ).",`{$key}`,VALUES(`{$key}`)),";
    }
    return  $str.trim(array_reduce(array_filter($notDependentCols, function ($column) use ($exclude) {
        return ! in_array($column, $exclude);
    }), function ($updates, $column) {
        return $updates . "`{$column}`=VALUES(`{$column}`),";
    }, ''), ',');
}

and the invoke looks like

// 'updated_at' depending on all columns except itself and excluded
$queryGenerator -> generate($tableName, $campaigns, $excludedColumnsFromUpdate, [
    'updated_at' => null
]);

or

// 1. Depending on 'name', 'status'  2.  'color' on all except itself and excluded
$queryGenerator -> generate($tableName, $campaigns, $excludedColumnsFromUpdate, [
    'updated_at' => ['name', 'status'], 'color' =>  'on all'
]);

I haven't tested it yet it works in my case but I'm almost sure it's not reliable yet. For instance what happens if

• depending depends on another depending
• or all columns are depending and no regular columns have left
• or among explicitly entered dependent columns there are excluded
• ...