Placeholders must have a trailing [] if they are to be expanded with an array of values

Question

In Drupal 8 I am trying to insert some data in my custom table. While executing query I am getting error:

InvalidArgumentException: Placeholders must have a trailing [] if they are to be expanded with an array of values. in Drupal\Core\Database\Connection->expandArguments()

Please find my code below:

$qt = new \stdClass();
$qt->name = 'test name';
$qt->machine_name = 'test machine name';
$qt->settings = array('setting1'=>value1, 'setting2'=>value2);

$query = \Drupal::database()->insert('my_custom_table');
$query->fields([
  'machine_name',
  'name',
  'settings',
]);
$query->values([
  $ob->machine_name,
  $ob->name,
  $ob->settings,
]);
$query->execute();

If I am giving values like below it is working. Please help me whats wrong with above code.

$query->values([
  'test',
  'ttt',
  'dfs',
]);
$query->execute();

Answer 1

This is all explained in the Drupal documentation. If you are forming a query that can take an array of arguments, then the (arbitrary) placeholder string needs to indicate this. So a placeholder of :column will only work for a single value, but if you want to use an array of values you need to use the syntax :column[].

See https://www.drupal.org/docs/drupal-apis/database-api/static-queries#placeholder-arrays

Specifically, from the documentation, you can use (for example):

$result = $database->query("SELECT * FROM {mytable} WHERE id IN (:ids[])", [':ids[]' => [13, 42, 144]]);

OR you can use (for example):

$result = $database->query("SELECT * FROM {mytable} WHERE id IN (:ids_1, :ids_2, :ids_3)", [
  ':ids_1' => 13, 
  ':ids_2' => 42, 
  ':ids_3' => 144,
]);

But you CANNOT use (for example):

$result = $database->query("SELECT * FROM {mytable} WHERE id IN (:ids)", [':ids' => [13, 42, 144]]);

because in this case you're passing in an array ( [13, 42, 144] ) but the placeholder ( :ids ) does not use the proper syntax for array placeholders.

Answer 2

That issue got resolved. It was regarding passing array in to the db. Added serialise function resloved the issue.

$query->values([
$ob->machine_name,
$ob->name,
serialize($ob->settings),
]);
$query->execute();

Answer 3

that also happen to me when adding a string to an expected int so casting was necessary

(int)$ob->number_as_string