table 1

| ID | val1 |

| 1 | a |

| 2 | b |

| 3 | c |

table 2

| ID | val1 |

| 1 | a |

| 2 | b |

I need to get the table1 data, which is not on table 2

$users_without_info = DB::table('users')->rightJoin('user_infos', 'users.email', '=', 'user_infos.email')
->select('users.*')
->latest()
->get();

I tried this command, doesn't work.

CodePudding user response：

use inner join

$users_without_info = DB::table('users')
        ->join('user_infos', 'users.email', '!=', 'user_infos.email')
        ->select('users.*')
        ->latest()
        ->get();

if you have models then use relationships with the foreign key then in user model add relation

public function userInfo(){

   return $this->hasOne(UserInfo::class);
}

and the query will be

$users_without_info=\App\Models\User::doesntHave('userInfo')->get();

if you don't have foreign key and want to use email as unique identifier then

public function userInfo(){

 return $this->hasOne(UserInfo::class,'email','email');
}

CodePudding user response：

Here is the solution,

• The issue is in the join statement.
• You can try this way to get the table-1(users) data, which is not on table-2(user_infos),

$users_without_info = DB::table('users')
  ->leftJoin('user_infos', 'users.email', '=', 'user_infos.email')
  ->whereNull('user_infos.email')
  ->get();

Hope you will fix it..