Laravel — Auto Saving Timestamp With Query Builder

Insert query with created_at and created_by data

Hello, I’ll explain you how to add automatic timestamp and footprint (created_by, updated_by, deleted_by) records with Laravel Query Builder.

Although Eloquent provides a lot of convenience, in some cases, we want to use DB class because Eloquent consumes a lot of system.

Now with the new provider we will create, both the created_at, updated_at, deleted_at (soft delete) data will be added automatically. You’ll also be able to automatically add the created_by, updated_by, deleted_by ($withBy ) data.

Note: For using footprint you need to create related (created_by, updated_by, deleted_by) columns in the database tables.


Usage

1. Inserts

insertTs method inserting records into database with created_at data:

DB::table('users')->insertTs([
'email' => 'john@example.com'
]);

If you want to save the created_by data when inserting, add true to the 2nd argument of the insertTs method:

DB::table('users')->insertTs([
'email' => 'john@example.com'
], true);

Auto-Incrementing IDs
If the table has an auto-incrementing id, use the insertGetIdTs method to insert a record and then retrieve the ID:

$id = DB::table('users')->insertGetIdTs([
'email' => 'john@example.com'
]);

2. Updates

updateTs method updating records into database with updated_at data:

DB::table('users')
->where('id', 1)
->updateTs(['email' => 'john@example.com']);

If you want to save the updated_by data when updating, add true to the 2nd argument of the updateTs method:

DB::table('users')
->where('id', 1)
->updateTs(['email' => 'john@example.com'], true);

3. Deletes

deleteTs method deleting records into database with deleted_at data (Soft delete). If you do not want to do soft delete, use the delete method.

DB::table('users')
->where('id', 1)
->deleteTs();

If you want to save the deleted_by data when deleting, add true argument of the deleteTs method:

DB::table('users')
->where('id', 1)
->deleteTs(true);

Let’s Code This Provider

So I’m going to tell you how to do it gradually. To do this, we will use Laravel’s magic macro property.

1. Create A Provider

Create a provider named QueryBuilderMacroProvider:

php artisan make:provider QueryBuilderMacroProvider

2. Add Builder Macro In “QueryBuilderMacroProvider”

Let’s write the following code in the app/Providers/QueryBuilderMacroProvider.php file:

<?php

namespace
App\Providers;

use Illuminate\Support\ServiceProvider;
use Illuminate\Database\Query\Builder;

class QueryBuilderMacroProvider extends ServiceProvider
{
protected static $methods = ["insertTs", "insertGetIdTs", "updateTs", "deleteTs"];

protected static function timestampValues($funcName, array $colNames)
{
Builder::macro($funcName, function (array $values, $withBy = false) use ($colNames) {
            // created_by or updated_by
$user_id = $withBy ? auth()->user()->id : null;
            // created_at or updated_at
$now = \Carbon\Carbon::now();

if (array_key_exists(0, $values) && is_array($values[0])) {
foreach ($values as &$value) {
$value[$colNames[1]] = $now;

if ($withBy) {
$value[$colNames[2]] = $user_id;
}
}
} else {
$values[$colNames[1]] = $now;

if ($withBy) {
$values[$colNames[2]] = $user_id;
}
}

return Builder::{$colNames[0]}($values);
});
}

/**
* Insert with timestamp
*/
protected static function insertTs()
{
return self::timestampValues(__FUNCTION__, ["insert", "created_at", "created_by"]);
}

/**
* Insert with timestamp and return id
*/
protected static function insertGetIdTs()
{
return self::timestampValues(__FUNCTION__, ["insertGetId", "created_at", "created_by"]);
}

/**
* Update with timestamp
*/
protected static function updateTs()
{
return self::timestampValues(__FUNCTION__, ["update", "updated_at", "updated_by"]);
}

/**
* Soft delete (with timestamp)
*/
protected static function deleteTs()
{
Builder::macro(__FUNCTION__, function ($withBy = false) {
$values = [
"deleted_at" => \Carbon\Carbon::now()
];
            // deleted_by
if ($withBy) {
$values["deleted_by"] = auth()->user()->id;
}

return Builder::update($values);
});
}

/**
* Register services.
*
*
@return void
*/
public function register()
{
foreach (self::$methods as $method) {
self::{$method}();
}
}
}

3. Add this provider to Autoloader Service Providers

In theconfig/app.php we add the following in the ‘providers’ key:

'providers' => [
   /*
* Laravel Framework Service Providers...
*/

.
.
.
.
.
.
.
// Query Builder Macro
App\Providers\QueryBuilderMacroProvider::class,
],

Now we have automatically loaded a macro that is automatically installed for Illuminate\Database\Query\Builder class.

4. Result

Now you can use it in the following way. After than check it on database.

DB::table('table_name')->insertTs($array_values, true);
DB::table('table_name')->updateTs($array_values, true);
DB::table('table_name')->deleteTs(true);
$id = DB::table('table_name')->insertGetIdTs($array_values, true);

See you later in another article. 😉👍