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();
Advertisement
Answer
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 childrenTheParent::with(['child' => 'some condition'])
returns all parents with some childrenTheParent::whereHas('child', 'some condition')
returns some parents with all childrenTheParent::whereHas('child', 'some condition')->with(['child' => 'some condition'])
returns some parents with some children.