Laravel getting value from another table using eloquent-CodePudding

I got tables like this:

User table:

 ---- --------- ------------ 
| id |  name   |    level   |
 ---- --------- ------------ 
|  1 |  user 1 |     1      |
|  2 |  user 2 |     2      |
 ---- --------- ------------

Category table:

 ---- --------- ------------ 
| id | user_id |    name    |
 ---- --------- ------------ 
|  1 |       1 | category 1 |
|  2 |       2 | category 2 |
|  3 |       2 | category 3 |
 ---- --------- ------------

Product table:

 ---- ------------- ------------ 
| id | category_id |    name    |
 ---- ------------- ------------ 
|  1 |       1     | product 1  |
|  2 |       2     | product 2  |
|  3 |       3     | product 3  |
|  4 |       3     | product 4  |
 ---- ------------- ------------

I want to get all the product with user_id = 2 through eloquent, and i got it through the code below:

$id = 2;
$data = product::whereHas('category',  function ($q) use ($id) {
    $q->where('user_id', $id);
})->get();

But when i want to print the category name and user name through $data, it doesnt seem to work, my code is like this:

$data->first()->category->name;
$data->first()->user->name;

I can just solve this question with normal query build with JOIN, just join 3 tables together and select the desire columns and it's good to go, but i want to solve it with eloquent, i'm kinda clueless how to make it work.

And i have another question, i got a query builder code like this:

    $id = false;
    if(auth()->user()->level != 1){
        $id = auth()->user()->id;
    }
    $data = DB::table('product')
                ->select('product.*', 'category.name AS category_name', 'users.name AS user_name')
                ->join('category', 'category.id', '=', 'product.category_id')
                ->join('users', 'users.id', '=', 'category.user_id')
                ->when($id, function($query, $id){
                    return $query->where('users.id', $id);
                })
                ->get();

The idea of this code is when user level = 1, i will get all the products, but when user level != 1, i will get all the products with the user id = $id, the question is: how can i convert this to eloquent? I got an answer for myself but i think it's not good enough.

Thanks.

CodePudding user response：

In Product Model write this code

public function category()
{
    return $this->belongTo(Category::class,'category_id');
}

In Category Model

public function user()
{
    return $this->belongTo(User::class,'user_id');
}

Now you can get product with category and user.

$product = Product::with('category.user')->whereRelation('category','user_id',2)->first();
$product->category->name; // get category name
$product->category->user->name; // get user name

CodePudding user response：

you can check this link Load all relation to a single user laravel that help me for that.

I have to give full answer here as Padarom say.

User::where('id', $x)->with(['category.product'])->first();
//or
User::with(['category.product'])->find($x);

Presuming you already have relation in user.php

public function category()
{
    return $this->belongTo(Category::class,'category_id');
}

in category.php

public function user()
{
    return $this->belongTo(User::class,'user_id');
}