Using Yii2 querybuilder with inner join on the same table

Question

I have a table with lots of listings with a date column.

Usually I show all listings within a date rage using active record like this:

return $this->hasMany(Ranking::className(), ['project_hash' => 'hash'])
        ->where(['>', 'date', date('Y-m-d', strtotime("-8 weeks"))]);

The output is then a table with all the listings sorted by date, which is once a week as the app collects data.

I need to have an alternate view which shows the output by month, for which I have a working sql query, but I can't get it to work in Yii2. I am using the querybuilder as this seems the right route to build a more complex query.

The raw sql is:

SELECT r.*
FROM ranking AS r
JOIN (
    SELECT MAX(t.date) AS date
    FROM ranking AS t
    GROUP BY YEAR(t.date), MONTH(t.date)
    ) AS x USING (date)
where project_hash = 'TFQDaZ4CZpqvJtvF';

So far I have this code with the querybuilder but it outputs all items

$subQuery = (new \yii\db\Query())
->select(['MAX(t.date) AS date'])
->from('ranking AS t')
->groupBy(['YEAR(t.date)', 'MONTH(t.date)']);


$rows = (new \yii\db\Query())
->select(['r.*'])
->from('ranking AS r')
->innerJoin(['x' => $subQuery])
->where(['project_hash' => 'TFQDaZ4CZpqvJtvF'])
->all();
foreach ($rows as $rank) {
  echo 'key'.$rank['keyword'].' date:'.$rank['date'].'<br>';
};

The sql generated is:

SELECT `r`.* 
FROM `ranking` `r` 
INNER JOIN (
    SELECT MAX(t.date) AS date 
    FROM `ranking` `t` 
    GROUP BY YEAR(t.date), MONTH(t.date)) `x` 
WHERE `project_hash`='TFQDaZ4CZpqvJtvF'

Is it possible to create this query with the querybuilder? I have tried different methods that mostly result in errors, and I can't seem to get the 'AS''s in the right place or get the USING clause in there at all.

Any help is appreciated!

Double H Double H · Accepted Answer · 2015-12-04T13:22:46

Try this One

$subQuery = (new \yii\db\Query())
        ->select(['MAX(t.date) AS date'])
        ->from('ranking AS t')
        ->groupBy(['YEAR(t.date)', 'MONTH(t.date)']);


    $rows = (new \yii\db\Query())
        ->select(['r.*'])
        ->from('ranking AS r')
        ->innerJoin(['x USING (date)' => $subQuery])
        ->where(['project_hash' => 'TFQDaZ4CZpqvJtvF'])
        ->createCommand()->rawSql;

It will create Query Like

SELECT `r`.* FROM `ranking` `r` 
INNER JOIN (
 SELECT MAX(t.date) AS date 
 FROM `ranking` `t` 
 GROUP BY YEAR(t.date), MONTH(t.date)
) x USING (date) 
WHERE `project_hash`='TFQDaZ4CZpqvJtvF'

Using Yii2 querybuilder with inner join on the same table

1 Answers