Mmh...

You sure it's the same ? I don't see the max, and moreover, it request for status = 300 ?

Just want to make it clear that a pass can have many processes but a process belongs to only one pass.

processes basically just represent the whole process of a pass being purchased all the way to be printed and posted to customers.

A pass can have more than one process when a pass is lost, stolen etc... so we reprint the pass rather than creating another pass hence the reason it can have another processes.

When a new process is created the old process is still stored but the latest one is the most important.

Hope that helps understand what I am working with.

@Vilfago Sorry my bad... I changed it slightly let me change it back and send the sql query again. Sorry

Here you go:

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

When a new process is created the old process is still stored but the latest one is the most important.

How do you identify the "important" process ? Do you have a "is_active" column or something like that ?

No there isn't and 'is_active' column but there is a 'count' column so:

1. Count = 1 for the first print
2. Count = 2 lost so reprinted
3. Count = 3 current

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 MAX(status) = 100) and `passes`.`deleted_at` is null

I expected that ... still not the last query.

And I wonder if status is in the processes tables. I understand what you do, but it's really hard to know how you organize your data.

It could be great if we could have a concrete example.

Yes Status is in the processes table its a column.

Unfortunately I am working on an existing database so I can't really change much on it.

what would be the best way of sharing a concrete example?

Just take three passes, and show us for each of them which data you have in which table/columns.

One that have a max status at 100, and another with the max status = 300, and the last one with many processes.

ok no problem.

trying to make it easy to show you not sure how to right it up....

From the table below I need the query to return all passes with their latest process status being 300.

So it should return passes 2, 3

Passes Table

• id = 1
• bus_route_id = 17
• type = 5

• id = 2
• bus_route_id = 2
• type = 1

• id = 3
• bus_route_id = 4
• type = 1

Processes Table

• id = 1
• pass_id= 1
• count= 1
• status= 500
• status= 20/01/2018

• id = 2
• pass_id= 1
• count= 2
• status= 300
• status= 15/02/2018

• id = 3
• pass_id= 1
• count= 3
• status= 100
• status= 10/05/2018

• id = 4
• pass_id= 2
• count= 1
• status= 300
• status= 14/01/2018

• id = 5
• pass_id= 3
• count= 1
• status= 300
• status= 08/05/2018

Too complicate for me, but if I understand well (maybe someone could do it), you have two options.

I consider that the last column is not status (already taken) but status_date.

Option A, the easy one, only if your status only grow (100 => 200 => 300 => 400 => 500).

Option B, the complicate, if the next status could be anything (100 => 300 => 200 => 500 => 400)

The beginning is probably the same :

$passes = Pass::select([
            'id',
            'type',
        ])->whereHas('processes', function ($query){
            $query->//Option A or Option B below
             })
          ->get();

Option A query where MAX(count) (so only the line where the count is max for this pass_id) and where for these lines the MAX of status is 100.

Option B query where MAX(count) (so only the line where the count is max for this pass_id) and where for these lines take only the last status_date and then take only if the statusis 100.

@Vilfago no problem thanks for you help will keep trying.

I kind of think this is a bad db design that could really be improved upon. For instance, I deal with real estate data, which constantly changes (price drops, goes off market, etc). What I do is keep all of the OLD data in a history table, and only the current data in the actual table, so I have 1 table to get current data from and another table to get all of the old data. Basically, latest process status being 300 would all be in a single table of "current_processes" or something, which would be a very simple query to get.

Hi @Cronix I agree but its an old database that I am working on and there isn't much flexibility on changing it at the moment unfortunately.

The only alternative I can think of doing and going to implement, is having soft deletes for the processes.

So I wont need to do anything significant changes to the database only have to add the deleted_at and then that will allow me to filter only the current ones.

but the only thing is it will be time consuming soft deleting each process which is old.

Might wanna try utilising subselects:

SELECT 
    passes.*, 
    (
        SELECT status FROM processes WHERE processes.pass_id = passes.id ORDER BY status_dt DESC LIMIT 1
    ) as latest_status
