QueryBuilder - two models and a subquery

Need Help
1

Good evening,

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!