Laravel how to define relationship based on two models or columns?

user_13520667 0
Viewed 22

I have the following:

//users table
id
name
email
remeber_token
role_id

//roles table
id
name

//products table
id
name

//product_prices 
role_id
product_id
price

The price of the product will vary depending on the user role, how to define the correct relationship so in blade I can do something like:

$product->price

and that will return the correct price depending on the user and the product?

laravel-5.8 laravel-5 laravel
1 Answers

I believe that when you say the user role, you refer to the role of the authenticated user.

The easiest approach is to define a HasOne relationship on the product model:

class Product
{
    public function price()
    {
        return $this->hasOne(Price::class)->where('role_id', auth()->user()->role_id)
    }
}

So from the view, you can simply say:

// Lets use optional() because there may not be any price for that product and user.
optional($product->price)->price

If there are many products involved, then using sub queries would make more sense.

class ProductsController extends Controller
{
    public function index()
    {
        $products = Product::addSelect(['right_price' => Price::select('price')
            ->whereColumn('product_id', 'products.id')
            ->where('role_id', auth()->user()->role_id)
            ->limit(1)
        ])->get()

        return view('products.index', compact('products'));
    }
}

And then in the view:

@foreach($products as $product)
    <p>{{$product->right_price}}</p>
@endforeach

For more info, pls check out this article.

user_29949200
Related