Retrieving parent models when latest child meets specific date based conditions

I have a parent child relationship as follows:

class Parent extends Model
{
    public function child() {
        return $this->hasMany(Child::class);
    }

    public function latestFooChild() {
        return $this->hasOne(Child::Class)->foo()->latest('birth_date');
    }
// ...

class Child extends Model
{
    public scopeFoo() {
        return $query->where('foo', 1);
    }
// ...

I want to get Parents who's oldest Foo child was born between two dates.

If I do

Parent::with('latestFooChild')->whereHas('latestFooChild', function($q) {
    $q->where('birth_date', '>', $start_date)->andWhere('birth_date', '<', $end_date);
}

Then the relation gets filtered rather than only return parents who's oldest child was born between those dates (i.e. the parent's younger child might fulfill the criteria)

I've been able to create the SQL for it as follows:

select parents.name, childs.birth_date from parents left join childs on (childs.parent_id = parents.id and
childs.birth_date::DATE = (select max(birth_date) from childs c2 where c2.parent_id = parents.id and c2.foo = 1)::DATE)
where childs.birth_date > $start_date
and childs.birth_date < $end_date

but I would like to avoid using raw sql if I can at all help it, and I can't help but feel like I might just be missing some eloquent magic somewhere to achieve this.

splatEric

7 years ago

Level 2

At the risk of appearing to be talking to myself, I've moved forward on this a bit, encapsulating the necessary rawSQL into the relation so I can mostly leverage eloquent query building still.

For the parent, I have defined the following relation:

function oldest_foo_child() {
    return $this-hasOne(Child::class)
        ->whereRaw('birth_date = (SELECT MAX(birth_date) from childs c2 where c2.parent_id = parents.id AND foo = 1');
}

And then I can carry out the filtering query as follows:

Parent::whereHas('oldest_foo_child', function($q) {
    $q->where('birth_date', '>', $start_date)
        ->andWhere('birth_date', '<', $end_date);
    });

I abandoned using the scope for Foo, as it would be used externally to the raw conditional, and therefore not always get the right result.

This seems to be enough to move forward (got a deadline to meet), but would still be interested in how other people might approach this

splatEric

7 years ago

Level 2

The approach above is limited as you can only populate the oldest_foo_child relationship on Parent instances in initial queries. If you want to be able to retrieve it without loading the relation initially, it needs a bit more ...

function oldest_foo_child() {
    $relation =  $this-hasOne(Child::class);
    $raw = 'birth_date = (SELECT MAX(birth_date) from childs c2 where foo = ? AND c2.parent_id = ';
    $bindings = [1];
    if ($this->id) {
        // implies we're hydrating the relation for a single instance
        $raw .= ' ?)';
        $bindings[] = $this->id;
    } else {
        $raw .= 'horses.id)';
    }
    return $relation->whereRaw($raw, $bindings);
}

Please or to participate in this conversation.