I want to search data from 2 table (with same field: email, phone, …)
here is my code in search model:
// .... some code before
$columns = ['id', 'name', 'status']; // columns example in 2 tables
$query = new Query;
$query->andFilterWhere( [ 'email' => $this->email, 'phone' => $this->phone ] )
->andFilterWhere(.......); // .... and more conditional
$q = clone $query; // I don't want to rewrite **where** conditional
$query->select(ArrayHelper::merge($columns, ['"tableAAAAA" as `table`']))
->from(ModelAAAAA::tableName());
$q->select(ArrayHelper::merge($columns, ['"tableBBBBB" as `table`']))
->from(ModelBBBBB::tableName());
$query->union($q, true); // I need all records
$dataProvider = new ActiveDataProvider([
'query' => $query,
]);
return $dataProvider; // for GridView
By default, Yii will limit 20 records, when I see in debug, SQL will look like:
(SELECT `id`, `name`, `status`, "tableAAAAA" as `table`
FROM `tbl_tableAAAAA`
WHERE (`email`='abcd@gmail.com') AND (`phone`='0123')
LIMIT 20)
UNION ALL
(SELECT `id`, `name`, `status`, "tableBBBBB" as `table`
FROM `tbl_tableAAAAA`
WHERE (`email`='abcd@gmail.com') AND (`phone`='0123')
)
=> Yii will get all records from tableBBBBBB
but I want SQL look like:
(SELECT `id`, `name`, `status`, "tableAAAAA" as `table`
FROM `tbl_tableAAAAA`
WHERE (`email`='abcd@gmail.com') AND (`phone`='0123')
)
UNION ALL
(SELECT `id`, `name`, `status`, "tableBBBBB" as `table`
FROM `tbl_tableAAAAA`
WHERE (`email`='abcd@gmail.com') AND (`phone`='0123')
)
LIMIT 20
Currently, my solution is:
$q->limit = &$query->limit;
so SQL will be look like:
(SELECT `id`, `name`, `status`, "tableAAAAA" as `table`
FROM `tbl_tableAAAAA`
WHERE (`email`='abcd@gmail.com') AND (`phone`='0123')
LIMIT 20)
UNION ALL
(SELECT `id`, `name`, `status`, "tableBBBBB" as `table`
FROM `tbl_tableAAAAA`
WHERE (`email`='abcd@gmail.com') AND (`phone`='0123')
LIMIT 20)
Do you have better idea?
Thanks,