尝试此查询。此查询将记录作为数组返回。

$connection = \Yii::$app->db;
$model = $connection->createCommand('
                                  select t.id,
                                    t.ad_id,
                                    sum(t.impression) total_impression,
                                    sum(t.view) total_views,
                                    sum(t.clicks) total_clicks,
                                    t.publisher_id,
                                    i.budget,
                                    i.name_of_campaign
                                  from
                                    (select id,
                                       ad_id,
                                       max(impression)  impression,
                                       max(view)  view,
                                       max(clicks)  clicks,
                                       visitor_ip,
                                       publisher_id
                                    from ad_analytics
                                    group by ad_id, visitor_ip) t
                                    inner join inventory i
                                               on i.id = t.ad_id
                                  group by t.ad_id;
                                ');
$users = $model->queryAll();

这个任务是一次性的，听起来像是“只从旧数据库中获取那些订单，新数据库中提供了有关这些订单的信息。”

$limit = 1000;

    if( $type = 'viaRawSQL' ) {

        $sql = <<<SQL

    SELECT * FROM db1.orders
    LEFT JOIN db2.orders ON db1.orders.order_id = db2.orders.order_id
    WHERE db2.orders.order_id IS NOT NULL
    LIMIT $limit

    SQL;

        $activeQuery = new ActiveQuery( OrderOld::class );

        $activeQuery->sql = $sql;

    }
    else if( $type == 'viaModel' ) {

        $activeQuery = OrderNew::find()
                               ->where( [
                                   'vat' => null,
                               ] )
                               ->andWhere( [
                                   'not', [ 'invoice_number' => null, ],
                               ] )
                               ->limit( $limit )
        ;
    }

    $dataProvider = new ActiveDataProvider( [
        'query' => $activeQuery,
        'sort'  => [ 'defaultOrder' => [ 'order_id' => SORT_DESC ] ],
    ] );

    // OR get all models
    $index = $activeQuery->all();

如果您编写的rawsql查询使用phpmyadmin或任何其他实用程序在表上运行时得到所需的结果，则可以将其转换为以下内容。

$subQuery = Adanalytics::find()
        ->select([new Expression('[[id]], [[ad_id]], MAX([[impression]]) as impression, max([[view]]) as view, max([[clicks]]) as clicks,[[visitor_ip]],[[publisher_id]]')])
        ->where(['publisher_id' =>  Yii::$app->user->identity->id ])->
        ->groupBy('[[id]], [[visitor_ip]]');

$query = Adanalytics::find()
        ->select([new \yii\db\Expression('t.[[date_event]], t.[[id]], t.[[ad_id]], sum(t.[[impression]]) as total_impression, sum(t.[[view]]) 
         as total_views, sum(t.[[clicks]]) as total_clicks, t.[[publisher_id]], i.[[budget]],i.[[name_of_campaign]]')])
        ->from(['t' => $subQuery])
        ->innerJoin('{{%inventory}} i', 'i.id=t.ad_id')
        ->groupBy('t.ad_id, t.date_event');
$query->all()

只需确保用于连接其他表的外键应该在select中指定，否则可能会出现类似columnnotfound/或unknown列的错误。

如果您有一个有效的sql查询，您可以考虑使用 SqlDataProvider 而不是重写整个查询：

$sql = <<<'SQL'
from
  (select id,
     ad_id,
     max(impression)  impression,
     max(view)  view,
     max(clicks)  clicks,
     visitor_ip,
     publisher_id
  from ad_analytics
  group by ad_id, visitor_ip) t
  inner join inventory i
             on i.id = t.ad_id
group by t.ad_id;
SQL;

$selectSql = <<<SQL
select t.id,
  t.ad_id,
  sum(t.impression) total_impression,
  sum(t.view) total_views,
  sum(t.clicks) total_clicks,
  t.publisher_id,
  i.budget,
  i.name_of_campaign
$sql
SQL;

$countSql = 'select count(*) ' . $sql;

$dataProvider = new SqlDataProvider([
    'sql' => $selectSql,
    'totalCount' => Yii::$app->db->createCommand($countSql)->queryScalar(),
    'pagination' => [
        'pageSize' => 20,
    ],
]);