9

I used views_get_view_result() to get results from a view because it was convenient at that time. The query result rarely changes; I could use views caching for 6 days.

If I wanted to convert it to a call to db_query() how could I enable caching?

9

Doesn't mattter for caching if you use views or db_query(). Caching works always the same, how the data is fetched when the cache misses is completely up to you.

  1. Build a cache ID to identify your cache entry. Can be a simple, hardcoded string or something complex based on arguments and so on.
  2. Check if can load from the cache.
  3. If not, rebuild data and put it into the cache with the desired expiration time.

To see some examples, you can look at the functions that use cache_get(), for example variable_initialize().

If your function is called multiple times then you probably want to combine it with a static cache, see for example archiver_get_info(). And if the data rebuilding is really slow, you can prevent it from happening multiple times by using the locking framework as variable_initialize() does.

Note that caching a single query only makes sense if it's a slow one, because a cache_get() is also a db query unless you use an alternative cache backend like Memcache.

And lastly, Views has caching already built-in and can be configured in your view. So that might be an option as well.

4
  • Beat me to it ;) I'll leave my answer up as a code example but this is way more useful information – Clive Apr 28 '12 at 15:27
  • I thought PDO instances were not serializable? – mpdonadio Apr 28 '12 at 15:28
  • 1
    No, they are not, but that's not relevant. You don't cache the pdo result resource, you cache whatever data structures you actually fetch from that query. – Berdir Apr 28 '12 at 15:29
  • I would say it is very relevant. @MotoTribe was asking about caching results from db_query(), and having to cache the value from $results->fetchAll() and not $results is key to actually getting it to work. – mpdonadio Apr 28 '12 at 15:37
7

I don't think the DB layer has any built in caching mechanism (though I might be wrong), but you could make use of the default cache API.

This is just a basic example that will cache the results of a query to get nodes of a certain type:

function MYMODULE_get_nodes_by_type($type) {
  // Setup a cache ID
  $cid = 'MYMODULE:node_types:' . $type;

  // If a cached entry exists, return it
  if ($cached = cache_get($cid)) {
    return $cached->data;
  }

  // Otherwise load the data
  $data = db_query('SELECT * FROM {node} WHERE type = :type', array(':type' => $type))->fetchAll();

  // And cache it
  cache_set($cid, $data, 'cache', strtotime('+6 days'));
}
3

In addition to the standard cache_set / cache_get mechanism that Drupal provides, if you are using MySQL as your database, then you can enable the query cache, which can cache the results of views, or any other database queries, transparently. mysqltuner can aid in figuring out good values for the cache size.

Just note that if you are doing a lot of writing to the database, then query caching becomes less effective because of the way the cache invalidation strategy works (a write to a table invalidates all entries that SELECT FROM or JOIN that table).

There is also a caching mechanism for PostgreSQL, but I don't have direct experience with it.

3

I recently discovered the Cache Actions module. With this module, you can set your view caching to Rules-triggered Cache and create a rule for invalidating the cache on specific views and views displays.

For example, the cache for a view that lists nodes of a specific content type can be emptied when a new node is created of that content type.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.