-
Notifications
You must be signed in to change notification settings - Fork 9
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Conditional column update #2
Comments
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.
|
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
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
right after the
on duplicate key update
clause before all other updating columns so that it checks the value beforename
is set in order to compare the values between the new value and the one of the table record. As you see theupdated_at
receives the new value only whenname
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
where it generates the following line in the query
The text was updated successfully, but these errors were encountered: