时间戳

首页 > 解决方案 > Yii2 : 将原始查询转换为 ActiveRecord

php - Yii2 : 将原始查询转换为 ActiveRecord

问题描述

我有一个这样的查询。

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',
   ]

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

标签： phpmysqlyii2

解决方案

试试这个查询。此查询以数组形式返回记录。

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

推荐阅读