Yii2 :将原始查询转换为ActiveRecord
Yii2 :将原始查询转换为ActiveRecord
提问于 2018-05-07 21:10:03
我有一个这样的查询。
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
Stack Overflow用户
回答已采纳
发布于 2018-05-08 00:51:54
如果您编写的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中指定，否则它可能会给出一个错误，如column not found /或unknown column。
Stack Overflow用户
发布于 2018-05-07 21:24:51
尝试此查询。此查询以数组形式返回记录。
$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();Stack Overflow用户
发布于 2018-05-08 03:47:55
如果你有一个工作的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,
],
]);页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/50215060
复制相关文章
相似问题