How to display only the last 10 records from an activerecord link?

Question

MySQL

Hello.

I can't solve the next question. There is a model with a connection. It is necessary to display in the widget only the last 10 records from the link. I so understood that limit in communications does not work.

Example:
1. Categories of news, 5 items
2. News as a link to a category (only the last 10 news sorted by ID are needed)
2.1. Comments to the news in the form of a link to the news. Registered in the news model.
2.2. Pictures of the news in the form of a link to the news. Registered in the news model.

When displaying categories, it is necessary to display only the last 10 news from the link.

I do it like this in the model:

In the widget:

$models = Yii::$app->db->cache(function (Connection $db) {
    return Categories::find()
        ->alias('cat')
        ->select('cat.id, cat.name, cat.alias, cat.status')
        ->with('news')
        ->where(['cat.id' => [1,2,3,4,5])
        ->andWhere('cat.status = :cat_status', ['cat_status' => 1])
        ->orderBy([
                new Expression(sprintf("FIELD(cat.id, %s)", implode(",", [1,2,3,4,5])))
        ])
        ->all();
    });

In the category model, I specify the relationship like this:

public function getNews()
    {
        return $this->hasMany(News::className(), ['category_id' => 'id'])
                ->alias('new')
                ->select('new.*')
                ->with(['comments, 'photos',])
                ->where('new.status != :new_status', ['new_status' => 9])
                ->orderBy(['new.id' => SORT_DESC])
                ->limit(10);
    }

In view I display it all like this:

<?php foreach($model_category->news as $key => $item) : ?>
   // Выводим новости категории и связанные данные для них
<?php endforeach; ?>

This works, but the limit 10 parameter does not work. it acts on the total amount of news that is displayed, and not for each category as we would like. As a result, we get that in the first category there are 3 news, in the second 5 and so on.

If I add one more function to the category model in which I specify limit(10), then everything starts working as it should, but there are 35-55 more requests. the function pulls this on every foreach pass.

public function getNewsCategory($items = 10)
    {
        return $this->getNews()->limit($items)->all();
    }

Thanks in advance for your replies and advice.

Answer 1

$sql = 'Select ........';
$model = YourModel::findBySql($sql)->all();

Through relation normally will not leave.

Answer 2

I think that you need to split the query like this:
1. Select category id.
2. Select news for each category, with links to comments and pictures.
And the fact that it turns out 35-55 requests is not a lot, you need to look at what they are.
See what data format is obtained when executing the query:

News::find()->where(['category_id' => $id])->with('comments')->with('photos')->asArray()->limit(10)->all()