Laravel5 Eloquent中WhereNotIn未达预期，求问题排查与修正

First, let’s recap what your original SQL is doing clearly:

1. It joins chapters with chapters_has_parents to get all chapters that act as parent nodes (since chapters.id = chapters_has_parents.parents_id).
2. It then filters those chapters to only keep ones that are never listed as a child node anywhere in the chapters_has_parents table (via chapters.id NOT IN (SELECT children_id FROM chapters_has_parents)).

The Problem with Your Current Eloquent Code #

Your whereNotIn('chapters_has_parents.children_id', ['*']) line is the culprit:

• You’re targeting the wrong field: the original SQL checks if chapters.id is excluded from the children_id list, not the other way around.
• Using ['*'] as the exclusion set is meaningless here — it’s not a valid list of values or a subquery, so the database just ignores this condition entirely. That’s why removing it doesn’t change your results.

Correct Eloquent Implementations #

Option 1: Mirror the Original SQL Directly

We’ll use a subquery inside whereNotIn to replicate the exact logic of your SQL:

$startingChapter = Chapter::select('chapters.id', 'chapters.title')
    ->join('chapters_has_parents', 'chapters.id', '=', 'chapters_has_parents.parents_id')
    ->whereNotIn('chapters.id', function ($query) {
        // This subquery gets all child IDs from the junction table
        $query->select('children_id')
              ->from('chapters_has_parents');
    })
    ->get();

Option 2: Use Eloquent Relationships (Cleaner Approach)

If you define relationships on your Chapter model, you can avoid manual joins entirely. Add these to your Chapter model first:

// A chapter can have many child chapters (via the junction table)
public function childChapters()
{
    return $this->belongsToMany(Chapter::class, 'chapters_has_parents', 'parents_id', 'children_id');
}

// A chapter can have many parent chapters (via the junction table)
public function parentChapters()
{
    return $this->belongsToMany(Chapter::class, 'chapters_has_parents', 'children_id', 'parents_id');
}

Then your query becomes much more readable:

$startingChapter = Chapter::select('id', 'title')
    ->has('childChapters') // Only keep chapters that are parents (have children)
    ->doesntHave('parentChapters') // Exclude chapters that are children of others
    ->get();

Both of these approaches will return the same result as your original SQL: the chapter with id=1 and title="introduction".

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