How to return rows in table1 not present in table2 in laravel

460
January 08, 2017, at 7:37 PM

I have tried using join table in laravel

role_user table has the roles_id and the user_id column roles table has the id and the name of the role users table has the id and the name of the user

 $roles = Role::leftJoin('role_user', 'roles.id', '!=' , 'role_user.role_id')
        ->select('roles.*', 'role_user.user_id', 'role_user.role_id')
        ->where('role_user.user_id','=',$name)
        ->get();

But it seems that '!=' is not working I have tried using the '=' and it's just working fine When im trying to use the '=' there are no results

I have also tried

public function roles()
{
return $this->belongsToMany('App\Role');
}
@foreach($user->roles as $role)
    {{ roles->name}}
@endForeach

But it displays the matching id of user_role and role table, I want to do the opposite i want to display the role table's rows that do not match the user_role's table

Answer 1

I want to display the role table's rows that do not match the user_role table

Use whereDoesntHave() method:

 $roles = Role::whereDoesntHave('users', function($q) use($name) {
     $q->where('name', $name);
 })->get();

In the view:

@foreach ($roles as $role)
    {{ role->name }}
@endforeach

Don't forget to define users() relationship:

public function users()
{
    return $this->belongsToMany('App\User');
}
Answer 2

In your User Model create function roles

Role::whereNotIn('id',function($query){
            $query->select('role_id')->from('role_user');
        })->get();
Rent Charter Buses Company
READ ALSO
Why I Can't Insert Video to MySQL's Database?

Why I Can't Insert Video to MySQL's Database?

I have written this code on phpmyadmin, mysql

370
Can't open the mysql.plugin table. Please run mysql_upgrade to create it

Can't open the mysql.plugin table. Please run mysql_upgrade to create it

I have downloaded mysql ZIP from here https://devmysql

953
How to fix MySql database restore error using Wordpress UpdraftPlus: Error Code: 1067. Invalid default value for 'user_registered'

How to fix MySql database restore error using Wordpress UpdraftPlus: Error Code: 1067. Invalid default value for 'user_registered'

When running my Wordpress UpdraftPlus database restore script I get the error Error Code: 1067Invalid default value for 'user_registered'

935