How to filter using eloquent using distinct?

Question

Would like for some help. Currently I have a table with multiple records.

Columns have is : id, identifier, price

I would like to write a query which I'm able to get the unique identifier with the highest price only.

I would like the collection to be

[
    [
        'id' => 5,
        'identifier' => 1001
        'price' => 50
    ],
    [
        'id' => 7
        'identifier' => 1002,
        'price' => 35
    ]
]

the first array id is 5 because from the identifier 1001 the highest price is 50 and the id is 5 and second array id is 7 because identifier 1002 highest price is 35

Answer 1

Use this to get all distinct identifiers with max price (edit table):

$products = DB::table('tbl_products')->groupBy('identifier')->get(['identifier', DB::raw('MAX(price) as price')]);

Answer 2

$maxPrices = Model::select(DB::raw('max(price)'))
             ->groupBy('identifier')->get();

query result: [50, 35]

Model::select()->whereIn('price',$maxPrices)->orderBy('price', 'desc')->get()

query result

[
    [
        'id' => 5,
        'identifier' => 1001
        'price' => 50
    ],
    [
        'id' => 7
        'identifier' => 1002,
        'price' => 35
    ]
]

Answer 3

Hi guys found a better solution. Read on DB view table. This function will create a virtual table with the value wanted and in laravel you can create a model to reference that virtual table and do eloquent query on it.