Laravel getting value from another table using eloquent

Question

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.

Chando · Accepted Answer · 2022-01-11 09:59:53Z

3

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

answered Jan 11, 2022 at 9:59

Chando

4444 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

FalAn Over a year ago

thanks, i have already use relationship in the model, but i dont use class, instead i use this in product model: "return $this->belongsTo('App\Models\category');" Am i writing this wrong?

FalAn Over a year ago

and how about "hasMany"? like category model, since the foreign key "category_id" belong to the product table, how do i write it?

FalAn Over a year ago

and thanks for the whereRelation() clause, it answer my 2nd question too :D

Chando Over a year ago

Answer 1 yes. For whereRelation() is use for one column on relationsip. If you need query more than one, you can use whereHas() clause. Example:

Product::whereHas('category',function ($query){ $query->where('user_id',2)->where('name','like','%category 1%')->whereRelation('user','name,'like,'user 1') });

Chando Over a year ago

You can use Product::with['user'] by use hasOneThrough() clause. laravel.com/docs/8.x/eloquent-relationships#has-one-through

Tomerikoo · Accepted Answer · 2022-01-11 21:08:08Z

0

You can check this link that help me for that:

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');
}

edited Jan 11, 2022 at 21:08

Tomerikoo

19.6k16 gold badges57 silver badges68 bronze badges

answered Jan 11, 2022 at 10:30

Puneet Sharma

3373 silver badges10 bronze badges

7 Comments

padarom Over a year ago

This does not provide an answer to the question. Once you have sufficient reputation you will be able to comment on any post; instead, provide answers that don't require clarification from the asker. - From Review

Puneet Sharma Over a year ago

i don't want to take credit but point to solution but ok i copy the answer from there.

FalAn Over a year ago

may i ask you something? what is the different between "with(['category.product'])" and "with(['category', 'product'])"?? i only know it is nested relationships and multiple relationships, but that's it, can you please explain to me what is the different?

Puneet Sharma Over a year ago

user has relation to category and category has relationship to product on with(['category.product']) but on with(['category', 'product']) mean it load user relationship to product and category but that not applicable in your case not applicable on nested relationship

FalAn Over a year ago

so, i can only use "A::with(['B', 'C'])" in which case? like there is no relation ship between them all? or only one of them is enough?(A relate to B, but A and B is not relate to C)

|

Collectives™ on Stack Overflow

Laravel getting value from another table using eloquent

2 Answers 2

5 Comments

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related