autoincrement column when another column has same value in laravel

Question

I have a table with 3 fields ID, type, value. I want to value in Value column auto increment when value in Type column is same like table below

| ID | Type | Value |
| -- | ---- | ----- |
| 1  | 1    | 1     |
| 2  | 1    | 2     |
| 3  | 2    | 1     |
| 4  | 2    | 2     |
| 5  | 2    | 3     |

For now, I've already using this way:

• Set default value for Value column is 0
• Then use raw sql to update value in the column

DB::statement("UPDATE my_table SET number = (SELECT MAX(value) FROM (SELECT * FROM my_table) AS temp WHERE temp.type = '$type') + 1 WHERE id = '$table->id';");

But I want this run automatically whenever new row created. I wonder that there is another way to do this by migration, observer,... or something?

I'm using Laravel 8.0. Thanks

Answer 1

等待大神答复