Query by lastest relationship value

Hi all!

I need help!

I cant solve this issue, looks simple but I have failed in all my attempts... I'm trying to create a query scope that can filter Post models by latest relationship value.

Post model hasMany() Statuses, each status has a date.

Simple. So, a Post can has multiple Status childs, where the only one that Im interested is the last one, specifically its date.

So my Post model has:

public function status()
{
    return $this->hasOne(Status::class)->latest();
}

My first attempt was simple:

public function scopeIsActive($builder) 
{
    return $builder->whereHas('status', function($q) {
        // It doesnt work because its taking all related Statuses....
        return $q->whereDate('date', '<=', today());
    });
}

Despite hasOne(), it still is fetching all related Statuses instead of last one :( First strike.

My second attempt make me dig in the dark world of sub queries. I finally found something that looks cool, like this:

public function scopeIsActive($builder)
{
    return $builder->whereHas('status', function($q) { // So, I want Statuses!
        return $q->where('statuses.id', function($sub){ // BUT, i dont want all of them, lets find out wich one
            return $sub->select('id') // I'll find it by id
                    ->from('statuses') // So lets find what Status I want
                    ->where('post_id', 'statuses.id') // It must point to the Post
                    ->whereDate('date', '<=', today()) // It must satisfies a condition
                    ->latest() // And, I want the last created, so order with latest()
                    ->limit(1) // just pick one  <--- It seems to be not working!!!!!!!!
        });
    }
}

If it helps, it is the resulted SQL:

"select * from "posts" where exists (select * from "statuses" where "posts"."id" = "statuses"."post_id" and "statuses"."id" = (select "id" from "statuses" where "post_id" = "posts"."id" and strftime('%Y-%m-%d', "date") <= cast(? as text) order by "created_at" desc limit 1))

And thas all guys, how its possible!?

Nobody can help me T_T I can't believe I can't get it work... It still fetches all related Statuses instead of the last one...

Please help!

Thanks!

Zalo

6 years ago

Level 19

Nobody? I'm sure that someone have faced this issue before.

It is: How can you filter a Model by its latest Relationship value???

I want to use Eloquent and EagerLoading, so I need it in a query scope.

I tried using max(id) but looks like it doesnt work or I don't know how to use it.

Please, someone help me!

Thanks!

Zalo

6 years ago

Level 19

Omg guys, sorry. I finally get it work. The problem was in the subquery. One of the condition should be outside (I don't know why).

It is the resulted scope

/**
 * @return Builder
*/
public function scopeIsActive()
{
    return $this->builder->whereHas('statuses', function (Builder $q) {
        return $q->whereDate('date', '<=', today()) // This must be outside :D
            ->where('statuses.id', function ($sub) {
                return $sub->select('id')
                    ->from('statuses')
                    ->whereColumn('posts.id', 'post_id')
                    ->latest()
                    ->limit(1);
            });
    });
}

Please or to participate in this conversation.