Converting a query of join operation

I want to convert this following query into Yii query the SQL is as follows.


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;

What I am trying is as follows.


//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 dont know what to write down here";

How can i solve this problem?

I got the working answer here.

https://stackoverflow.com/questions/50215060/converting-a-join-table-query-into-yii2-query