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

Question

Below is my pure SQL query.

SELECT  a.*, b.*
FROM a 
INNER JOIN b 
ON a.id = b.a_id
INNER JOIN (
    SELECT a_id, MAX(add_time) AS max_add_time 
    FROM b 
    GROUP BY a_id
) m 
ON b.a_id = m.a_id AND b.add_time = m.max_add_time 
ORDER BY b.add_time DESC

I have the subquery in the second INNER JOIN. Below my active query.

$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('(' . 
                      $subQuery->prepare(Yii::$app->db->queryBuilder)
                               ->createCommand()
                               ->rawSql
                  . ') m', 'b.a_id = m.a_id AND a.add_time = m.max_add_time ')
                  ->orderBy('b.add_time DESC');

It works fine, but I do not like the way I use the subquery in the second INNER JOIN. What I want to approach with this query is to select the left table inner join with right table, group by a_id and order by the add_time (DESC) of the right table. How should I better use the subquery in the second INNER JOIN?

Answer 1

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');

Answer 2

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;