Getting all objects if the latest status is e.g. '100'

$passes = Pass::select([
            'id',
            'type',
        ])->whereHas('processes', function ($query){
            $query->where('status', 100)
              ->where('created_at', '>', DATE HERE);
        })->get();

@topvillas thanks for your reply what date would I put there?

Lets says they are 3 processes that belong to a pass and the dates are below:

1. date: 20/04/2018
2. date: 10/05/2018
3. date: 18/05/2018

I just want to get the most recent process which would be number 3 above.

please note there are hundreds of passes and processes. Thanks

$passes = Pass::select([
            'id',
            'type',
        ])->whereHas('processes', function ($query){
            $query->where('status', 100)->last();
        })->get();

Wouldn't it be just this easy?

Hi @JorickL thanks for your reply tried it but doesn't work it returns the following errror:

BadMethodCallException Call to undefined method Illuminate\Database\Query\Builder::last()

Probably you have to put ->get() before last() because the last() method is a collection method and not a query builder method.

$query->where('status', 100)->get()->last();

@JorickL but I need to get the Pass object so wouldn't it need to be outside the whereHas()...?

I did try it anyway didn't work.

Thanks

I think this will do what you're wanting. It gets the most recent process where status is 100. If you want more than one returned, adjust the ->take() or remove it. For instance ->take(5) will give the 5 most recent processes. If you remove it, it will just give all processes by the created_at in descending order (that's what ->latest() does).

$query->where('status', 100)->latest()->take(1);

@Cronix I tried that but it takes the one after checking checking where status is 100 I want it to take('1') and then perform the where() status is 100.

I have tried moving the latest() and take(1) before the where() but still makes no difference.

hope that makes sense.

It still returns pass with process->status == 100 even after filtering it with latest() and take().

I want it to basically ignore the previous processes and just check the latest one for the status.

No it doesn't make sense. It sounds like the same thing, really. And you stated

I am trying to right a query which gets all passes with the latest process->status equalling to 100.

I can get all passes with a process that has a status that equals 100 but that's not what I want I just want the query to focus and look at the most recent process.

That's basically what it does. It creates "where status = 100 order by created_at desc limit 1" to give the most recent process where status is 100.

If I understood well the request of @nizam0786 (after 4 threads here) :

His table "processes" receive a new entry each time the pass have a new status. For example :

• Monday : id 1, pass_id 1, status 0;
• Tuesday : id 1, pass_id 1, status 100;
• Wedn. : id 1, pass_id 1, status 150;
• Thursday : id 1, pass_id 1, status 200;
• etc.

In this case (= for this thread, because the OP changed the status he request for at each thread), the pass_id 1 should not be retrieve, has it's not the last status (which is 200). The OP only want pass where the last status is 100.

So I think he need a subquery to retrieve the status value of the last item in "processes" table to fill his where.

If what @vilfago posted is correct, then maybe this would work

$query->where('status', 100)->latest('pass_id')->latest()->take(1);

Sorry if i am not making alot of sense I am trying my best to explain.

each time a pass is created a process is created with it

1 pass & 1 process

then for example a customer has a pass but we need to reprint it for them there is a reprint function which then creates a new process which makes the previous processes invalid or not needed but I still need them for history.

whats happening is I have a page which lists all passes with status 'waiting to be posted' i.e. 100

I want to list all passes with that status on the page but some passes are being listed there which shouldn't be because the query is checking all processes and if 100 exist in an old one it will list that too.

I want it to only focus on the last entry on the most recent.

statuses I have are

• 100
• 200
• 300
• 400
• 500

Thanks guys will try it out and let you know.

Hi @Cronix and @Vilfago I tried the suggestion still did not work because what @Vilfago explained thats not exactly whats happening my message above explains whats happening but I think a subquery so just looking into that.

back to basic :

$passes = Pass::select([
            'id',
            'type',
        ])->whereHas('processes', function ($query){
            $query->havingRaw('MAX(status) = 100');
        })->get();

I'm don't really used to raw query, but maybe someone else will correct it if not working.

The raw sql is

select passes_id, id , type, from passes where exists (select * from processes where passes.passes_id = processes.processes_id and processes.deleted_at is null) and exists (select * from processes where passes.passes_id = processes.processes_pass_id and processes_status = ? and processes.deleted_at is null order by processes_pass_id desc, created_at desc limit 1) and passes.deleted_at is null

Hi @nizam0786

Can you share what you have done in your route and controller?

In eloquent you can easily use the "latest" method which gives you the result by date and you follow with the method "first"

Example:

$user = DB::table('users')->latest()->first();

I just saw this now in laravel docs: https://laravel.com/docs/5.6/queries

@Vilfago thanks for the reply will try that now.

@Melodia thanks for the reply I have already tried latest() and first().

@Vilfago it returned a

(2/2) QueryException SQLSTATE[42000]: Syntax error or access violation: 1140 In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'gtctesting.processes.passes_process_id'; this is incompatible with sql_mode=only_full_group_by

I need to figure a way to say in the sql to only check the latest process belonging to the pass to see if its status is 300 rather than all of them hope that makes sense.

The raw sql is

select passes_id, id , type, from passes where exists (select * from processes where passes.passes_id = processes.processes_id and processes.deleted_at is null) and exists (select * from processes where passes.passes_id = processes.processes_pass_id and processes_status = 300 and processes.deleted_at is null order by processes_pass_id desc, created_at desc limit 1) and passes.deleted_at is null

After doing raw queries in the database I kind of understand a bit better what is happening.

the query i was using was just checking to see if a process with status 300 exists return the pass which obviously was checking all processes rather than just the latest one.

So know I am getting the latest process but how would I check to see the status in the whereHas()?

$passes = Pass::select([
            'id',
        'type'
        ])->has('process')->whereHas('process', function ($query){
//need to check the status of this process and if it equals to 300 then return the pass otherwise don't. At the moment its just checking to see if it exists so its returning all the passes because every pass has a process.
            return $query->latest('date_queued')->take(1);
        })->get();

Thanks in advance

I think the only way to do it is joining the latest process and then checking the status of it.

if anyone knows how to do that please would appreciate the help.

I have tried the following but is only returning one pass? isn't there a way to use a join() in a whereHas() or another way to loop through the passes and join one process at a time.

 
$passes = Pass::select([
'pass_id',
'type',
'process_status',
])->has('process')->join('processes', function ($join)
{
$join->on('passes.pass_id', '=', 'processes.process_pass_id')
->where('processes.date_queued', '=', DB::raw("(select max('date_queued') from 'processes')"));
})->get();

$passes = Pass::select([
            'id',
            'type',
        ])->whereHas('processes', function ($query){
            $query->select('date_queued')
            ->groupBy('date_queued')
            ->havingRaw('MAX(status) = 100');
        })->get();

@Vilfago Thanks for the reply again.... still returns a process which 'latest' status is not 100.

At the moment it just returns all passes with a process that has status == to 100.

I need the passes to be returned only if the 'latest' status is equal to 100.

hope that makes sense.

$passes = Pass::select([
            'id',
            'type',
        ])->whereHas('processes', function ($query){
            $query->select('status')
            ->groupBy('status')
            ->havingRaw('MAX(status) = 100');
        })->get();

Could you let us see the query created by this code ?

And could you confirm that the last status has always a number greater than the previous ones ?

@Vilfago yeah sure.

select `id`, `type`,  from `passes` where exists (select `status` from `processes` where `passes`.`id` = `processes`.`pass_id` and `processes`.`deleted_at` is null group by `status` having status = 300) and `passes`.`deleted_at` is null

note: my variable names are different just renaming them to make it easy to understand.