How to figure out what number a record is, when in date order

In my Laravel project I have a Policy model that looks like so:

<?php

namespace App;

use AppNotificationsPolicyRequiresApproval;
use IlluminateDatabaseEloquentModel;

class Policy extends Model
{
    const STATUS_PUBLISHED = 'published';
    const STATUS_PENDING_REVIEW = 'pending review';
    const STATUS_SENT_FOR_REVIEW = 'sent for review';
    const STATUSES = [
        self::STATUS_PENDING_REVIEW,
        self::STATUS_PUBLISHED,
    ];

    /**
     * The attributes that are mass assignable.
     */
    protected $fillable = [
        'id', 'user_id', 'name', 'content', 'status',
    ];

    /**
     * The user who authored the policy.
     */
    public function author()
    {
        return $this->belongsTo(User::class, 'user_id', 'id');
    }

    /**
     * Get all of the audits for this policy.
     */
    public function audits()
    {
        return $this->hasMany(PolicyAudit::class, 'policy_id', 'id')->orderBy('created_at', 'desc');
    }

    /**
     * Get all of the versions for this policy.
     */
    public function versions()
    {
        return $this->hasMany(PolicyVersion::class, 'policy_id', 'id');
    }

    /**
     * Get the current version.
     */
    public function currentVersion()
    {
        return $this->versions()->orderBy('created_at', 'DESC')->first();
    }

    /**
     * Get the most recent approved version.
     */
    public function currentApprovedVersion()
    {
        return $this->versions()
            ->whereNotNull('approved_at')
            ->orderBy('created_at', 'DESC')
            ->first();
    }

    /**
     * Get the URL to preview the policy for administrators.
     */
    public function getPreviewUrlAttribute()
    {
        return route('thanos.policies.show', $this);
    }

    /**
     * Send a policy to the policy administrator for approval.
     */
    public function sendForReview(Policy $policy)
    {
        User::role(['admin'])->get()
        ->each(function ($user) use ($policy) {
            $user->notify((new PolicyRequiresApproval($policy))->delay(now()->addSeconds(10)));
        });

        $this->update(['status' => self::STATUS_SENT_FOR_REVIEW]);
    }
}

As you can see there are versions of a policy.

My policy versions table schema looks like so:

<?php

use IlluminateDatabaseMigrationsMigration;
use IlluminateDatabaseSchemaBlueprint;
use IlluminateSupportFacadesSchema;

class CreatePolicyVersionsTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('policy_versions', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->unsignedBigInteger('policy_id');
            $table->unsignedBigInteger('user_id')->nullable();
            $table->longText('values');
            $table->boolean('is_major_change')->default(0);
            $table->dateTime('approved_at')->nullable();
            $table->unsignedBigInteger('approved_by')->nullable();
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('policy_versions');
    }
}

My question is as follows: if I have five versions of a policy in date order and I select a policy version, how could I say “you are on version n of x for this policy”?

Would it be best to add a policy_version_number and just increment it each time a new version is created?

This feels like a dumb question but initially I was just going to use the version id but this would actually be incorrect.

Answer

You can listen to the model’s events and automatically populate the column when the model is created. To do that, implement the boot method in your PolicyVersion model:

public static function boot() {
    parent::boot();

    static::creating(function (PolicyVersion $item) {
        $max = PolicyVersion::where('policy_id', $item->id)->max('policy_version_number');
        $item->policy_version_number = $max + 1;
    });
}

Untested, but should do the trick.