(almost) English, CakePhp, Top Story

Single query cache and pagination cache (until a future post) and ad hoc joins. An over-override of appModel::find

 , , ,

In a project i’m working on, being unaware of super behaviours or plugins that may appear on github, i made some search to find fast and effective solutions (or ideas) for the needs at hand. Namely, performance -> caching.

Needs stacked over time.

I liked the idea of single query cache. I needed ad hoc joins to perform complex filtering e reduce the number of queries per request. I had some future-publish feature. I needed to cache many paginated records.

For every need, i found a ready made solution (or almost ready, with a good implementation idea and examples):

Great! Just one problem: many of these solutions implied a Model::find override (and the need to define Model::paginate and Model::paginateCount).

So, I took the ideas and big chunks of code, and tried to merge them together in a single working thing for my app.

I’m sharing this because 1- it may be useful for someone else, 2- i may get suggestions on a better implementation / alternative or bug’s / issues notices.

The code is specific to my case, i hope it’s clear and easily customizable (i.e. think of $this->site_id property as a group, we need to differentiate query cache for).

Methods in AppModel:

  1. <?php
  2. public function find ($conditions = null, $options = array (), $order = null, $recursive = null) {
  3.  
  4. 	// Custom find
  5. 	// - 'macthes' for actually joining hasMany and Habtm models,
  6. 	// and use conditions on related models
  7. 	$join_type = 'inner';
  8. 	if(isset($options['join_type'])) $join_type = $options['join_type'];
  9.  
  10.         if (is_string ($conditions)) {
  11.             switch ($conditions) {
  12.                 case 'matches':
  13.                     if (!isset ($options['joins'])) {
  14.                         $options['joins'] = array ();
  15.                     }
  16.                     $conditions = isset ($options['operation']) ? $options['operation'] : 'all';
  17.  
  18.                     if (!isset ($options['model']) or !isset ($options['scope'])) {
  19.                         break;
  20.                     }
  21.                     // hack to filter over several HABTM tables
  22.                     $model_list = (is_array ($options['model']) ? $options['model'] : array ($options['model']));
  23.  
  24. 	    foreach ($model_list as $model) {
  25.                     $scope = ((sizeof ($model_list) > 1 and isset ($options['scope'][$model])) ? $options['scope'][$model] : $options['scope']);
  26.  
  27. 		    if(isset($this->hasAndBelongsToMany[$model])) {
  28. 			$assoc = $this->hasAndBelongsToMany[$model];
  29.                         $bind = "{$assoc['with']}.{$assoc['foreignKey']} = {$this->alias}.{$this->primaryKey}";
  30.                         $options['joins'][] = array (
  31.                             'table'         => $assoc['joinTable'],
  32.                             'alias'         => $assoc['with'],
  33.                             'type'          => $join_type,
  34.                             'foreignKey'    => false,
  35.                             'conditions'    => array ($bind)
  36.                         );
  37.                         $bind = $model . '.' . $this->{$model}->primaryKey . ' = ';
  38.                         $bind .= "{$assoc['with']}.{$assoc['associationForeignKey']}";
  39.                         $options['joins'][] = array (
  40.                             'table'         => $this->{$model}->table,
  41.                             'alias'         => $model,
  42.                             'type'          => $join_type,
  43.                             'foreignKey'    => false,
  44.                             'conditions'    => array_merge (array ($bind), (array) $scope)
  45.                         );
  46.                     }
  47.  
  48. 		    if(isset($this->hasMany[$model])) {
  49. 			App::import('Inflector');
  50. 			$assoc = $this->hasMany[$model];
  51. 			$bind = "{$assoc['className']}.{$assoc['foreignKey']} = {$this->alias}.{$this->primaryKey}";
  52.                         $options['joins'][] = array (
  53.                             'table'         => Inflector::tableize($assoc['className']),
  54.                             'alias'         => $assoc['className'],
  55.                             'type'          => $join_type,
  56.                             'foreignKey'    => false,
  57.                             'conditions'    => array ($bind)
  58.                         );
  59. 		    }
  60.  
  61.                     unset ($options['model'], $options['scope']);
  62.                     break;
  63. 		}
  64. 	    }
  65. 	}
  66.  
  67. 	//
  68. 	// CACHE
  69. 	//
  70. 	if ((Configure::read('Cache.disable') === false) && (Configure::read('Cache.check') === true) && is_array($options) &&  isset($options['cache']) && ($options['cache'] !== false)) {
  71. 			$key = $options['cache'];  // solitamente nella forma "modello.'-'.$this->id";
  72. 			$expires = '+1 day';
  73. 			$conf = 'sql';
  74.  
  75. 			if (is_array($options['cache'])) {
  76. 				$key = $options['cache'][0];
  77. 				if (isset($options['cache'][1])) {
  78. 				    $conf = $options['cache'][1];
  79. 				}
  80. 				else $conf = strtolower ($this->alias).'-'.$this->site_id;
  81. 			}
  82.  
  83. 			// Set cache settings
  84. 			$this->_set_cache_config($conf);
  85.  
  86. 			// Load from cache
  87. 			$results = Cache::read($key, $conf);
  88.  
  89. 			if (!is_array($results)) {
  90. 			    $results = parent::find($conditions, $options, $order, $recursive);
  91. 			    Cache::write($key, $results, $conf);
  92. 			}
  93. 			return $results;
  94. 		}
  95. 		// Not cacheing
  96.  
  97.         return parent::find ($conditions, $options, $order, $recursive);
  98.  
  99.     }
  100.  
  101.     protected function _set_cache_config ($conf) {
  102.  
  103. 	$expires = $this->_set_expiry();
  104.  
  105. 	$path = CACHE . 'sql'.DS. $conf;
  106.  
  107. ///// create folder if not exists
  108. 	App::import('Folder');
  109. 	$Folder = new Folder($path, true); //, '755');
  110. 	unset ($Folder);
  111.  
  112. 	Cache::config($conf, array(
  113. 				'engine' => 'File', // DA SPECIFICARE, non defult 'FIle' in cake 1.3
  114. 				'path' => $path,
  115. 				//'prefix'    => strtolower($this->name) .'-',
  116. 				'duration'  => $expires
  117. 			    )
  118. 		);
  119.  
  120.     }
  121.  
  122.     protected function _set_expiry() {
  123. 	$expires = '+1 day';
  124. 	if (!empty($this->_schema['publish_date'])) {
  125. 				$next = $this->find('first', array('conditions' => array($this->name . '.publish_date > NOW()'), 'order' => array($this->name . '.publish_date'), 'recursive' => -1));
  126. 	    if (!empty($next)) {
  127. 		$expires = $next[$this->name]['published_date'];
  128.  
  129. 	    }
  130. 	}
  131.  
  132. 	return $expires;
  133.  
  134.     }
  135.  
  136.     function delete_cache_data($name = null, $conf = 'sql') {
  137. 	if($conf == 'sql')
  138. 	    $conf = strtolower($this->alias).'-'.$this->selected_site_id;
  139.  
  140. 	//debug ($conf);
  141. 	   $this->_set_cache_config($conf);
  142.  
  143. 		if ($name) {
  144. 			Cache::delete($name, $conf);
  145. 		} else {
  146. 			Cache::clear(false, $conf);
  147. 		}
  148.     }
  149.  
  150. //
  151. //    public function paginateCount($conditions = array(), $recursive = 0, $extra = array()) {
  152. //        $parameters = compact('conditions');
  153. //        if ($recursive != $this->recursive) {
  154. //            $parameters['recursive'] = $recursive;
  155. //        }
  156. //
  157. //        if (isset($extra['type']) && ($extra['type'] == 'matches')) {
  158. //            $extra['operation'] = 'count';
  159. //            return $this->find('matches', array_merge($parameters, $extra));
  160. //        }
  161. //        else {
  162. //            return $this->find('count', array_merge($parameters, $extra));
  163. //        }
  164. //
  165. //    }
  166.  
  167.     function paginateCount ($conditions = null, $recursive = 0, $extra = array()) {
  168.  
  169.         $parameters = compact('conditions');
  170.         if ($recursive != $this->recursive) {
  171.             $parameters['recursive'] = $recursive;
  172.         }
  173.  
  174. // spostato sotto..
  175. //        if (isset($extra['type']) && ($extra['type'] == 'matches')) {
  176. //            $extra['operation'] = 'count';
  177. //            return $this->find('matches', array_merge($parameters, $extra));
  178. //        }
  179. //        else {
  180. //            return $this->find('count', array_merge($parameters, $extra));
  181. //        }
  182. 	$paginationcount = array();
  183. 	if ((Configure::read('Cache.disable') === false) && (Configure::read('Cache.check') === true) && $this->usePaginationCache ) {
  184. 			//$key = $options['cache'];
  185. 			$expires = '+1 day';
  186. 			$conf = 'sql';
  187. 	}
  188. 	if ($this->usePaginationCache) {
  189. 	    $conf = strtolower($this->alias).'-'.$this->site_id;
  190. 	    $this->_set_cache_config($conf);
  191. 		$args = func_get_args();
  192. 		$uniqueCacheId = '';
  193. 		foreach ($args as $arg) {
  194. 			$uniqueCacheId .= serialize($arg);
  195. 		}
  196. 		$uniqueCacheId = md5($uniqueCacheId);
  197. 		if (!empty($extra['contain'])) {
  198. 			$contain = $extra['contain'];
  199. 		}
  200. 		if (!empty($extra['joins'])) {
  201. 			$joins = $extra['joins'];
  202. 		}
  203. 		if (!empty($extra['group'])) {
  204. 			$group = $extra['group'];
  205. 		}
  206.  
  207. 		$paginationcount = Cache::read('paginationcount-'.$uniqueCacheId, $conf);
  208. 	}
  209. 		if (empty($paginationcount)) {
  210. 		        if (isset($extra['type']) && ($extra['type'] == 'matches')) {
  211. 			    $extra['operation'] = 'count';
  212. 			    $paginationcount = $this->find('matches', array_merge($parameters, $extra));
  213. 			}
  214. 			else {
  215. 			    $paginationcount = $this->find('count', compact('conditions', 'contain','joins','group')); // , 'recursive'
  216. 			}
  217. 			if ($this->usePaginationCache)
  218. 			    Cache::write('paginationcount-'.$uniqueCacheId, $paginationcount, $conf);
  219. 		}
  220. 		return $paginationcount;
  221.  
  222. 	}
  223.  
  224.     function paginate ($conditions, $fields, $order, $limit, $page = 1, $recursive = null, $extra = array()) {
  225.  
  226. 	if ((Configure::read('Cache.disable') === false) && (Configure::read('Cache.check') === true) && $this->usePaginationCache) {
  227. 			//$key = $options['cache'];
  228. 			$expires = '+1 day';
  229. 			$conf = 'sql';
  230. 	}
  231.  
  232. 	$conf = strtolower($this->alias).'-'.$this->site_id;
  233. 	$this->_set_cache_config($conf);
  234.  
  235. 	if ($this->usePaginationCache) {
  236.  
  237. 		$args = func_get_args();
  238. 		$uniqueCacheId = '';
  239. 		foreach ($args as $arg) {
  240. 			$uniqueCacheId .= serialize($arg);
  241. 		}
  242. 		$uniqueCacheId = md5($uniqueCacheId);
  243. 		$pagination = Cache::read('pagination-'.$uniqueCacheId, $conf);
  244.  
  245. 	}
  246.  
  247. 	if (!empty($extra['contain'])) {
  248. 			$contain = $extra['contain'];
  249. 		}
  250.  
  251. 	if (!empty($extra['joins'])) {
  252. 			$joins = $extra['joins'];
  253. 		}
  254. 	if (!empty($extra['group'])) {
  255. 			$group = $extra['group'];
  256. 	}
  257.  
  258. 	if (empty($pagination)) {
  259. 	    $pagination = $this->find('all', compact('conditions', 'fields', 'order', 'limit', 'page',  'group', 'contain','joins','group')); //'recursive'
  260. 	    if ($this->usePaginationCache)
  261. 		Cache::write( 'pagination-' . $uniqueCacheId, $pagination, $conf);
  262.  
  263. 	}
  264.  
  265. 	return $pagination;
  266.  
  267.     }
  268.  
  269.  public function afterSave($created, $key = null) {
  270.  
  271.  $conf = strtolower($this->alias).'-'.$this->selected_site_id;
  272.  $conf = $this->_set_cache_config($conf);
  273.  
  274.  if($created) $name = null;
  275.  
  276.  if(!$created) {
  277.  $name = $key;
  278.  if(is_null($key)) $name = strtolower($this->alias).'-'.$this->id;
  279.  }
  280.  
  281.  $this->delete_cache_data ($name, $conf);
  282.  
  283.  $home_conf = strtolower($this->alias).'-%%';
  284.  $this->delete_cache_data ($name, $home_conf);
  285.  
  286.  parent::afterSave($created);
  287.  }
  288.  
  289. ?>

Query cache is called in controller actions, as in sanisoft example, by adding a the ‘cache’ key like this

‘cache’ => array(‘uniquequeryidentifier’, ‘configuration_name’)

Pagination caching is (in my case, i simply differentiate between frontend and admin) turned on/off by a model’s property (AppModel->usePaginationCache), but it could of course be implemented in other ways.