求助：如何基于另一张表的字段过滤$employees数据表

Hey there! Let's get this filtering sorted out for you. It looks like you're trying to narrow down your $employees collection to only those records where the other_id matches any id from the $other_ids collection. I'll cover a few different approaches depending on whether you're working with Eloquent ORM (since you mentioned whereHas), raw collections, or straight SQL.

1. Eloquent ORM (Laravel) Approach #

If you're using Laravel's Eloquent, the simplest and most efficient way is to use whereIn with a list of allowed IDs:

// First, grab all the IDs from $other_ids that we want to match
$allowedIds = OtherId::pluck('id');

// Filter employees where other_id is in the allowed list
$employees_filtered = Employee::whereIn('other_id', $allowedIds)->get();

If you want to use whereHas (with a model relationship) #

First, make sure your Employee model has a relationship defined to the OtherId model:

// In Employee.php
public function otherId()
{
    return $this->belongsTo(OtherId::class, 'other_id', 'id');
}

Then you can use whereHas to filter employees that have a matching related record:

$employees_filtered = Employee::whereHas('otherId', function ($query) {
    $query->whereIn('id', OtherId::pluck('id'));
})->get();

Fixing your Left Join attempt #

If you tried a left join but didn't get the right results, you need to add a condition to exclude records where the join didn't match:

$employees_filtered = Employee::leftJoin('other_ids', 'employees.other_id', '=', 'other_ids.id')
    ->whereNotNull('other_ids.id') // Only keep records that have a match
    ->select('employees.*') // Ensure we only fetch employee fields, not join fields
    ->get();

2. Collection Filtering (If you already have both collections loaded) #

If you've already retrieved $employees and $other_ids into memory as collections, you can filter directly on the collections:

// Extract the allowed IDs into an array
$allowedIds = $other_ids->pluck('id')->toArray();

// Filter the employees collection
$employees_filtered = $employees->filter(function ($employee) use ($allowedIds) {
    return in_array($employee['other_id'], $allowedIds); // Use ->other_id if working with model objects
});

Or use the built-in whereIn collection method for a cleaner approach:

$allowedIds = $other_ids->pluck('id');
$employees_filtered = $employees->whereIn('other_id', $allowedIds);

3. Raw SQL Query #

If you prefer to use raw SQL, this query will give you the exact filtered results:

SELECT employees.*
FROM employees
WHERE employees.other_id IN (SELECT id FROM other_ids);

Alternatively, using an inner join (which automatically only keeps matching records):

SELECT DISTINCT employees.*
FROM employees
INNER JOIN other_ids ON employees.other_id = other_ids.id;

All of these methods will return the 1st, 2nd, and 4th records from your example, excluding the employee with other_id: 3.

内容的提问来源于stack exchange，提问作者Sachin