CODEWITHSUNDEEP
phpsqllaraveleloquent
Daniel Groppo
Daniel Groppo

Reputation: 59

Select last row of group on Laravel Eloquent

I have three tables:

stores
|- id
|- name

products
|- id
|- name

prices
|- id
|- value
|- product_id
|- store_id
|- created_at

As you can see, we have the same product in several stores, each store with your own price (a new record is created when the price is updated to maintain the history).

My questions are:
A) I want to display a table of the current prices in all stores of a certain product;
B) I want to display a list of all products and your cheapest price with the name of the store with this price.

How is it possible on Eloquent of Laravel 5.6?

Upvotes: 1

Views: 450

Answers (3)

Jonas Staudenmeir
Jonas Staudenmeir

Reputation: 25906

Use this relationship:

class Product extends Model
{
    public function stores()
    {
        return $this->belongsToMany(Store::class, 'prices')->withPivot('value');
    }
}

A)

foreach($product->stores as $store) {
    $price = $store->pivot->value;
}

B)

$products = Product::with('stores')->get();
foreach($products as $product) {
    $cheapestStore = $product->stores->sortBy('pivot.value')->first();
    $price = $cheapestStore->pivot->value;        
}

Upvotes: 1

Kamlesh Solanki
Kamlesh Solanki

Reputation: 646

Using Eloquent getting the relational data is an easy way.

1) Price(Belongs to Product and Store)

2) Product (has many prices)

3) Store (has many prices)

1) Price.php

<?php

namespace App\Models;
use Eloquent;

class Price extends Eloquent{

    protected $table = 'prices';

    public function store()
    {
        return $this->belongsTo('App\Models\Store');
    }

    public function product()
    {
        return $this->belongsTo('App\Models\Product');
    }

}

2) Product.php

<?php

namespace App\Models;

use Eloquent;

class Product extends Eloquent
{
    protected $table = "products";

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

}

3) Store.php

<?php

namespace App\Models;

use Eloquent;

class Store extends Eloquent
{
    protected $table = "stores";

    public function prices()
    {
        return $this->hasMany('App\Models\Store');
    }

}

After model declaration, you need to get records like this

$price = \App\Models\Price::with(['store','product'])->groupby(['store.name','product.name'])->get();
$price->store->name;

I hope you have to help this one.

Upvotes: 1

Pankaj Kumar
Pankaj Kumar

Reputation: 570

This will help you to write your query:

  A.   select st.name,pd.name, pr.value from prices pr
       left join products pd on pd.id = pr.product_id
       left join stores st on st.id = pr.store_id

  B.   select st.name,pd.name, min(pr.value) as price from prices pr
       left join products pd on pd.id = pr.product_id
       left join stores st on st.id = pr.store_id group by st.name,pd.name

Upvotes: 1

Related Questions