CODEWITHSUNDEEP
mysqllaraveleloquentrelationshiphas-many
Zalo
Zalo

Reputation: 704

Eloquent Filter by relationship (ONLY last record)

I edit my question to be more explicative

I need to build a scope for filtering a hasMany relationship. The problem is, I want to apply that query to last item ONLY, I mean, I want my Model filtered by a column of an specific record of a HasMany relationship, is it possible?

Context: An App that have Posts, each Posts have many Statuses to keep an historic. I want to filter the Posts by the last Status of each one.

Posts:

{
    public function statuses() 
    {
        return $this->hasMany(Status::class); // Only last one is important for querying
    }
}

Then, in an scope, I would like to do something like this:

public function scopeIsActive(Builder $builder)
{
    return $builder->whereHas('statuses', function(Builder $q) {
        // How to apply this ONLY to last created record???
        return $q->whereDate('activation', '<=', today());
    });
}

And that's all!

Edit: (Solved query)

After some digging I solved my problem with this:

public function scopeIsActive(Builder $builder)
{
    return $builder->whereHas('statuses', function (Builder $q) {
        return $q->whereDate('activation', '<=', today()) // applies to all
            ->where('statuses.id', function ($sub) { // applies to specific one
            return $sub->select('id')
                ->from('statuses')
                ->whereColumn('post_id', 'statuses.id')
                ->latest()
                ->limit(1);
        });
    });
}

Upvotes: 4

Views: 4271

Answers (3)

Mathieu Ferre
Mathieu Ferre

Reputation: 4412

instead of

public function others() 
{
    return $this->hasMany(Other::class); // Only last one is important for querying
}

you can just do :


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

and then

public function scopeCurrentActive(Builder $builder)
{
    return $builder->whereHas('other', function(Builder $q) {
        return $q->whereDate('activation', '<=', today());
    });
}

But You can't make a custom order, If you want to make a custom order, oyou have to use subqueries :

return Model::orderByDesc(
    Other::select('arrived_at')
        ->whereColumn('model_id', 'model.id')
        ->orderBy('activation', 'desc')
        ->limit(1)
)->get();

If you want to learn more about subqueries, you can refer to :

https://laravel-news.com/eloquent-subquery-enhancements

For using only the last record, you might have to use a having statement like this, but I'm not sure about the exact synthax ^^


public function scopeIsActive(Builder $builder)
{
    return $builder->whereHas('statuses', function (Builder $q) {
        return $q->havingRaw(Other::select('arrived_at')
        ->whereColumn('model_id', 'model.id')
        ->orderBy('activation', 'desc')
        ->limit(1)->select('activation')->toSql(), '<=' , today());
    });
}

Upvotes: 3

lephleg
lephleg

Reputation: 1764

Not sure what you exactly meant with the last record of each one.

Here is how you can get all the posts that have the same status as the latest post:

public function scopeIsActive(Builder $builder)
{
    $lastPostStatus = Post::select('status')
        ->orderBy('activation', 'desc')
        ->first()
        ->status;

    return $builder->where('status', $lastPostStatus);
}

Upvotes: 0

Ahmed Atoui
Ahmed Atoui

Reputation: 1556

to filter by items that has activate today write:

public function scopeCurrentActive()
{
    return $this->whereHas('other', function($q) {
        return $q->whereDate('activation', '==', Carbon::now()->toDateString());
    });
}

Upvotes: 0

Related Questions