Best way to cache the database record for frequent usage

Yii 2.0 General Discussions
1

What is the best way to cache the database query result to use that in future calls?

For example:

// Calling this method before every page
// ID = Logged in user ID
$model::getUserDetails($id); 

public static function getUserDetail($userid)
{
	if (($model = UserDetails::find()->where(['user_id' => $userid])->one()) !== null) {
		return $model;
	}
	return [];
}

How can I get the below results:

  1. Data should be cached on the first request.
  2. On the second or Nth request, the data should return from cached data instead of real DB
  3. If primary records change in the database, it should also update the cache records
2
3

I do use cache (REDIS) but don’t know how can I use it for data cache. Any instructions would be good.

I followed ActiveRecord from Yii2-Redis but got blank value when fetching with find() method.

So any example or explanation would be helpful.

4 
public static function getUserDetail($userid)
{
        $model = UserDetails::find()
            ->cache()
            ->where(['user_id' => $userid])
            ->one()
       //.....
}

Assuming you have cache component set to redis
See for details the API docs: ActiveQuery, yii\db\ActiveQuery | API Documentation for Yii 2.0 | Yii PHP Framework

1 Like
5

Thanks for the recommendation. It seems to be working great.

However, I need help understanding how I can invalidate the cache after updating the row. I have implemented the below code, but it seems it is not working.

Controller

I query user data:

public static function getUserDetail($user_id)
{
        $dependency = new TagDependency(['tags' => UserDetails::getDependencyTagName($user_id)]);
        $model = UserDetails::find()
            ->cache(3600, $dependency)
            ->where(['user_id' => $user_id])
            ->one()
       //.....
}

Model

calling afterSave method and trying to invalidate the cache with TagDependency

// Dynamic Tag Name for each user:
public static function getDependencyTagName($user_id)
{
	return md5($user_id.'_'.self::tableName()); // table name: user-details
}

// Invalidate cache once user perform update
public function afterSave($insert, $changedAttributes)
{
	parent::afterSave($insert, $changedAttributes);
	TagDependency::invalidate(Yii::$app->cache, [$this::getDependencyTagName($this->user_id)]);
}

Main.php

use yii\redis\Cache;
use yii\redis\Connection;
use yii\redis\Session;

'components' => [
    'redis' => [
		'class' => Connection::class,
		'hostname' => $params['REDIS_HOST'],
		'port' => 6379,
		'database' => 0,
	],
    'session' => [
		'name' => 'advanced-backend',
		'class' => Session::class,
		'keyPrefix' => "back_session_"
	],
    'cache' => [
		'class' => Cache::class,
		'keyPrefix' => "back_cache_" 
	],
]

Please let me know if you have any other way to invalidate the cache after updating the row.

I appreciate any help you can provide.

6

I believe the answer lies here: [solved]Clear query cache by key - #2 by Pasman

7

Thanks,

As you can see, I do the same procedure but still do not get fresh data after saving the records.

Any way to debug the cache info?

8

I would try with simple hardcoded name like the example above and make sure that it works before start dealing with static functions, et al

1 Like
9

Thanks for the information.

However, the issue was prior cache without a dependency tag.

I was working to optimise the code for better performance and reduce the number of calls to the database.

I cached data by calling ->cache(3600) in $query without any dependency injection.

Then I realised performance could improve further by validating the previous cache and clearing it in some conditions; I thought of adding dependency injection.

So even though I have added the dependency, the ActiveQuery was still loaded from the cache.

After 1 hour, when the cache automatically expired, the ActiveQuery with cache dependency was executed, and everything worked as expected.

TIME IS SOLUTION in this case.

But just curious to know if YII2 allows seeing what query is cached and what data is cached by dependency tag,

Is there any extension or code available to do that?

10

My whole point was, you might have something setup wrong, hence my suggestion of doing hardcoded stuffs like in the link. Just to confirm that your setup is correct. Currently I have no time to try it myself, but I will try it when I find time. Meanwhile try that suggestion, of using exact setup on the link and see if it works!

11

Thanks for your response.

I already resolved it with the same approach; the only problem was Cache without dependency doesn’t invalidate itself if we call the same query with dependency injection.

Step 1: Call Query Without Dependency

First, get the response from Cache () without dependency:

$model = UserDetails::find()->cache('3600')->where(['user_id' => 1])->one();

// To make it easy for understanding Let's assume CACHE_ID = ABCD

/* CACHE_ID with ABCD return below response */
[
    'id' => 1,
    'name' => 'John Doe',
    'number' => '1800111222'
]

Step 2: Add dependency

Now add the dependency in the query and invalidate it in the afterSave function:

$dependency = new TagDependency(['tags' => 'user_cache']);
$model = UserDetails::find()->cache('3600', $dependency)->where(['user_id' => 1])->one();

// Let's assume for this dependency cache the CACHE_ID = PQRS

// In Model file
// Invalidate cache once user perform update
public function afterSave($insert, $changedAttributes)
{
	parent::afterSave($insert, $changedAttributes);
	TagDependency::invalidate(Yii::$app->cache, 'user_cache');
}

Step 3: Update Data

Now made update in the name: Changed to “Harry Porter”,
As we have added invalidate tag in the AFTER SAVE method, it will invalidate the “ID = PQRS” Cache. It is working perfectly fine.

$dependency = new TagDependency(['tags' => 'user_cache']);
$model = UserDetails::find()->cache('3600', $dependency)->where(['user_id' => 1])->one();

//Cache with the value PQRS is invalidated.
// But the query still returns the value from the ABCD cache.
[
    'id' => 1,
    'name' => 'John Doe', // It should be Harry Porter
    'number' => '1800111222'
]

The Problem

The last query should load the data from the cache “PQRS” with dependency injection. However, the data was still loading from the “ABCD” CACHE, which has no dependency injection STEP 1.

Time solved the issue:

After 1 hour, when the ABCD cache automatically expires, the above code will work perfectly and return the PQRS value.

Hope you got the point.

1 Like
12

Glad you solved it. And thanks for documenting it for others.

1 Like