How to generate a custom primary key in MySQL using Laravel Eloquent? [closed]

Instead of saving your PK as SEQ/YYYY (001/2025, 123/2025, etc), just save it as YYYYSEQ instead (2025001, 2025123, etc). Then use mutators/accessors to get the formatted id

<?php namespace App\Models; use Illuminate\Database\Eloquent\Model; use Illuminate\Support\Facades\DB; class YourModel extends Model { protected static function booted() { static::creating(function ($model) { $year = date('Y'); // Get the last ID $lastId = DB::table($model->getTable())->max('id'); // Check if last ID starts with current year if (!$lastId || substr((string)$lastId, 0, 4) != $year) { // Assign id as YYYY001 $model->id = (int)($year . '001'); } // Mysql will auto increment }); } // Accessor to display as SEQ/YYYY public function getFormattedIdAttribute() { $year = substr((string)$this->id, 0, 4); $seq = substr((string)$this->id, 4); return str_pad($seq, 3, '0', STR_PAD_LEFT) . '/' . $year; } }

Then use as

$model = YourModel::create([ 'name' => 'Test Record', ]); echo $model->id; // 2025001 if first record of 2025 echo $model->formatted_id; // 001/2025

Reason why saving as YYYYSEQ is better

You only need to check the first 4 digits (year) then do your logic to increament, instead of looking at the last 4 digits then finding the biggest number

You need to know the projected number of records per year. If the total records exceed 999 within the same year, then the 1000th record will be record next year + 001

ex: 2025999 -> 2026001

If so, then adjust the format as needed

Beware that this code is sensitive to race condition. As per @Olivier's comment