Laravel get last row of a specific table without returning the actual object, doing it in a QueryBui-CodePudding

So I have this table.

I want to select the last row (by ID) with student_id = 1 and compare it with a specific status_id (which in my code is $status_id). (taken from a HTML form).

Expected behavior:

for student_id = 1 and status_id = 1:
    return nothing
for student_id = 1 and status_id = 6:
    return the row with student_id = 1, status_id = 6

Explanation:

The last row created has a status_id of 6, so return data only when the user sends the status_id of 6.

I tried using it this way:

$q->latest('id')->first()->where('status_id', $status);

But it won't work because first() returns an object not a QueryBuilder anymore, so the ->where() clause won't work anymore.

My question is: how can I get the first row in this case while still being in a QueryBuilder, or if it is not possbile, how can I do this query in Laravel?

CodePudding user response：

Assuming your table name is ‘students’.

Option 1:

$student_id = 1;
$status_id = 6;

$students = DB::table('students')
    ->where('id', function($query) use ($student_id) {
        $query->selectRaw('max(id)') // select highest id
            ->from('students')
            ->where('student_id', $student_id); // where student_id=1
    })
    ->where('status_id', $status_id) // only if status_id=6
    ->first();

// returns ROW if highest id with student_id 1 has status_id 6, otherwise null
dd($students);

Option 2:

$student_id = 1;
$status_id = 6;

$students = DB::table('students')
    ->where('id', function($query) use ($student_id) {
        $query->select('id') // select all id's
            ->from('students')
            ->where('student_id', $student_id) // where student_id=1
            ->orderByDesc('id') // sort it newest to oldest
            ->first(); // and get us the first
    })
    ->where('status_id', $status_id) // only if status_id=6
    ->first();

// returns ROW if highest id with student_id 1 has status_id 6, otherwise null
dd($students);

In both options we combine where 'highest id (given by a subquery) which has student_id' and where 'status_id=6'. Option 1 is better, because it directly selects the highest id. Option 2 first selects all rows with student_id=8, sorts it from newest to oldest and take the first.

Edit: I misunderstood the question first. Now edited based on conversation in comments.