How to properly build a query to get data in Yii2?

Question

SQL

There are two tables - post and category.
There is a OneToMany relationship [category.id -> post.category_id], that is, several posts can have one category, and one category can have several posts.

I need to get 4 categories and 4 posts related to that category. Posts are sorted in descending order by the number of views (post.views). Categories are sorted in descending order by the amount of views of posts in this category.

I have no idea how to do it. All I have:

$categories = Category::find()
            ->joinWith('posts p')
            ->where(['=', 'p.status', Post::STATUS_PUBLISH])
            ->limit(4)
            ->all();

Communication code:

public function getPosts()
    {
        return $this->hasMany(Post::class, ['category_id' => 'id']);
    }

Help me please!

Answer 1

The following should help, but with the condition of denormalizing the database. That is, the sum of post views is stored both in the category and in the post.
https://www.yiiframework.com/doc/guide/2.0/en/db-a...

$categories = Category::find()
->with([
    'posts' => function ($query) {
        $query->limit(4)->orderBy(['views' => SORT_DESC]);
    },
])
->orderBy(['views' => SORT_DESC])
->all();

UPD:
Draft version without denormalization.
Solution:
- categories are sorted using JOIN to posts, SUM and grouping by categories.id
- posts are obtained by a separate query via Yii links

$categories = Category::find()
    ->joinWith('posts')
    ->select('*', 'SUM(posts.views) as category_posts_views')
    ->with([
               'posts' => function ($query) {
                   $query->limit(4)->orderBy(['views' => SORT_DESC]);
               },
           ])
    ->groupBy('categories.id')
    ->orderBy(['category_posts_views' => SORT_DESC])
    ->all();