How to use subquery in the join function of Yii framework 2 ActiveRecord?
The snippet below is untested but it should be something like that. If you read the docs (at http://www.yiiframework.com/doc-2.0/yii-db-query.html#innerJoin()-detail) you can see an array with a subquery is also valid input, with the key being the alias.
$subQuery = B::find() ->select(['a_id', 'MAX(add_time) AS 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 AND a.add_time = m.max_add_time') ->orderBy('b.add_time DESC');
Having created the join, if you need to use any of the columns returned by the subQuery, you need to add properties to the Yii2 model class, e.g.
$subQuery = FinancialTransaction::find() ->select( new \yii\db\Expression( 'SUM(amount) as owing') ) ->addSelect('booking_id') ->groupBy('booking_id');$query = $query ->addSelect(['b.*', 'owing']) ->leftJoin(['ft' => $subQuery], 'b.booking_display_id = ft.booking_id');To access "owing" the model has to have the property (PHPdoc optional):
/** * @var float */public $owing=0;