CODEWITHSUNDEEP
mysqllaraveleloquentlaravel-5.4
MikeK
MikeK

Reputation: 383

In a Laravel Model's relationship how do I return a relationship using GROUP BY and MAX?

I have a project, the project can have attached documents. Both projects and documents are models/tables.

Simplified example documents table:

-----------------------------------
| ID  | filename        | version |
-----------------------------------
| 1   | list.docx       | 1       |
| 2   | list.docx       | 2       |
| 3   | file.xls        | 1       |
-----------------------------------

I want to have some simple version control so that if a user wants to they can 'replace' the document, with the new table row copying all of the previous entries values except for ID and version.

My issue is that I want to use a relationship to pull out all of the latest documents. I'm currently using this monstrosity in the Project model:

public function latest_documents()
{
    return $this->hasMany(Document::class)
        ->where('version', \DB::raw('(SELECT max(d.version) from documents d where d.filename = documents.filename)'))
}

There must be a better way? I tried just using groupBy() and max() with the relationship but got errors.

EDIT: Attempted 'Laravel method' before the DB::raw solution:

public function documents()
{
    return $this->hasMany(Document::class)->groupBy('filename')->max('version');
}

Error:

Relationship method must return an object of type Illuminate\Database\Eloquent\Relations\Relation

Upvotes: 1

Views: 525

Answers (1)

Amir
Amir

Reputation: 423

My advise is to create a MySQL view based on documents table that always contains only latest document files and simply make a relation to that view. So :

Step-1/ create the view :

create view latest_documents as
  select filename,
         max(version)  as version,
         project_id    as project_id,
         any_value(id) as id
  from documents
  group by project_id, filename;

Step-2/ create the view model :

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class LatestDocument extends Model
{
    //
}

Step-3/ add the relation to the project model :

public function latest_documents()
{
    return $this->hasMany(LatestDocument::class);
}

I tested this and should work.

note: I used any_value() in step one to prevent errors according to only_full_group_by. So if you disabled this mode(I would not recommend) there is no need to use this function around the id.

Upvotes: 1

Related Questions