【cakephp】サブクエリを実行する方法メモ

サブクエリのサンプルメモ。
ついでにgroup by した結果のレコード数を求めるサンプル。

$dbo = $this->MyTable->getDataSource();
$subQuery = $dbo->buildStatement(array(
        "fields" => array("distinct MyTable.id"),
        "table" => "my_table",
        "alias" => "MyTable",
        "conditions" => array(
                "MyTable.deleted =" => 0,
        ),
        "joins" => array(
                "type" => "left",
                "table" => "other_table",
                "alias" => "OtherTable",
                "condition" => array(
                        MyTable.id = OtherTable.my_table_id,
                ),
        ),
        "group" => array(
                "MyTable.id",
        ),
));

$query = "select count(*) as cnt from ({$subQuery}) as tmp";
$result = $this->MyTable->query($query);

以上です。