Yii2 subquery GROUP BY in active record

Question

can I convert this query "SELECT * FROM (SELECT * FROM blog_post ORDER BY data DESC) blog_post GROUP BY blog_id LIMIT 2"

into a Yii2 active record query?

Thx Ms

Answer 1

Yes, you can do this. Yii2 gave us a wonderful library support.

You can form custom sql query and pass this query in findBySql() like:

$sql = "Some query/nested query";
$result = ModelClass::findBySql($sql);

Visit Yii official documentation.

Answer 2

BlogPost::find() ->orderBy('data DESC') ->groupBy('blog_id') ->limit(2) ->all();

Answer 3

I suppose you can do in this way :

Ⅰ:create a subQuery where you select from.

$blogPostQuery = BlogPostModel::find()->orderBy(['data' => SORT_DESC]);

Ⅱ:Get activeRecord results. The from params is an exist Query.

$models = (new yii\db\Query)
->from(['blog_post ' => $blogPostQuery])
->groupBy(['blog_id'])
->limit(2)
->all();

ps: see yii\db\query->from()-detail in Yii Api;

(public $this from ( $tables )) $tables can be either a string (e.g. 'user') or an array (e.g. ['user', 'profile']) specifying one or several table names··· or a sub-query or DB expression

Have a try!I hope it`s useful for you:))