Laravel - whereHas last relation is = 'manual'

Related searches

I have a model Lead which 'belongsToMany' Status through lead_has_status table.

What I want is to get all the Leads where the last (current) status of that lead is 'manual'.

I've tried this

$leads = Lead::whereHas('statuses', function ($query) use ($q) {
  $query->where('description', 'LIKE', '%' . $q . '%');
})->get();

But since the Lead still has it's old statuses linked to it, it returns every Lead that at some point had Status 'manual'. But I only want the Leads where current status is 'manual'. So I want to do something like

$leads = Lead::whereLast('statuses', function ($query) use ($q) {
  $query->where('description', 'LIKE', '%' . $q . '%');
})->get();

Or

$leads = Lead::whereLast('statuses', function ($query) use ($q) {
  $query->latest()->first()->where('description', 'LIKE', '%' . $q . '%');
})->get();

(I know this it not the way to do it, it's simply to express what I'm trying to do)

I hope the question makes sense, I am super confused right now.

AFAIK there is no built-in eloquent function that gives you this kind of behaviour. If you would like to solve this with SQL only, you would have to look at the other answer using SQL function MAX in a sub-query and add raw queries in your Laravel code (probably a little bit more efficient then rejecting non-matching).

However you can filter your collection after retrieval and reject the elements that doesn't match your criteria.

Assuming the variable $q is of type string and contains a status e.g. "manual", something like this should do the job (code not tested).

$leads = Lead::whereHas('statuses', function ($query) use ($q) {
    $query->where('description', 'LIKE', '%' . $q . '%');
})
->with('statuses')
->get()
->reject(function($element) use($q) {
    $lastStatus = $element->statuses[count($element->statuses)-1];
    return (strpos($lastStatus->description, $q) === false);
});

If you're column description should contain the exact string "manual" I would do it this way instead:

$leads = Lead::whereHas('statuses', function ($query) {
    $query->where('description', 'manual');
})
->with('statuses')
->get()
->reject(function($element) {
    $lastStatus = $element->statuses[count($element->statuses)-1];
    return $lastStatus->description !== 'manual';
});

The whereHas method will not remove any relations like you need. The whereHas method will only say "select the records that have one or more children that matches this criteria", however all of the children records will be accessible, even the once that doesn't matches your criteria, as long as the parent has one that matches.

What you would like to say is "select the records that have one or more children that matches this criteria (whereHas) THEN remove all parents where the last child record does not match the record I am looking for (reject)"

Edit: Updated code using ->with('statuses') for improved performance.

Latest record from relationship in whereHas(), I'm currently working on the project of a stock management application (Laravel). I came to the point where anything I do doesn't work, so now I� It appears that the reason Laravel doesn’t natively support WhereHas on polymorphic relationships is that the very nature of a polymorphic relationship doesn't limit the type, or more

It looks like you already have a great Laravel answer, but in case you end up going down the native query option, which you can then hydrate, here's a raw sql option... At the least, I hope it might help you visualise what you're trying to achieve.

CREATE TABLE leads (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `info` varchar(100) NULL default NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE statuses (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `leads_id` int(11) null default null,
  `description` varchar(100) NULL default NULL,
  PRIMARY KEY (`id`)
);

INSERT INTO `leads` (`info`) VALUES ('foo'), ('bar'), ('test');

INSERT INTO `statuses` (`leads_id`, `description`) VALUES
(1, 'manual'),
(1, 'auto'),
(1, 'auto'),
(2, 'auto'),
(2, 'auto'),
(2, 'manual'),
(3, 'manual'),
(3, 'manual'),
(3, 'manual');

SELECT l.*
FROM statuses s 
JOIN leads l ON l.id = s.leads_id
WHERE s.id IN (
    SELECT MAX(s.id)
    FROM leads l
    JOIN statuses s ON s.leads_id = l.id
    GROUP BY l.id
)
AND s.description LIKE '%manual%'

whereHas latest child equals a value, This is the best answer I could find thus far that touches this topic, even though it uses the where function of eloquent. Posted 1 year ago by� Dynamic relationships in Laravel using subqueries - Jonathan Reinink php - Laravel - whereHas checking latest record of a relationship without checking others - Stack Overflow Tweaking Eloquent relations – how to get latest related model?

My solution is to define a new relationship on the Lead table.

class Lead extends Model
{
    public function lastStatus()
    {
        return $this->statuses()->latest()->limit(1);
    }
}

This way, the final query can be a bit simpler.

$leads = Lead::whereHas(‘lastStatus’, function ($query) use ($q) {
    $query->where(‘description’, ‘LIKE’, $q);
}

Dynamic scope on latest record in Laravel's HasMany relationships , Dynamic scope on latest record in Laravel's HasMany relationships, Part $ device) { return $query->whereHas('logins', function ($query) use� Hello Web Artisans and Laravel Enthusiasts,. I had a very interesting and challenging question while browsing through Laracasts. What If we need to have a whereHas constraint on a Many mapping (HasMany, BelongToMany) Relation such that "Related Entity is also subjected to a Constraint"?

Eloquent: Relationships - Laravel, Has. has() is used to filter the selecting model based on the selected relationship. It is basically a where method for relations. A polymorphic relationship allows the target model to belong to more than one type of model using a single association. One To One (Polymorphic) Table Structure. A one-to-one polymorphic relation is similar to a simple one-to-one relation; however, the target model can belong to more than one type of model on a single association.

Laravel – Eloquent “Has”, “With”, “WhereHas”, Today I want to talk about a feature of Laravel which is really useful but can be potentially difficult to understand at first. Pivot table is an example of intermediate table with relationships between two other "main" tables.

Laravel whereHas Relationship Query General Error: 25 Posted 4 years ago by bin0. Okay, so first I'll explain what I'm trying to do here. So picture this: there is a

Comments
  • One thing I forgot to mention was that I want to paginate the result. How do I go about doing that along with the reject? ->get()->reject() seems to work fine, but ->paginate(10)->reject() doesn't seem to work
  • Have not used Laravel's pagination, however I think fetching records, then removing them can create some problems with pagination. I would suggest take a look at Kevin Bui's answer below (second solution). Define a relationship returning the last record only. That way you don't have to use reject, and you don't fetch unnecessary records like my answer will, and you can use pagination as normal.
  • This is definitely work, but not very performant. For each $lead object, you are running an extra query to the database.
  • That is true, however this can easily be solved by adding ->with('statuses') resulting in one query only.
  • Fixed the code now for improved performance. Still, I think @KevinBui answer is the way to go. He deserves the correct answer on this one.
  • This is the way to go for best efficiency.
  • Nice workaround, without testing the code I think this will work, giving OP the possibility to solve the problem with pure eloquent-functionality.
  • Thanks @Andrew. This hasOne relationship with a condition is pretty neat!
  • my statuses() relation in my Lead class is a 'belongsToMany' so wouldn't my lastStatus() be belongsTo instead of hasOne? Anyways, I've just tested this, but it throws an error: Column not found: 1054 Unknown column 'leads.last_status_id' in 'where clause' (SQL: select * from leads where (exists (select * from statuses where leads.last_status_id = statuses.id and description LIKE %Manual%)))
  • @Nissen just copy-paste your statuses() function, rename it to lastStatus() and add ->latest();.
  • if I add latest() all it does is reverse the collection, so it doesn't just take the last status, it still takes all the statuses, but in reverse