yii 2:将原始查询转换为ActiveRecord?

内容来源于 Stack Overflow,并遵循CC BY-SA 3.0许可协议进行翻译与使用

  • 回答 (2)
  • 关注 (0)
  • 查看 (109)
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;
//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',
   ]

此外,按上述方式调用列,我如何解决这个问题?

提问于
用户回答回答于

如果你编写的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()
用户回答回答于

你可以考虑使用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,
    ],
]);

所属标签

可能回答问题的人

  • 不吃貓的鱼oo

    5 粉丝466 提问6 回答
  • 人生的旅途

    10 粉丝484 提问5 回答
  • Richel

    8 粉丝0 提问4 回答
  • 御姐万岁

    6 粉丝507 提问3 回答

扫码关注云+社区

领取腾讯云代金券

年度创作总结 领取年终奖励