Yii2:将原始查询转换为 ActiveRecord [英] Yii2 : Converting a raw query into ActiveRecord

问题描述

我有一个这样的查询.

选择t.id，t.ad_id,总和(t.impression)total_impression，sum(t.view) total_views,sum(t.clicks) total_clicks,t.publisher_id,i. 预算，i.name_of_campaign从(选择身份证，广告编号，最大(印象)印象，最大(视图)视图，最大(点击)点击次数，访客IP，发布者_id来自 ad_analytics按 ad_id、visitor_ip 分组) t内连接库存 i在 i.id = t.ad_id按 t.ad_id 分组；

我想为 yii2 转换相同的内容.我写的查询是.

//对于模型使用前端\模型\库存；使用前端\模型\Adanalytics；

对于同一模型中的网格视图.

$ivcsubquery = Adanalytics::find()->select('id,ad_id,date_event,max(cpc) cpclick,max(cpv) cpview,max(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 = "我真的不知道在这里写什么";

在我的网格视图中，它看起来像这样.

 'columns' =>[['类' =>'yii\grid\SerialColumn'],//'ID'，'用户身份'，'放置日期','name_of_campaign','预算'，//'开始日期'，//'平台类型','titleOfTheVideo','视频网址','结束日期',]

也以上述方式调用了列，我该如何解决这个问题?

解决方案

如果您编写的 RawSql 查询在使用 phpmyadmin 或任何其他实用程序在表上运行时为您提供了所需的结果，您可以将其转换为以下内容.

>

$subQuery = Adanalytics::find()->select([new Expression('[[id]], [[ad_id]], MAX([[impression]]) 作为展示，max([[view]]) 作为视图，max([[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]]) astotal_impression, sum(t.[[view]])作为 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()

只要确保你用来连接其他表的外键应该在选择中指定，否则它可能会给你一个错误，如未找到列/或未知列.

I have a query which goes into this way.

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;

I want to convert the same for yii2. The query which I have written is.

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

And for grid view in the same model.

$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";

And in my grid view which looks like this.

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

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

Also columns are called in at the above way How can i solve this issue?

解决方案

If the RawSql query you wrote is giving you the desired results if run on the table using phpmyadmin or any other utility you can transform it into the following.

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

Just make sure that the foreign keys that you use to join the other tables should be specified in the select otherwise it may give you an error like column not found / or unknown column.

这篇关于Yii2:将原始查询转换为 ActiveRecord的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！