Json format using firstOrCreate will cause SQLSTATE[HY093]: Invalid parameter number

after some test I just notice that

if you have a Json or Array format column in database, using firstOrCreate will cause SQLSTATE[HY093]: Invalid parameter number error.

Even you use json casts in model will not solve this.

The only way is json_encode before send to database.

My guess is, firstOrCreate is running before json cast.

Is that an issue??

LaryAI

2 years ago

Level 58

It seems like you are encountering an issue with Laravel's firstOrCreate method when dealing with JSON or array formatted columns in your database. The error SQLSTATE[HY093]: Invalid parameter number typically indicates a mismatch in the number of placeholders and the number of supplied parameters in a prepared statement.

In Laravel, when using firstOrCreate, it indeed might not automatically handle JSON serialization based on the casts defined in your model. This is because firstOrCreate attempts to find an existing record using the provided attributes and if it doesn't find one, it creates a new record with those attributes. The attributes are used directly in the query builder which might not respect the custom casting defined in the model.

To work around this issue, you should manually encode your JSON or array data before passing it to firstOrCreate. Here's how you can do it:

use App\Models\YourModel;

$data = [
    'json_column' => ['key' => 'value'] // Your actual array or JSON data
];

// Manually encode the JSON data
$data['json_column'] = json_encode($data['json_column']);

// Use firstOrCreate with the manually encoded data
$record = YourModel::firstOrCreate($data);

Make sure that your model has the correct casts defined for the JSON columns:

protected $casts = [
    'json_column' => 'array'
];

This approach ensures that the JSON data is correctly formatted as a JSON string when it is passed to the database, which should prevent the SQL error you're seeing. Remember to replace 'json_column' and YourModel with the actual column name and model name you are working with.