How to fetch filtered data from a pivot table

Question

I'm going to register an ambassador. Ambassadors have relationships with users. So I have a pivot table;

When I go to register an ambassador, I bring the data of a user and they are shown in a select. However, I can only show those users that were not registered as ambassadors or that are not in the ambassadors_users table.

I tried to do something like this but it duplicates the data.

$users = DB::table('ambassadors_users');
$users = $users->join('users', 'ambassadors_users.user_id', '!=', 'users.id');
$users = $users->get();

Answer 1

If you use Eloquent Relationships, can use whereHas(); it will helps you to check the users has ambassadors or not.

By the way below query may helps you-

$user_ids = DB::table('ambassadors_users')->pluck('user_id', 'user_id')->toArray();

Users those are not in ambassadors_users table-

$users = DB::table('users')->whereNotIn('id', $user_ids)->get();

Users those are in ambassadors_users table-

$users = DB::table('users');

$users = $users->join('ambassadors_users', 'users.id', 'ambassadors_users.user_id');

$users = $users->get();

Or, $users = DB::table('users')->whereIn('id', $user_ids)->get();