sql - How to use subquery in the join function of Yii framework 2 ActiveRecord? -

below pure sql query.

select  a.*, b.*  inner join b  on a.id = b.a_id inner join (     select a_id, max(add_time) max_add_time      b      group a_id ) m  on b.a_id = m.a_id , b.add_time = m.max_add_time  order b.add_time desc 

i have subquery in second inner join. below active query.

$subquery = b::find()->select(['a_id', 'max(add_time) max_add_time'])->groupby('a_id');  $query = a::find()->innerjoin('b', 'a.id = b.a_id')                   ->innerjoin('(' .                        $subquery->prepare(yii::$app->db->querybuilder)                                ->createcommand()                                ->rawsql                   . ') m', 'b.a_id = m.a_id , a.add_time = m.max_add_time ')                   ->orderby('b.add_time desc'); 

it works fine, not way use subquery in second inner join. want approach query select left table inner join right table, group a_id , order add_time (desc) of right table. how should better use subquery in second inner join?

the snippet below untested should that. if read docs (at http://www.yiiframework.com/doc-2.0/yii-db-query.html#innerjoin()-detail) can see array subquery valid input, key being alias.

$subquery = b::find()     ->select(['a_id', 'max(add_time) max_add_time'])     ->groupby('a_id');  $query = a::find()     ->innerjoin('b', 'a.id = b.a_id')     ->innerjoin(['m' => $subquery], 'b.a_id = m.a_id , a.add_time = m.max_add_time')     ->orderby('b.add_time desc');