0

I am attempting to override the default database connection used by a model to another one which I have defined in config/database.php.

However when bulk inserting data, the two inserts listed below work differently:

$model= new SomeModel();
$model->setConnection('another_connection');

// Throws exception due to invalid data which needs casted, 
// done via setSomeFieldAttribute() methods defined in SomeModel()
$model->insert($data);

// Works, performs setSomeFieldAttribute() methods 
// but inserts data on default connection
$model::insert($data);

How can I perform bulk insertion, with setAttribute() methods on a set connection?

herrbischoff
  • 3,294
  • 1
  • 29
  • 50
Qerim
  • 1
  • 1
  • 2
  • Can you include your `.env` and `config/database.php` codes? – rkg Oct 02 '19 at 11:35
  • @rkt the connection is fine because when I attempt to insert a single entry using `$model->setConnection('another_connection')->create($data)` it works. It just seems to have different behaviour with the `->insert($data)` method. – Qerim Oct 02 '19 at 12:45

3 Answers3

0

The issue with what you're doing is, whenever you run a method on an Eloquent Model, it runs the query on the Illuminate\Query\Builder object after going through the Illuminate\Query\Builder wrapper function. The Query Builder object stores a cache/reference of the connection object used for the query. Since your Model is created first before the connection is changed, the Query Builder object will use the old connection.

There are multiple ways to solve the issue you're facing:

1.You can use the static function on:

Model::on('new_connection')->insert($data);

2.Use the fresh method in your case:

$model = SomeModel();

$model->setConnection('new_connection')->fresh()->insert($data);

Just note that fresh method fires a new query vs the other option that sets the connection without firing a new query.

rkg
  • 805
  • 5
  • 14
  • I attempted both ways, no luck. The 1st way works but does not go through the set...Attribute() methods in the model. The 2nd method throws me this error: `Call to a member function insert() on null in...` There is data, so not sure where the `null` is coming from. – Qerim Oct 02 '19 at 12:41
0

OK I found the issue.

When setting up the new database connection in config/database.php, I had set strict to true. All my connections have this set to false:

'another_connection' => [
    'strict' => true,
    'driver' => 'mysql',
    '...'=> ...
]

Found the answer from here: https://stackoverflow.com/a/52685074/3554278

Strict mode was introduced in MySQL 5.7 if anyone wants to do more reading: https://mattstauffer.com/blog/strict-mode-and-other-mysql-customizations-in-laravel-5-2/

Qerim
  • 1
  • 1
  • 2
-1

When you do $model->setConnection(...) you are setting it only for this object. Therefore, when you call it statically it will not consider the information

If you want to change it in the model level, add the property protected $connection = 'another_connection'; in your model.

Mauro Baptista
  • 195
  • 4
  • I have done that, which works, however it does not execute the set..Attribute() methods in the model to clean up the data tat is required. So I then get errors saying data is not formatted correctly. – Qerim Oct 02 '19 at 12:43
  • Do you mind add your model code? I believe it can be something related with the fillables or with how the set..Attribute was created. – Mauro Baptista Oct 02 '19 at 16:46