CODEWITHSUNDEEP
phpmysqllaravellaravel-5eloquent
Buno
Buno

Reputation: 597

How do you select distinct() models based on a specific column with Laravel?

I have a database in which I store documents - some are independant from each other, but some are just a "next version" of a previous document. In that case, I store the id of the latest version in a column.

An example dataset would be like this:

id | name       | version | validated | latest_version_id
 1 | 'Doc 1'    |       1 |      true | null
 2 | 'Doc 2'    |       1 |      true | null
 3 | 'Doc 3 v1' |       1 |      true |    5
 4 | 'Doc 3 v2' |       2 |      true |    5
 5 | 'Doc 3 v3' |       3 |     false |    5

I would like to be able to select all validated documents in their latest version. Typically a request that would return lines 1, 2 and 4 in the dataset.

I was hoping to be able something like Docs::where('validated', true)->distinct('latest_version_id')->get() but it's obviously not as simple as that..

Any query guru to point me in the right direction? A solution would be to get all results and then filter out some models, but that doesn't sound very optimized.

Upvotes: 0

Views: 70

Answers (2)

Simon R
Simon R

Reputation: 3772

I agree with the Watercayman, you should think about refactoring. In the short term, you can use the following, but it's not 100% reliable but it will achieve what you want with your current dataset

  $unversioned = Doc::query()->whereNull('latest_version_id')->where('validated', true);
  $versioned = DB::table(DB::raw("(SELECT * 
                                   FROM docs WHERE latest_version_id IS NOT NULL AND validated = true 
                                   ORDER BY version DESC) as t"))
                            ->groupBy('latest_version_id');

  $unversioned->union($versioned)->get();

Please note models should be singular - which is why I've called Doc:: rather than Docs::

Upvotes: 1

Watercayman
Watercayman

Reputation: 8178

I think you are going to have trouble because there is no unique way (that I can see) to identify that a doc is related to another doc. I.E. Doc 3 v1 is the same document as Doc 3 v2 to a human... but how does the app know this? How does it search to know which are common and thus to check for the latest version of this particular doc?

I think your best solution may be if you can slightly re-architect the way you have stored data, to provide possibly an FK to a set of docs, you can solve this. This way, a 'documents table' would allow for a query to know specifically which docs are the same. Then, your existing table above would have an FK doc_id in addition to the existing name column. Thus, you eliminate the problem of various names (they would all be attached to the same master document by FK). This will then allow you to show what version corresponds to that particular master document and which set that versions belongs to, and then you can distinctly manage the query much easier based on these parameters.

Upvotes: 1

Related Questions