CODEWITHSUNDEEP
phpmysqldatabaselaravellaravel-5.6
Rana Raheel Tariq
Rana Raheel Tariq

Reputation: 101

Fetch data from two Table with where condition in Laravel 5.6

I want to fetch data from two table profile and User they are link with one to one Relationship i'm fetch data from both table but now i want to add where condition that column in user table. this code working fine for fetch data.

        $clients = Profile::all();
    return view('admin.clients', compact('clients'));

But i want some thing like this.

'select profile.*, user.* from profile, user where profile.user_id=users.id and users.role_id=3';

how to convert this query in shape of above query.

Upvotes: 0

Views: 1919

Answers (2)

Rana Raheel Tariq
Rana Raheel Tariq

Reputation: 101

If you are fetch data with relational tables and use where condition in query write this code it's 100% working for me.

Profile::join('users', function($join){
        $join->on('profiles.user_id', '=', 'users.id')
        ->where('users.role_id', '=', 3);
    })
    ->get();

using join i'm successfully fetch all profiles where users role id equal 3 with other relational tables data like email from users table and service title from service table along with all data from profile table

Upvotes: 1

Carlos Salazar
Carlos Salazar

Reputation: 1898

You could add a where in your query relationship or in the relationship itself.

lets say you have a users relationship you could do this

Profile::with(['users' => function($users) {
    $users->where('role_id','=',3);
}])->get();

This will get you all the profiles, and filter the users that has the role = 3 of those profiles.

if what you want is to get the profiles of the users that has a role = 3 then you use whereHas

Profile::with('users')->whereHas('users', function($users) {
    $users->where('role_id','=',3);
})
->get();

Upvotes: 1

Related Questions