Yii2: How to cache queries made by ActiveRecord relations

Question

I have news table and its related news_comment table. I have defined relation newsComment with news_comment table.

If I execute this query:

$result = News::getDb()->cache(function () use($id) {
    return News::find()->with('newsComment')->where(['news.id' => $id])->one();
});

Only query that is fetching data from news table will be cached. Query that is selecting from related table is not.

Is it possible to cache both main query and queries executed to retrieve data from related tables together, without having to write them separately ?

Basrelief

Have You tried to use joinWith? yiiframework.com/doc-2.0/… – Complete

@PatrykRadziszewski yes, and it doesn't make any difference. – Basrelief

This should work (already tried few weeks ago), tell us how did you check that the same query is executed 2 times ? – Decorum

Answer 1

Try This:

$db = News::getDb();
$result = $db->cache(function ($db) use ($id) {
  $query = new \yii\db\Query;
  $query->select("news.*,newsComment.*") // write table name for newsComment model and also in join
        ->from('news')
        ->leftjoin('newsComment','newsComment.id=news.product_id')
        ->where(['news.id' => $id])
        ->one();

  $command = $query->createCommand();
  $result  = $command->queryAll();

  return $result;

});

Claudine

Answer 2

Try to add $db or Yii::$db as a param:

$result = News::getDb()->cache(function ($db) {
    return News::find()->with('newsComment')->where(['news.id' => $id])->one();
});

Or add the cache in the query itself:

$result = News::find()->with('newsComment')->where(['news.id' => $id])->cache(60)->one(); 

Roussel