I need help to turn some raw SQL into Eloquent code-CodePudding

As the title says, I'm having trouble turning my SQL code into Eloquent.

Here's the raw SQL:

select * from `students`
where (
    select status_student.id from `status_student` 
WHERE
status_student.id = (SELECT MAX(status_student.id) from `status_student`
WHERE 
status_student.student_id = students.id)
AND
status_student.status_id = 6
) 
IS NOT NULL;

and here's the code so far:

$status = $this->request->get('status');
if ($status !== "0") {
dd($query = Student::where(function($q) use($status){
                    $q->where('status_student.id', function($q2) use ($status) {
                        $q2->selectRaw('MAX(status_student.id)')
                        ->where('status_student.student_id', '=', 'students.id')
                        ->where('status_student.status_id', '=', $status);
                    });
                })->toSql());
}

which translates into SQL:

"select * from `students` where (`status_student`.`id` = (select MAX(status_student.id) where `status_student`.`student_id` = ? and `status_student`.`status_id` = ?))

so it's not working properly yet.

I'm having trouble introducing that IS NOT NULL clause and doing

select status_student.id from 'status_student'

instead of just

status_student.id =

How should I modify my code to get the desired SQL?

Thanks.

CodePudding user response：

$status = request()->get('status');
        if ($status !== "0") {
            dd($query = Student::where(function($q) use($status){
                $q->where([['status_student.id' => function($q2) use ($status) {
                    $q2->selectRaw('MAX(status_student.id)')
                        ->where('status_student.student_id', '=', 'students.id')
                        ->where('status_student.status_id', '=', $status);
                }],
                    ['status_student.id', '<>', null]
                ]);
            })->toSql());
        }

Generated SQL query:

SELECT *
FROM `students`
WHERE (((`status_student`.`student_id` = ?
     AND `status_student`.`status_id` IS NULL)
    AND `status_student`.`id` IS NOT NULL))

Not sure if I understanded you correctly but I assume you just didn't get the syntax right

CodePudding user response：

try this one:

$status = $this->request->get('status');

if ($status !== "0") {
  // log the queries:
  \DB::enableQueryLog();
  $query = Student::with([
     'statusStudent' => function($q1) use($status) {
        $q1->where('status_student.id', function($q2) use ($status) {
        $q2->selectRaw('MAX(status_student.id)')
         ->where('status_student.student_id', '=', 'students.id')
         ->where('status_student.status_id', '=', $status);
        });
      }
  ])

  ->get();
  $pureQuery = \DB::getQueryLog();
  dd($pureQuery);
}

If you would like to join in eloquent, you must use with() or load() to do that.