Level 6
HI , This may help you! https://laravel.com/docs/9.x/eloquent#local-scopes
Dec 15, 2022
4
Level 1
get latest 3 record from pivot table in eloquent way
I've an items table and a suppliers table with many to many relationship. the supplier will quote the price differently at different date. How do I get the latest 3 pricing (regardless of supplier) for each item using eloquent?
items table
| id | item_code| item_name|
| -------- | -------- | -------- |
| 1 | Code 1 | Item 1 |
| 2 | Code 2 | Item 2 |
suppliers table
| id | supplier_code| supplier_name|
| -------- | ------------ | ------------ |
| 1 | Supplier 1 | Name 1 |
| 2 | Supplier 2 | Name 2 |
item_supplier
| id | item_id | supplier_id| price | price_at |
| -------- | -------- | ---------- | ----- | ------------------- |
| 1 | 1 | 1 | 100 | 2022-12-01 00:11:22 |
| 2 | 1 | 1 | 101 | 2022-12-02 00:11:22 |
| 3 | 1 | 2 | 98 | 2022-12-03 00:11:22 |
| 4 | 1 | 1 | 95 | 2022-12-04 00:11:22 |
| 5 | 1 | 2 | 120 | 2022-12-05 00:11:22 |
| 6 | 2 | 1 | 105 | 2022-12-06 00:11:22 |
| 7 | 2 | 1 | 100 | 2022-12-07 00:11:22 |
| 8 | 2 | 1 | 101 | 2022-12-08 00:11:22 |
| 9 | 2 | 2 | 98 | 2022-12-09 00:11:22 |
| 10 | 2 | 2 | 95 | 2022-12-10 00:11:22 |
| 11 | 1 | 1 | 120 | 2022-12-11 00:11:22 |
| 12 | 1 | 2 | 105 | 2022-12-12 00:11:22 |
what I would like to get is like below
| item_name | supplier_name| price | price_at |
| ----------- | ------------ | ----- | ------------------- |
| Item 1 | Name 2 | 120 | 2022-12-05 00:11:22 |
| Item 1 | Name 1 | 120 | 2022-12-11 00:11:22 |
| Item 1 | Name 2 | 105 | 2022-12-12 00:11:22 |
| Item 2 | Name 1 | 101 | 2022-12-08 00:11:22 |
| Item 2 | Name 2 | 98 | 2022-12-09 00:11:22 |
| Item 2 | Name 2 | 95 | 2022-12-10 00:11:22 |
class Item extends Model
{
public function supplier() {
return $this->belongsToMany(Supplier::class)
->withPivot('price', 'price_at')
->withTimestamps();
}
}
class Supplier extends Model
{
public function item() {
return $this->belongsToMany(Supplier::class)
->withPivot('price', 'price_at')
->withTimestamps();
}
}
Tried below but I dont know how to limit it to 3 latest pricing for each item
Item::whereKey($this->selectedItems)
->with(['supplier' => function ($q) {
$q->orderBy('price_at','DESC');
}])
->get();
Level 1
@Elliot_putt Thanks for your reply. Can you give an example of how to implement it. I try to understand it but getting no idea how to start using the scope to limit the latest 3 pricing. Sorry I'm totally new to php and laravel stuff. I use below but it limit all the pricing to get a total of 3 pricing only. so some supplier do not display any pricing at all.
public function scopeLatestPricing($query)
{
$query->with(['supplier' => function ($q) {
$q->orderBy('price_at','DESC')
->take(3);
}]);
}
Level 6
@leoiweb you can use $query->latest(“price_at”)->take(3);
Level 70
@leoiweb What if you try this?
Item::whereKey($this->selectedItems)
->with(['supplier' => function ($q) {
$q->orderBy('price_at', 'DESC')->limit(3);
}])
->get();
This will retrieve all items with their suppliers, and for each item, it will get the latest 3 pricing ordered by price_at in descending order.
Please or to participate in this conversation.