Limit and Union in Query (SearchModel)

[vantuan113]

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,

[samdark]

Please use forums at http://www.yiiframework.com/forum/index.php/forum/42-general-discussions-for-yii-20/ to ask questions and discuss things. Issue tracker is for issues. Thanks.