Consider this database table layout: https://getkirby.com/docs/guide/virtual-pages/content-from-database
and this update statement: Db::update('comments', $data, ['slug' => $this->slug()]).
If, for whatever reason (like a joined table in children()), my $data array contains a key $data['non_existing_column_key'] = 'foo' that’s not in the current table (comments)), the update will fail silently.
Before the update I have to perform a table column name lookup like this:
$data = [
'text' => 'blabla',
'non_existing_column_key' => 'foo',
'user' => '...'
];
// create the SQL query
$col = Db::sql()->columns('comments');
// execute the SQL query and get the table names in an array
$cols = Db::query($col['query'], $col['bindings'])->pluck('name');
// remove non existent keys
$data = array_intersect_key($data, array_flip($cols));
// results in:
/*
$data = [
'text' => 'blabla',
'user' => '...'
];
*/
Db::update('comments', $data, ['slug' => $this->slug()])
Is there a more simplified way to write this table column lookup? Shouldn’t that be done in the update method? Or am I overlooking something?