Something

$query = 'select * from pages p left join content c on c.page=p.id where c.revision=(select max(revision) from content where page=p.id) and p.path=:page';
$q = $this->connection->prepare($query);
$result = $q->execute(array(':page'=>$page));
return $result;