Very complex sql function => Cake ORM

Need Help
1

I’m trying to convert a sql function into Cake’s ORM, but is having some problem with it. It’s divided into multiple queries using WITH, and I’m unsure of how to do this in Cake. Hope someone can help.

The SQL:

   WITH pns AS
   (
    SELECT DISTINCT
    p.id,
    coalesce(p.ki_common_name, p.ek_common_name),
    pn.id AS pni,
    pn.name,
    CASE WHEN pno.ok IS NULL THEN false ELSE pno.ok END AS ok
   FROM ki.persons p
   INNER JOIN ki.person_names pn ON (pn.person_id = p.id)
   LEFT JOIN analysis.person_name_ok pno ON (pno.person_name_id = pn.id)
   LEFT JOIN (SELECT (SELECT id FROM person_names WHERE person_id = p.id ORDER BY id LIMIT 1) AS primid FROM persons p) prim ON (prim.primid = pn.id)
   WHERE p.id NOT IN (SELECT * FROM ki.verify_do_not_include())
     AND (EXISTS (SELECT * FROM ki.kimkat_department_persons WHERE username = p.ki_username AND active_affiliation)
       OR EXISTS (SELECT * FROM ki.research_affiliations WHERE person_id = p.id AND active AND research_org))
     AND (include_ok OR pno.ok ISNULL OR pno.ok=false)
     AND prim.primid ISNULL
   ),
   selection AS
   (
    SELECT id FROM pns WHERE NOT ok GROUP BY id HAVING count(*) >= x
   )
   SELECT pns.* FROM pns INNER JOIN selection ON (selection.id = pns.id)
   ORDER BY pns.id, pns.pni;

This is my attempt so far at coding the ORM in Cake:

function getManyNamestrings(int $x, bool $include_ok): array {
    $sub1 = $this->find()
        ->select(['id' => 'id'])
        ->from(['person_names'])
        ->where(['person_id' => 'p.id'])
        ->order('id')
        ->limit(1);

$sub2 = $this->find()
    ->select(['primid' => $sub1])
    ->from(['p' => 'persons']);

$sub3 = $this->find()
    ->from(['ki.kimkat_department_persons'])
    ->where([
        'username' => 'p.ki_username',
        'active_affiliation',
    ]);

$sub4 = $this->find()
    ->from(['ki.research_affiliations'])
    ->where([
        'person_id' => 'p.id',
        'active',
        'research_org',
    ]);

$sub5 = $this->find()
    ->select([
        'pid' => 'p.id',
        'pname' => $this->find()->newExpr('COALESCE(p.ki_common_name, p.ek_common_name)'),
        'pnid' => 'pn.id',
        'namestr' => 'pn.name',
        'isok' => $this->find()->newExpr('CASE WHEN pno.ok IS NULL THEN false ELSE pno.ok END'),
    ])
    ->distinct()
    ->join(['pn' => ['table' => 'ki.person_names', 'type' => 'inner', 'conditions' => 'pn.person_id = p.id']])
    ->join(['pno' => ['table' => 'analysis.person_name_ok', 'type' => 'left', 'conditions' => 'pno.person_name_id = pn.id']])
    ->join(['prim' => ['table' => $sub2, 'type' => 'left', 'conditions' => "prim.primid = pn.id"]])
    ->where([
        $this->find()->newExpr('p.id NOT IN (SELECT * FROM ki.verify_do_not_include())'),
        $this->find()->newExpr("(EXISTS ($sub3) OR EXISTS ($sub4))"),
        $this->find()->newExpr()->isNull('prim.primid'),
    ]);

   if (!$include_ok) {
       $sub5->where('(pno.ok ISNULL OR pno.ok=false)');
   }

$sub6 = $this->find()
   ->select(['p' => $this->find()->func()->count('*')])
   ->from($sub5)
   ->where($this->find()->newExpr('NOT ok'))
   ->group('id')
   ->having(['p' >= $x]);

return $this->find()
    ->from($sub5)
    ->join(['table' => $sub6, 'type' => 'inner', 'conditions' => 'selection.id = pns.id'])
    ->order('pns.id, pns.pni')
    ->toArray();

}

2

If that query works, just use it with cakes pdo instance.