FROM passes
HAVING latest_status = 100

Using SubSelects might be less efficient, but with the right indexes should do just fine

Thanks for the reply @arukomp will look into it.

@arukomp YES! it worked but how shall I right it up in Laravel. Thank you so much this has been really draining.

The below are the actual names of the columns etc.. I was changing them to make it easier to read.

SELECT 
    gtcPasses.*, 
    (
        SELECT gtcpassesprocess.gtcPassesProcess_Status FROM gtcpassesprocess WHERE gtcpassesprocess.gtcPassesProcess_gtcPasses_ID = gtcPasses.gtcPasses_ID ORDER BY gtcpassesprocess.gtcPassesProcess_DateQueued DESC LIMIT 1
    ) as latest_status
FROM gtcpasses
HAVING latest_status = 300

I executed the query on sql and it works fine, just need to figure a way how to right it in Laravel/eloquent then hopefully it will working.

Try this snippet :

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

Let us know if this works.

@himanshu-dhiman thanks for your reply however, tried that in the past what @arukomp works however, need to figure a way to right the query in Laravel.

Another try

$passes =  DB::table('gtcPasses')
    ->select('gtcPasses.*', DB::Raw('(gtcpassesprocess.gtcPassesProcess_Status FROM gtcpassesprocess WHERE gtcpassesprocess.gtcPassesProcess_gtcPasses_ID = gtcPasses.gtcPasses_ID ORDER BY gtcpassesprocess.gtcPassesProcess_DateQueued DESC LIMIT 1) as latest_status')
    ->havingRaw('latest_status = 300')
    ->get();

@nizam0786 can you share the model snippets for Pass and Process ? That will really help.

@Vilfago its returning the following error:

(2/2) QueryException SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') as latest_status from gtcPasses having latest_status = 300' at line 1 (SQL: select gtcPasses.*, gtcPassesProcess.gtcPassesProcess_Status FROM gtcPassesProcess WHERE gtcPassesProcess.gtcPassesProcess_gtcPasses_ID = gtcPasses.gtcPasses_ID ORDER BY gtcPassesProcess.gtcPassesProcess_DateQueued DESC LIMIT 1) as latest_status from gtcPasses having latest_status = 300)

$passes =  DB::table('gtcPasses')
    ->select('gtcPasses.*', DB::Raw('(SELECT gtcpassesprocess.gtcPassesProcess_Status FROM gtcpassesprocess WHERE gtcpassesprocess.gtcPassesProcess_gtcPasses_ID = gtcPasses.gtcPasses_ID ORDER BY gtcpassesprocess.gtcPassesProcess_DateQueued DESC LIMIT 1) as latest_status')
    ->havingRaw('latest_status = 300')
    ->get();

@Vilfago nope error again :(

(2/2) QueryException SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT (gtcpassesprocess.gtcPassesProcess_Status FROM gtcpassesprocess WHERE gtc' at line 1 (SQL: select gtcPasses.*, SELECT (gtcpassesprocess.gtcPassesProcess_Status FROM gtcpassesprocess WHERE gtcpassesprocess.gtcPassesProcess_gtcPasses_ID = gtcPasses.gtcPasses_ID ORDER BY gtcpassesprocess.gtcPassesProcess_DateQueued DESC LIMIT 1) as latest_status from gtcPasses having latest_status = 300)

Done Finally :)

$passes =  DB::table('gtcPasses')
            ->select('gtcPasses.*', DB::Raw('(SELECT gtcpassesprocess.gtcPassesProcess_Status FROM gtcpassesprocess WHERE gtcpassesprocess.gtcPassesProcess_gtcPasses_ID = gtcPasses.gtcPasses_ID ORDER BY gtcpassesprocess.gtcPassesProcess_DateQueued DESC LIMIT 1) as latest_status'))
                ->havingRaw('latest_status = 300')
                ->get();

I updated it, but you already took it :)

Try again, just a switch in the DB::raw between select and (