CakePHP2 Postgres Lateral Join Hack

app-Config-bootstrap.php

<?php
// …
CakePlugin::load('PostgresApp');
// …

app-Config-database.php

<?php
// …
public $default = [
  // …
  'datasource' => 'PostgresApp.Database/PostgresApp',
  // …
];

app-Model-Post.php

<?php

// …
$posts = $this->find('all', [
  'fields' => [
    'Post.id',
    'Post.title',
    '"Comment"."field1" AS "Comment__field1",
    '"Comment"."field2" AS "Comment_field2",
  ],
  'joins' => [
    'type' => 'LEFT LATERAL',
    'alias' => 'Comment',
    'table' => '(
      SELECT field1, field2 FROM comments WHERE post_id = "Post"."id" AND truthy_column = true
    )',
  ],
]);
// …

app-Plugin-PostgresApp-Model-Datasource-Database-PostgresApp.php

<?php

App::uses('Postgres', 'Model/Datasource/Database');

/**
 * A Postgres driver to support our special "LEFT LATERAL" join case.
 */
class PostgresApp extends \Postgres
{
    /**
     * Renders a final SQL JOIN statement
     *
     * @param array $data The data to generate a join statement for.
     * @return string
     */
    public function renderJoinStatement($data) {
        if (strtoupper($data['type']) === 'LEFT LATERAL') {
            return "LEFT JOIN LATERAL {$data['table']} {$data['alias']} ON (TRUE)";
        }
        return parent::renderJoinStatement($data);
    }
}