Laravel - Eloquent - Filter based on latest HasMany relation

laravel filter relationship
laravel relationships
laravel eloquent relationships example
laravel nested relationships
laravel eloquent one to many example
laravel relationship with condition
laravel eloquent with function
laravel eloquent join

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

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);
    });

Filter Eloquent relationships "on-the-fly", when you need it, The phone method should call the hasOne method and return its result: Eloquent determines the foreign key of the relationship based on the model name. You can also filter the results returned by belongsToMany using the wherePivot  Defining Relationships. Eloquent relationships are defined as methods on your Eloquent model classes. Since, like Eloquent models themselves, relationships also serve as powerful query builders, defining relationships as methods provides powerful method chaining and querying capabilities.

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.

Eloquent: Relationships - Laravel, Filter products by hasMany relation (Names are for example.) This one works for 1 property per attribute, but not if 2 properties are there. hasManyThrough Relationship for Eloquent laravel 4 Tag: laravel-4 , eloquent After a lot of searching i couldn't find a real solution to my "problem", so if there any one how can help me, i’am all listening

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)

Filter products by hasMany relation, I want to retrive for each order the last order status so this is what I do: out an Eloquent relation to show only those orders with a status of 1. Filter Eloquent relationships “on-the-fly”, when you need it January 2, 2018 I find more and more Eloquent functions that are rarely used, so continue posting these short tips on the blog.

Eloquent hasMany filtering the latest, Eloquent determines the foreign key of the relationship based on the model name. To define the inverse of a hasMany relationship, define a relationship function on the child model Filtering Relationships Via Intermediate Table Columns. The model will automatically return a MongoDB-related or SQL-related relation based on the type of the related model. If you want this functionality to work both ways, your SQL-models will need to use the Jenssegers\Mongodb\Eloquent\HybridRelations trait.

Eloquent relationships, You're confusing whereHas and with . The with method will let you load the relationship only if the query returns true. The whereHas method will let you get only  That basically means, along the main model, Laravel will preload the relationship(s) you specify. This is especially helpful if you have a collection of models and you want to load a relation for all of them. Because with eager loading you run only one additional DB query instead of one for every model in the collection. Example: User > hasMany

Laravel Eloquent, An Eloquent Way To Filter Laravel Models And Their Relationships Latest Stable Version Total Downloads Daily Downloads License StyleCI Here is an example of Models and their respective filters based on the default naming convention use Filterable; public function clients() { return $this->hasMany(​Client::class); }  Laravel Eloquent when() method is crazy, say goodbye to if-else. Instead of using if and else conditionals, Eloquent provides a convenient method when(). For example, when we are filtering some table based on multiple conditions.

Comments
  • so you want to get the Lead by status so to say?For instance to get all Leads with the status 1
  • 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
  • Still did not understand it 100% could you explain again :)
  • Added some lines, hope it's clearer now
  • 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?:)
  • Good morning Sir, works great. I'll give it some thoughts because I'm not in love with filtering by created at
  • Not what I'm trying, I expanded the question