Yii2 How create ActiveDataProvider with union from different models

Question

I'm trying to create union from two different models the two models have same column id, body below the error when send dataProvider to GridView:

SQLSTATE[07002]: [Microsoft][ODBC Driver 11 for SQL Server]COUNT field incorrect or syntax error The SQL being executed was: SELECT COUNT(*) FROM ((SELECT [id], [body] FROM [web_article] WHERE body LIKE '%a%') UNION ( SELECT [id], [body] FROM [email_article] WHERE body LIKE '%a%' )) [c]

I copied the sql query then run it on MSSQL Server and it return result!

Below the controller code:

$model = new DynamicModel([
    'text', 'type'
]);

$model->addRule(['text'], 'required')
      ->addRule('text', 'string');

$emailArticle = EmailArticle::find()->select('id, body')->where('body LIKE :query')->addParams([':query'=>"%{$model->text}%"]);
$webArticle = WebArticle::find()->select('id, body')->where('body LIKE :query')->addParams([':query'=>"%{$model->text}%"]);
$webArticle->union($emailArticle);

$dataProvider = new ActiveDataProvider([
    'query' => $webArticle,
    'pagination' => [
        'pageSize' => 21,
    ],          
]);

return $this->render('form', [
    'model'=>$model,
    'dataProvider' => $dataProvider,
]);

Answer 1

I use SqlDataProvider instead of ActiveDataProvider below the final code:

$sql = "(SELECT [id], [body] FROM [web_article] WHERE body LIKE '%{$model->text}%' UNION SELECT [id], [body] FROM [email_article] WHERE body LIKE '%{$model->text}%')";
$totalCount = \Yii::$app->db->createCommand("SELECT COUNT(*) FROM $sql AS [c]")->queryScalar();
$dataProvider = new SqlDataProvider([
    'sql' => $sql . ' ORDER BY body',
    'totalCount' => (int)$totalCount,
    'pagination' => [
        'pageSize' => 20,
    ],          
]);