Laravel Model multiple versions-CodePudding

I am looking for a way how to return only the newest version of model for table and all other versions of model consisting of multiple unique columns.

$table->string('document_number');
$table->string('version');
$table->unique(['document_number','version']);

Example of showing only newest version:

    ABCDoc1 Version 1
    ABCDoc1 Version 2
    ABCDoc2 Version 1
    ABCDoc2 Version 2
    ABCDoc2 Version 3
    
    Desired Result:
    ABCDoc1 Version 2
    ABCDoc2 Version 3

Example of showing older versions

ABCDoc1 Version 1
ABCDoc1 Version 2
ABCDoc2 Version 1
ABCDoc2 Version 2
ABCDoc2 Version 3

Desired Result:
ABCDoc1 Version 1
ABCDoc2 Version 1
ABCDoc2 Version 2

Any help would be appreciated :)

CodePudding user response：

You could use max of your modified string of version in your query and don't forget to add groupBy() for grouping the result by document_number, for example :

YourEloquent::select(DB::raw("
    document_number,
    max( SUBSTR(version, -1, LOCATE(' ', version)) ) as max_version"))
    ->groupBy('document_number')->get();

You will get the result something like this https://paiza.io/projects/-yZtaUKekoabUDfzLihQgw?language=mysql