こんにちは SGK です。
今回は CakePHP の クエリービルダーで UNION する方法と、
それをサポートする from メソッドについてのテクニックを紹介したいと思います。
 

 
UNION は SELECT の結果を結合する場合に使います。
別のテーブルデータ同士をくっつける感じです。
 
例として２種類の商品が別のテーブルに分けて保存されている、a, b テーブルがあったとします。
これを union で結合するには以下のようなSQLになります。
 

SELECT * FROM a
UNION ALL
SELECT * FROM b

ただしこれだと a と b で異なる列がある場合にエラーになってしまうのと、どちらのテーブルのデータか判別できないので、
列指定と、テーブル名を列として加えた以下のようなSQLを発行することになります。
※加えて有効なデータのみを抽出するような条件文「active = true」を加えました。
 

SELECT id, name, 'a' as tbl FROM a WHERE active = true
UNION ALL
SELECT id, name, 'b' as tbl FROM b WHERE active = true

これをCakePHPで書くと以下のようになります。
 

$a = \Cake\ORM\TableRegistry::getTableLocator()->get( 'a' );
$b = \Cake\ORM\TableRegistry::getTableLocator()->get( 'b' );

$queryA = $a->find()->select(['id', 'name', 'tbl'=>"'a'"])->where(['a.active'=>true]);
$queryB = $b->find()->select(['id', 'name', 'tbl'=>"'b'"])->where(['b.active'=>true]);

$queryA->unionAll( $queryB );

※「UNION ALL」ではなく「UNION」で結合する場合は union メソッドを使います。
 

 
さてここからです。
このクエリを拡張し、a, b 両方に共通してある外部キー status_id から statuses テーブルを参照して
ステータスデータを取得したい場合、直感的には以下のようにしたいと思うのですが、、、
 

$queryA->unionAll( $queryB );
$queryA->contain( 'Statuses' );

これを実行した場合のSQLを見ると以下のようになります。
 

SELECT id, name, 'a' as tbl FROM a WHERE active = true
LEFT JOIN statuses ON statuses.id = a.status_id
UNION ALL
SELECT id, name, 'b' as tbl FROM b WHERE active = true

う～ん、そうですよね、、Cake は悪くない、間違っていない。。
なので以下のように書いてあげればステータスデータを取得できるんですが、、、
 

$queryA = $a->find()->select(['id', 'a.name', 'tbl'=>"'a'", 'Statuses.name'])
	->contain( 'Statuses' )->where(['a.active'=>true]);
$queryB = $b->find()->select(['id', 'b.name', 'tbl'=>"'b'", 'Statuses.name'])
	->contain( 'Statuses' )->where(['b.active'=>true]);

$queryA->unionAll( $queryB );

結合した後にまとめて操作したいと思うのが ORM な動作だと思うのです。。
その思いで実行して欲しいクエリは以下のようなSQLになります。
 

SELECT * FROM (
	SELECT id, name, status_id, 'a' as tbl FROM a WHERE active = true
	UNION ALL
	SELECT id, name, status_id, 'b' as tbl FROM b WHERE active = true
) tmp
LEFT JOIN statuses on tmp.status_id = statuses.id

このようにしたい場合どうすれば良いかというとクエリーメソッドの from を使い以下のように書きます。
 

$a = \Cake\ORM\TableRegistry::getTableLocator()->get( 'a' );
$b = \Cake\ORM\TableRegistry::getTableLocator()->get( 'b' );

//注1
$queryA = $a->find()
	->select(['id'=>'id', 'name'=>'name', 'status_id'=>'status_id', 'tbl'=>"'a'"])
	->where(['a.active = :active']);

$queryB = $b->find()
	->select(['id'=>'id', 'name'=>'name', 'status_id'=>'status_id', 'tbl'=>"'b'"])
	->where(['b.active = :active']);

$queryA->unionAll( $queryB );

//注2
$queryUnion = $a->find()->from( '(' . $queryA->sql() . ') AS a' );

//注3
$queryUnion->select(['id', 'a.name', 'tbl', 'Statuses.name'])->bind( ':active', true );

$queryUnion->contain( 'Statuses' );

from の他にいろいろハックを追加したのでその部分に「注」を置いておきました。
詳しい説明は以下の通りです。
 
– 注1
select で「’id’=>’id’」のように同じフィールド名を繰り返しているのは余計な別名を付けないようにするためで、
これを行わないと「id as a__id」のようにSQL化され union に失敗します。
 
where で「:active」としているのは from を介すると where でセットした
値のバインド（置き換え）情報がクリアされるため、任意にバインドを行うための対策です。
※「’a.active’=>true」とすると中間SQLでは「a.active = :0」となっており、
実際にクエリを実行する際にバインドが実行され「a.active = true」というSQLになります。
しかし from メソッドで渡している「$queryA->sql()」は中間SQLしか得られないため、
このように任意にバインドできるパラメータをセットしておくことになります。
 
– 注2
from の部分です。 union したクエリから sql を作成し、aテーブルから派生したクエリに偽装するため「AS a」を付加しています。
 
– 注3
fromにセットしたSQLで指定した列と元のaテーブルの列が異なるため select は指定しないとエラーになります。
「注1」の where でセットしたバインドパラメータ「:active」に対して値をバインドします。
 
– ちなみにこのクエリ $queryUnion から生成される Entity は Aテーブルの Entity になります。
 

 
ややこしい。やっぱり、う～ん、ですね。。
できればこの煩雑さをライブラリ（CakePHP）で吸収してくれないかなと思う今日この頃です。
 
こんなことするぐらいなら union した select文 で view テーブルを作る方が正しい選択かもしれません。
 
ちなみに from メソッドはサブクエリを発行する場合にも有効です。
しかし、union と同様に「任意のバインド」や、「select による列指定」が必要なケースがでてきます。
 
以上 CakePHP の union と from メソッドについてでした。