CODEWITHSUNDEEP
phplaravel
danial dezfooli
danial dezfooli

Reputation: 818

Laravel - use distinct on elequent

I want to get last three prices (6,7,8). I don't need Limit.

id: 8 is an update from id: 5,

id: 8 should show instead of id: 5

[
    {
        id: 5,
        product_id: 1,
        seller_id: 26,
        color_id: 18,
        warranty_id: 29,
        price: 1000000,
        created_at: "2017-05-27 09:33:44",
    },
    {
        id: 6,
        product_id: 1,
        seller_id: 26,
        color_id: 19,
        warranty_id: 30,
        price: 1200000,
        created_at: "2017-05-27 09:33:56",
    },
    {
        id: 7,
        product_id: 1,
        seller_id: 26,
        color_id: 21,
        warranty_id: 33,
        price: 900000,
        created_at: "2017-05-27 09:34:06",
    },
    {
        id: 8,
        product_id: 1,
        seller_id: 26,
        color_id: 18,
        warranty_id: 29,
        price: 600000,
        created_at: "2017-05-27 10:21:23",
    }
]

My try:

$product->get_prices()
->groupby(['product_id','color_id','warranty_id','seller_id'])
->latest()
->get();
 // 5,6,7,8

Product.php 

function get_prices(){
    return $this->hasMany('\App\Price');
}

Upvotes: 2

Views: 204

Answers (1)

TheAlexLichter
TheAlexLichter

Reputation: 7289

Collection version

You can use the unique() collection method here. As unique value I used a combination of the given ids (seller, product, warranty and color id) with a delimiter (which you can change if you want.

I used the reverse() function before, so the unique function will return the last duplicates instead of the first ones.

Then go for take() as usual :)

$product->get_prices()->get()->reverse()->unique(function ($item) {
    return $item['product_id'] . "|" . $item['seller_id'] . "|" .          
              $item['color_id'] . "|" . $item['warranty_id'];
})->take(3);

You can issue Eloquent Queries before using the given solution too:

$product->get_prices()->with("...")->where("....")
        ->get()->reverse()->unique(function ($item) {
            return $item['product_id'] . "|" . $item['seller_id'] . "|" .          
                   $item['color_id'] . "|" . $item['warranty_id'];
        })->take(3);

ORM Version ?

I have tried a lot to get a working ORM version up, but unfortunately I couldn't manage to create one. If you really want to do it on the DB layer, better use a raw query I guess.

Related: Distinct most recent data from database

Upvotes: 2

Related Questions