Optimize createMany

I wish to improve my performance without removing the readability with Eloquent.

Currently, I am having this

$user->notifications()->createMany($notifications);

Which produces one SQL per notification. I would expect this to run just one SQL query to insert them all.

I have experience with the alternative Notification::insert($notifications), but this does not set the created_at and updated timestamps.

I could easily add these timestamps myself to each notification in the array, but there must be a way to do this and still keep it more readable. I know Eloquent by default returns the objects along with their ID from the database, which could be why it's needed to run it in separate queries, but for my case, I don't care about the objects, I just need these created in the database.

I am also open to solutions that could extend my base model in order to add support for this in a readable way.

Nakov

4 years ago

Level 73

Yeah, Eloquent does adds the timestamps but the QueryBuilder does not. Simply because Eloquent allows you to prevent updates on those columns in case you want to insert old data, or want to make an update without touching the timestamps.

And yes, createMany does per notification insert as you said.

There are couple of ways to consider.

Update your database timestamp columns to this maybe:

$table->timestamp('created_at')->default(\DB::raw('CURRENT_TIMESTAMP'));
..

now using the insert will set the timestamps.

Check this article: https://medium.com/fattihkoca/laravel-auto-save-timestamps-with-query-builder-without-using-eloquent-123f7ebfeb92

2 likes

tykus

4 years ago

Level 104

Or, just add the current datetime to each of the $notifications:

Notification::insert(
    array_map(fn ($n) => array_merge($n, ['created_at' => now(), 'updated_at' => now()]))
);

1 like

Please or to participate in this conversation.