代码之家 › 专栏 › 技术社区 › arun webber

Yii2:将原始查询转换为ActiveRecord

yii2 mysql php

arun webber · 技术社区 · 7 年前

我有一个这样的疑问。

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;

我想将其转换为yii2。我写的查询是。

//For model
use frontend\models\Inventory;
use frontend\models\Adanalytics;

对于同一模型中的栅格视图。

$ivcsubquery = Adanalytics::find()->
                  select('id,ad_id,date_event,max(cpc) cpclick,max(cpv) cpview,max(impression) impression,max(view) view,max(clicks) clicks,visitor_ip,publisher_id')->
                  from('ad_analytics')->
                  where(['publisher_id' =>  Yii::$app->user->identity->id ])->
                  groupBy('ad_id,date_event,visitor_ip');
        $ivcquery=Adanalytics::find()->
                // $subquery = select('id,ad_id,max(impression) impression,max(view) view,max(clicks) clicks,visitor_ip,publisher_id')->
                //     from('ad_analytics'),
              //where(['publisher_id' =>  Yii::$app->user->identity->id ])->
              select('t.ad_id,t.date_event,sum(t.cpclick) total_click_cost,sum(t.cpview) total_view_cost,sum(t.impression) total_impression,sum(t.view) total_views,sum(t.clicks) total_clicks,t.publisher_id')->
              from(['t'=>$ivcsubquery])->
              groupBy('t.ad_id,t.date_event');



      $query = Inventory::find()->
      where(['publisher_name' =>  Yii::$app->user->identity->id ])->
      andWhere('ending_date>= NOW()')->
      andWhere('paid=1')->
      andWhere('status=1');

      $joinquery = "Really I don't know what to write down here";

在我的网格视图中,如下所示。

  'columns' => [
            ['class' => 'yii\grid\SerialColumn'],

            //'id',
            'user_id',
            'placed_date',
            'name_of_campaign',
            'budget',
            //'start_date',
            //'platformType',
            'titleOfTheVideo',
            'videoUrl',
            'ending_date',
   ]

此外,以上述方式调用列如何解决此问题?

4 回复 | 直到 6 年前

Muhammad Omer Aslam 7 年前

如果您编写的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中指定用于连接其他表的外键,否则可能会出现列未找到/或未知列之类的错误。

Peerbits - Backend Team 7 年前

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

$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();

rob006 7 年前

如果您有一个有效的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,
    ],
]);

-1

Ivan Kryazhev 6 年前

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

    $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();