Laravel - for some reason, max breaks my query

Question

I have this piece of code:

$query = Student::whereHas('statusuri', function($q) use ($status) {
                    $q->latest('status_student.id')->take(1)
                    ->where('status_id', $status)
                    ->whereNotNull('status_id');
                });

and it works fine, but I don't necessarily get the desired result.

I tried changing the first line to max (so I don't filter all my records then do a limit 1), I just get the highest ID from the beginning - like this:

$query = Student::whereHas('statusuri', function($q) use ($status) {
                    $q->max('status_student.id')
                    ->where('status_id', $status)
                    ->whereNotNull('status_id');
                });

but then my query breaks.

For some reason, I get this:

Unknown column 'students.id' in 'where clause' (SQL: select max(`status_student`.`id`) as aggregate from `statusuri` inner join `status_student` on `statusuri`.`id` = `status_student`.`status_id` where `students`.`id` = `status_student`.`student_id`)

Why does my query break after I do this change?

Thanks.

Tables:
students
id bigint(20)
//other non-related data

statusuri
id  bigint(20) 
nume VARCHAR(255)

status_student
id int(11)
student_id int(10)
status_id int(10)
stare_stagiu_id int(11)
created_at timestamp
updated_at timestamp

statusuri() from Student

public function statusuri()
    {
        return $this->belongsToMany(Status::class, 'status_student')
            ->withPivot('id', 'data_inceput', 'data_sfarsit', 'document', 'status_id', 'stare_stagiu_id')
            ->withTimestamps();
    }

Status and StatusStudent classes

class StatusStudent extends Model
{
    protected $table = 'status_student';
    protected $fillable = ['id', 'student_id', 'status_id', 'stare_stagiu_id'];
}

class Status extends Model
{
    protected $table = 'statusuri';

    public $fillable = ['nume'];

}

user_4857150 · Answer

Your relations are messed up. The query is trying to use a column from the student table, but the student table is not available in said query as it is not joined. See this fiddle to see what goes wrong in the SQL. http://sqlfiddle.com/#!9/52c96fa/6 In the end, I'd do it like this if I understood you correctly:

In StatusStudent.php (Model):

public function student() {
    return $this->hasOne(Student::class, 'id', 'student_id');
}

In Controller:

public function stackoverflowtest() {
    //Set teststatus
    $status = 1;

    //Get the latest status of all users - and if that status is correct, retrieve into array
    $latest = DB::select( DB::raw("SELECT max(id) as id, student_id FROM status_student group by student_id"));
    $array = [];
    foreach ($latest as $l) {
        $status_id = StatusStudent::whereId($l->id)->whereStatusId($status)->first();
        if ($status_id) {
            array_push($array, $status_id);
        }
    }

    //$array now holds all the StatusStudent, simply user ->student to get the student related to said status, example below 
    if($array) {
        dd($array[0]->student);
        return $array;
    } else {
        return 'No match';
    }
}

First, we get all latest records for each user IF the status is correct. Then, we simply get the Student from the status_student table via the relation.

Laravel - for some reason, max breaks my query

1 Answers