I want to get the results of this very simple, common SELECT statement:

SELECT * FROM parent
  JOIN child ON parent.id = child.parent_id
  WHERE child.column = 1;

For the non-SQL fluent, this returns all of the columns for all parent and child rows where the child column named column contains the value 1.

Using the Laravel models below, what's the correct Eloquent way to do this?

<?php
class TheParent extends Model {
    public function child(): HasMany {
        return $this->hasMany(Child::class);
    }
}

class Child extends Model {
    public function parent(): BelongsTo {
        return $this->belongsTo(TheParent::class);
    }
}

// Fails.  Returns empty set.
$data = TheParent::getModel()
    ->child()
    ->where('column', 1)
    ->get();    // returns empty set

// Fails:  Returns correct data FOR JUST ONE parent Model if and only if a
// child meets the conditions. But find() is not usable for my purpose.
$data = TheParent::find(1)
    ->child()
    ->where('column', 1)
    ->get();

// Fails:  Only returns the parent data and cannot reference the child.
$data = TheParent::whereHas(
    'child',
    function ($query) {
        $query->where('column', 1);
    }
)->get();

CodePudding user response：

You were close with your last attempt; your callback filters the Parent instances returned, but not the attached Child instances. Try something like this:

$data = TheParent::whereHas('child', fn($q) => $q->where('column', 1))
    ->with(['child' => fn($q) => $q->where('column', 1)])
    ->get();

The callback has to be repeated for both the whereHas and with methods...

• TheParent::with('child') returns all parents with all children
• TheParent::with(['child' => 'some condition']) returns all parents with some children
• TheParent::whereHas('child', 'some condition') returns some parents with all children
• TheParent::whereHas('child', 'some condition')->with(['child' => 'some condition']) returns some parents with some children.