CODEWITHSUNDEEP
phplaraveleloquentsubquery
Santiago Capdevila
Santiago Capdevila

Reputation: 125

Get the last element that fulfills condition in eloquent

I have 2 models, Queue and Action.

A queue has many actions, those actions could be pending, redirected, finished, etc.

I add an action for every action the given queue had been taken.

So, the first action is 'Pending' as soon as I add a queue object.

I need to retrieve the queues which their last action is either Pending (status_id = 1) or Redirected (status_id = 5)

My tables look like this

qms_queue id

qms_actions id status_id queue_id

My approach was something like this:

$queue_query->whereHas('actions', function ($query) {
            $query->where('id', function ($query2) {
                $query2->from('qms_actions')
                    ->select('id')
                    ->orderBy('created_at', 'desc')
                    ->limit(1);
            });
        })->whereIn('status_id', [1,5]);

But with that I retrieve the action that has status_id being either 1 or 5, but not being the last one of the given queue, so, every queue is returned.

I am not able to figure out how to do this in eloquent. Could anybody please help me out here?

Upvotes: 1

Views: 373

Answers (1)

Maraboc
Maraboc

Reputation: 11083

Try it like this, if it's ok to load the queues that has not the wanted actions :

$queue_query->with('actions', function ($query) {
            $query->whereIn('status_id', [1,5]);
            $query->orderBy('created_at', 'desc');
            $query->limit(1);
        });

Try this :

$queues = $queue_query->whereHas('actions', function ($query) {
            $query->where('id', function ($query2) {
                $query2->from('qms_actions')
                    ->select('id')
                    ->orderBy('created_at', 'desc')
                    ->whereIn('status_id', [1,5]);
                    ->limit(1);
            });
        })->get();

And you can get the action like that :

foreach ($queues as $queue) {
    //$queue->actions->first();
}

Upvotes: 1

Related Questions