🤹
TALL Stack Tips
  • What is TALL Stack
  • Tailwind
    • Tailwind Resources
    • Swinging Bell Notification Icon
    • Styled Unordered Lists
  • Alpine
  • Alpine Resources
  • Tabbed Content Using Alpine JS
  • Checkbox component with SVG tick
  • Dropdown animation
  • Create a Sliding Puzzle Captcha
  • Laravel
    • Tabler Icons Component
    • Password-less Login with Laravel 8+
    • Password-less Login with Magic Link in Laravel 8
    • Laravel Resources
    • Laravel Breeze Login Conditional Redirect
    • Jetstream Login Conditional Redirect
    • Simplify Laravel CRUD Controllers
    • CSRF and expired login forms
    • CSRF and expired logout forms
    • Add your own logo to Laravel Mail
    • Specify a different mail theme for Notifications
    • Show custom page when email verification link expired
    • Using a mutator to save currency
    • Using Spatie Valuestore to hold frequently accessed settings
    • Using the old() helper
    • Alternatives to using Eloquent Accessor
    • UpdateOrCreate may not update timestamp
    • Use of lockForUpdate()
    • Using S3
    • Super Simple User Based Feature Flags
    • Installing a Specific Version of Laravel
    • Versioning your Laravel Project
    • CSS Cache Busting with your Git Commit SHA
    • Adding column to Database Notifications table
    • Find nearby locations using the Haversine formula in Eloquent query
    • Using Queues on Shared Hosting with Laravel
    • Create Guaranteed Unique Invoice Number in Laravel
    • Send Notification to all team members
    • Protect Staging site with Basic Auth
    • Working with Enums
    • PHP DateTime formatting cribsheet
  • Livewire
    • Livewire Resources
    • Naming Livewire Components
    • Dynamic Cascading Dropdown with Livewire
    • Hiding a button after click
    • Working with Javascript Components
    • SweetAlert2 with Livewire
    • Select Multiple or Checkboxes
    • Clearing checkboxes in Livewire
    • Livewire File Uploads Using S3
    • Simple Log File Viewer
  • Related Resources
    • Testing resources
    • When Composer runs out of memory
    • Deployment
    • Security
    • Scheduler & Cron tips
    • LastPass tips
    • Using Git
    • VSCode Tips
    • Markdown
    • Cpanel resources
Powered by GitBook
On this page
  • Problems Considered
  • Determine the current financial year
  • Reset the count in each Financial Year
  • Model and Migration
  • Eloquent FirstOrCreate
  • Safely Increment the invoice number
  • Putting it all together

Was this helpful?

  1. Laravel

Create Guaranteed Unique Invoice Number in Laravel

Not as simple as it may sound. How to avoid problems from race conditions.

An interesting question was posed on Laracasts Discussions which seems straightforward, but comes with a number of hidden issues;

I need help in generating invoice number

I need this format. For example in the financial year 01-04-2022 to 31-03-2023 I have generate number like this R2022/2023-0001 and so on.

In the next year 01-04-2023 to 31-03-2024 it should be like this R2023/2024-0001

Problems Considered

  • How do we determine the current financial year?

  • How do we reset the count in each financial year?

  • How do we increment the invoice number and make it atomic so that another user cannot get the same number

Determine the current financial year

This is relatively straightforward. Get today, then change to 1st April in the current calendar year. If the date moved forward in time then today must be between 1st January and 31st March. If so revert to the previous year.

$dt = today()->setMonth(Carbon::APRIL)->setDay(1);

if($dt > today()) {
 $dt->subYear(1);
}

Reset the count in each Financial Year

This is a little tricky as the invoice number must restart at 0001 in each year.

We don't want to have something that needs to run on a specific date when we can let our database do it for us. We are going to create a database table to keep track of the current invoice number in each financial year;

Model and Migration

php artisan make:model InvoiceSequence -m

In our invoice_sequences migration

        Schema::create('invoice_sequences', function (Blueprint $table) {
            $table->id();
            $table->integer('fy')->unique();
            $table->integer('current')->default(0);
        });
    

the fy column allows us to create a new record for each financial year. The current column stores the next invoice number to be issued. Unique on the fy column ensures that there can only be one entry per year.

Eloquent FirstOrCreate

Initially, I was using the FirstOrCreate method to create a new record each time the financial year changes;

$is = InvoiceSequence::firstOrCreate(['fy' => $dt->format('Y')]);

The fy column is set to the current financial year eg 2022. When the year changes to 2023, there will not be a row with that year so a new row is added, and thanks to the default value, the current value is automatically set to 0. However, it has been pointed out to me by @swaz at Laracasts that the firstOrCreate is not atomic and if two transactions occur at the same time then two entries might be created. For this reason, `unique()` is added to the table.

The preferred approach is instead as below;

$fy = $dt->format('Y');

DB::statement("INSERT INTO invoice_sequences (fy) VALUES ({$fy}) ON DUPLICATE KEY UPDATE fy = fy, id=LAST_INSERT_ID(id)");
    
$lastInsertId = DB::getPDO()->lastInsertId();

This attempts to insert a new row into the database, and if this fails, returns the record ID for the existing row. The point is that this single SQL statement is atomic. Two threads cannot interfere with each other's operation.

Safely Increment the invoice number

If we don't take care at this stage, two requests occurring at the same time could be given the same invoice number.

At first glance, it would be easy to write a solution that;

  • gets the invoice_sequences record for the current year

  • adds one the current value

  • saves the record

The problem with this approach is that two users might both execute the read part and get the same number, both then increment and both save. The counter has only been incremented once and both requests get the same number.

This is not a problem in testing, or when your application is small, but as it increases in activity the chances of this happening start to increase, are unpredictable and extremely hard to track down.

The example here is for invoice numbers but it could apply to any number that needs to be guaranteed to issue once and once only.

DB::statement("UPDATE invoice_sequences SET current = LAST_INSERT_ID(current) + 1 WHERE id = {$lastInsertId}");

$current = DB::getPDO()->lastInsertId();

Read the linked article to understand why this works.

This solution is only applicable to MYSQL

Putting it all together

Along with formatting it in the format requested by the question poster

$dt = today()->setMonth(Carbon::APRIL)->setDay(1);

if($dt > today()) {
 $dt->subYear(1);
}

$fy = $dt->format('Y');

// ensure there is a record for the current financial year
DB::statement("INSERT INTO invoice_sequences (fy) VALUES ({$fy}) ON DUPLICATE KEY UPDATE fy = fy, id=LAST_INSERT_ID(id)");
$lastInsertId = DB::getPDO()->lastInsertId();

// automatically increment the count AND get the value
DB::statement("UPDATE invoice_sequences SET current = LAST_INSERT_ID(current) + 1 WHERE id = {$lastInsertId}");
$current = DB::getPDO()->lastInsertId();

$invoiceNumber = sprintf('R%s/%s-%04u', $year, $year+1, $current);

Grateful to @swaz at Laracasts for helping with this. You can read the full thread and how we arrived at the solution here;

PreviousUsing Queues on Shared Hosting with LaravelNextSend Notification to all team members

Last updated 2 years ago

Was this helpful?

see:

The solution, inspired by this safely increments the current number and also returns its value.

You can discuss pages on this site at

https://freek.dev/1087-breaking-laravels-firstorcreate-using-race-conditions
https://www.sqlines.com/mysql/how-to/select-update-single-statement-race-condition
https://github.com/snapey/talltips/discussions
Logohttps://laracasts.com/discuss/channels/laravel/generating-unique-invoice-number-based-on-financial-yesrLaracasts