I’m using cakePhp 4 and I get stuck with a query. I have a Students and a Studentmarks table. I’d like to retrieve students with their last mark, including students who may have not marks yet. My SQL works just fine in phpmyadmin but I can’t push it to cakePhp.
Here is the SQL:
SELECT * FROM students AS s LEFT JOIN studentmarks AS m ON s.id = m.student_id WHERE m.markdate = (SELECT MAX(markdate) FROM studentmarks WHERE studentmarks.student_id = students.id) OR m.markdate IS NULL
Here is my closest (but still far) try to get something working but there is obviously something I understand wrongly so any help is the most welcome:
$subquery = "(SELECT MAX(markdate) FROM studentmarks WHERE studentmarks.student_id = students.id); $this->paginate = [ 'contain' => ['Studentmarks'], 'conditions' => $subquery, ]; $students = $this->paginate($this->Students);
Thanks in advance for any tips!