Tag Archives: CakePHP

Integrating calculated fields and model data in CakePHP

(This is mostly a summary of Dealing with calculated fields in CakePHP’s find().)

One of the great things about CakePHP is that if it doesn’t have some core functionality you want/need there are easy ways to add it. More and more I’m taking advantage of this ability. This all comes about because I wanted to have calculated fields available inline with the model data. By calculated fields I mean results that are not data columns (e.g. SELECT *, CURDATE() AS current_date FROM users … yes, that’s a fairly contrived example).

By default Cake places results from calculated fields outside the model data, like this:

    [0] => Array
            [User] => Array
                    [id] => 1
                    [username] => aaas

            [0] => Array
                    [current_date] => '2011-05-13'


What we want is to place the “current_date” calculated field inside the User model, so it’s more naturally accessed with $users[0]['User']['current_date'] instead of $users[0]['User'][0]['current_date']. Easy enough to do through a model’s afterFind() callback method (to make it widely available place the function in app_model.php).

function afterFind($results, $primary=false) {
	if($primary == true) {
		if(Set::check($results, '0.0') && Set::check($results, "0." . $this->alias)) {
			$fields = array_keys( $results[0][0] );
			foreach($results as $key=>$value) {
				foreach( $fields as $fieldName ) {
					$results[$key][$this->alias][$fieldName] = $value[0][$fieldName];
	return $results;

And yet, even as I add such enhanced functionality to my web apps I’m finding limits. The above logic is complicated a little because you don’t know what type of results you’re getting. The results you get from calling, for example, find('all') versus find('count') are not the same. But Cake doesn’t give any hints to the afterFind() callback, and as a result additional logic is included to try and guess at our data structure. The above adds a quick but incomplete hack by a) checking that the results are for the primary model queried (e.g. not from contained model), b) checking for the presence of nested numerical keys, and c) checking that there is model data to integrate with.

The take away is that while the code produces the desired data structure, in its current form it does so only for specific results.

Rewriting URLs on IIS6

One of the nice things about CakePHP is that it attempts to make a site more friendly to the average web site visitor by creating easier-to-remember URLs. There are a few techniques that CakePHP uses, but for the sake of this conversation we’ll focus on one way in particular: apache’s mod_rewrite functionality. Using mod_rewrite, URLs that would normally include the controller file (index.php) and a querystring can be rewritten as a simple file path.

Microsoft doesn’t include mod_rewrite-style functionality in IIS by default, though it has created an extension for IIS7. Since we are currently using IIS6 the extension isn’t an option for us. Fortunately there are a few other options available, including an open-source project called Ionics Isapi Rewrite Filter (IIRF). IIRF is an ISAPI filter that is very similar to mod_rewrite in terms of functionality. After some testing I’ve found this filter works almost perfectly for enabling CakePHP’s friendly URLs.

Getting Started

Installation is painless and requires only a few steps. The following is based on the IIRF release which does not include an installer:

  1. Extract the files from the IIRF archive to a folder on the server.
  2. Open the properties of the IIS root “Web Sites” folder or the specific site that needs IIRF.
  3. In the “ISAPI Filters” tab create a new entry for IIRF; name the new filter (e.g. “IIRF”); specify the IIRF DLL located with the extracted IIRF files at binIIRF.dll.
  4. Ensure that the IIS user has read/execute access to the IIRF DLL and read/write access to any directories that will be used for logging.
  5. Restart IIRF.

IIRF should now be installed, but I’ve found that sometimes the filter won’t appear to be active in the “ISAPI Filters” tab until it has been used the first time. To enable IIRF all you have to do is create a file called IIRF.ini and place it in the web site root folder or in the root of a virtual directory. This file contains any local configuration directives (such as log directory) and URL rewriting rules.

The obligatory gotcha

I mentioned before that the filter works almost perfectly. The only situation where I’ve had problems up to now is if the URL contains one or more space characters. The IIRF log indicates that a URL with a space is being parsed correctly and returning a valid URL. And yet the web server reports a 404 error.

I’ve only done testing when the final URL references a physical file, but based on a conversation in the support forum I suspect this problem also affects parameters in the querystring. I haven’t yet had a chance to fully investigate the issue, so I don’t have a work-around yet when the issue affects physical files. There does, however, appear to be a work-around for spaces in the querystring.

For now I have decided to ensure that any URLs parsed by IIRF that will point to a physical file/directory does not contain spaces.


CakePHP usage:

Space-in-URL problems:


As of cakePHP you can’t use CURRENT_TIMESTAMP as the default for a column. With MySQL when the default is set to CURRENT_TIMESTAMP the current date+time is inserted for a new row that doesn’t specifically define the value of the column.

From what I can tell, when CakePHP specifies the values for all columns when it creates a new record. A column with a defined default that is not specifically set by the user is manually set by CakePHP (rather than let MySQL handle defaults upon record insertion). But CakePHP doesn’t understand the CURRENT_TIMESTAMP keyword and so treats it as a string and wraps it in quotes. This breaks the resulting INSERT statement and you receive an error:

Incorrect datetime value: ‘CURRENT_TIMESTAMP’

Interestingly, columns that are named “created” and “modified” receive special handling by CakePHP. These columns are treated like auto-update columns by CakePHP and it sets them as expected. With the special handling of these columns in mind it is possible to get around the CURRENT_TIMESTAMP bug by following the recommended settings for created/modified columns, i.e. specifying the field as DATETIME with a default of NULL. CakePHP will automatically update the columns when inserting/updating records.