Laravel - Eloquent - Filter based on latest HasMany relation

Question

I have this two models, Leads and Status.

class Lead extends Model
{
    public function statuses() {
        return $this->hasMany('App\LeadStatus', 'lead_id', 'id')
            ->orderBy('created_at', 'DESC');
    }

    public function activeStatus() {
        return $this->hasOne('App\LeadStatus', 'lead_id', 'id')
            ->latest();
    }


}

class LeadStatus extends Model
{
    protected $fillable = ['status', 'lead_id'];
}

This works fine, now I'm trying to get all Leads based on the 'status' of the last LeadStatus.

I've tried a few combinations with no success.

if ($search['status']) {
            $builder = $builder
                ->whereHas('statuses', function($q) use ($search){
                    $q = $q->latest()->limit(1);
                    $q->where('status', $search['status']);
                });
        }



 if ($search['status']) {
            $builder = $builder
                ->whereHas('status', function($q) use ($search){
                    $q = $q->latest()->Where('status', $search['status']);
                });
        }

Has anybody done this with Eloquent? Do I need to write some raw SQL queries?

EDIT 1: I'll try to explain again :D

In my database, the status of a lead is not a 1 to 1 relation. That is because I want to have a historic list of all the statuses which a Lead has had.

That means that when a Lead is created, the first LeadStatus is created with the status of 'new' and the current date.

If a salesman comes in, he can change the status of the lead, but this DOES NOT update the previous LeadStatus, instead it creates a new related LeadStatus with the current date and status of 'open'.

This way I can see that a Lead was created on 05/05/2018 and that it changed to the status 'open' on 07/05/2018.

Now I'm trying to write a query using eloquent, which only takes in count the LATEST status related to a Lead.

In the previous example, if I filter by Lead with status 'new', this Lead should not appear as it has a status of 'open' by now.

Hope this helps

so you want to get the Lead by status so to say?For instance to get all Leads with the status 1 — utdev, May 07 '18 at 16:48
Exactly, but only by the latest status of the Lead. That means that if the lead has a related status of open from 2 weeks ago and another status that is closed 1 week ago. It should be returned only if I filter for closed Leads — Borjante, May 07 '18 at 16:49
So you filter the Leads through the status and another property? Couldn't you use the `->latest()` helper method of laravel in addition to the query to accomplish this?:) — utdev, May 07 '18 at 17:03

Jonas Staudenmeir · Accepted Answer · 2018-05-08T10:53:15.327

Try this:

Lead::select('leads.*')
    ->join('lead_statuses', 'leads.id', 'lead_statuses.lead_id')
    ->where('lead_statuses.status', $search['status'])
    ->where('created_at', function($query) {
        $query->selectRaw('max(created_at)')
            ->from('lead_statuses')
            ->whereColumn('lead_id', 'leads.id');
    })->get();

A solution using the primary key (by Borjante):

    $builder->where('lead_statuses.id', function($query) {
        $query->select('id')
            ->from('lead_statuses')
            ->whereColumn('lead_id', 'leads.id')
            ->orderBy('created_at', 'desc')
            ->limit(1);
    });

Good morning Sir, works great. I'll give it some thoughts because I'm not in love with filtering by created at — Borjante, May 08 '18 at 07:28

score 2 · Answer 2 · answered Jan 24 '19 at 10:29

I had this same problem and posted my solution here but I think it's worth re-posting as it improves on the re-usability. It's the same idea as the accepted answer but avoids using joins, which can cause issues if you want to eager load relations or use it in a scope.

The first step involves adding a macro to the query Builder in the AppServiceProvider.

use Illuminate\Database\Query\Builder;

Builder::macro('whereLatestRelation', function ($table, $parentRelatedColumn) 
{
    return $this->where($table . '.id', function ($sub) use ($table, $parentRelatedColumn) {
        $sub->select('id')
            ->from($table . ' AS other')
            ->whereColumn('other.' . $parentRelatedColumn, $table . '.' . $parentRelatedColumn)
            ->latest()
            ->take(1);
    });
});

This basically makes the sub-query part of the accepted answer more generic, allowing you to specify the join table and the column they join on. It also uses the latest() function to avoid referencing the created_at column directly. It assumes the other column is an 'id' column, so it can be improved further. To use this you'd then be able to do:

$status = $search['status'];
Lead::whereHas('statuses', function ($q) use ($status) {
    $q->where('status', $userId)
        ->whereLatestRelation((new LeadStatus)->getTable(), 'lead_id');
});

It's the same logic as the accepted answer, but a bit easier to re-use. It will, however, be a little slower, but that should be worth the re-usability.

score 0 · Answer 3 · answered May 07 '18 at 16:52

If I understand it correctly you need / want to get all Leads with a specific status.

So you probably should do something like this:

    // In your Modal

    public function getLeadById($statusId)
    {
        return Lead::where('status', $statusId)->get();
        // you could of course extend this and do something like this:
        // return Lead::where('status', $statusId)->limit()....->get();
    }

Basically I am doing a where and returning every lead with a specific id.

You can then use this function in your controller like this:

Lead::getLeadById(1)

Not what I'm trying, I expanded the question – Borjante May 07 '18 at 16:58 — Borjante, May 07 '18 at 16:58

Laravel - Eloquent - Filter based on latest HasMany relation

3 Answers3