CakePHP Query Method Does Support Bind Parameters

Just came across a post from a few months ago where I stated that CakePhp doesn’t support bind parameters. I have learned since then that this is not true. Happily, it does support bind parameters. An example I have used successfully:

    function query_bottom_count($bottom_value, $start_uts, $end_uts)
    {
        $sql = <<= ?
AND insert_uts query($sql, $ParamList);
        if ( !$Data ) return 0;
        return count($Data);
    }

Just setting the record straight.

Advertisements
CakePHP Query Method Does Support Bind Parameters

Advance Model Testing in CakePhp

One of the most challenging issues I’ve found in developing with CakePhp is unit testing. Testing models, with its dependence on fixtures, is especially touchy. The documentation, while constantly improving, still seems at crucial points incomplete or inconsistent. In this post, I’d like to draw attention to a few pitfalls that I burned a few brain cycles in overcoming and offer some tips for unit testing models with fixtures.

Cake Core Bugs

There are a couple documented bugs that still outstanding as of release 1.2.4.8284. If you are doing anything more than basic testing, one of these could bite you eventually:

https://trac.cakephp.org/ticket/6205
https://trac.cakephp.org/ticket/6468

Here is a patch file that fixes them:

# Fix issues with CakePhp model unit tests
# Tom at klenwell@gmail.com
# CakePhp Version: 1.2.4.8284

diff -r ead5d3b62da7 -r df04a99fec1b cake/libs/model/datasources/dbo_source.php
--- a/cake/libs/model/datasources/dbo_source.php	Mon Aug 10 21:16:09 2009 -0700
+++ b/cake/libs/model/datasources/dbo_source.php	Mon Sep 14 21:08:12 2009 -0700
@@ -2355,6 +2355,15 @@ class DboSource extends DataSource {
 			$column['default'] = null;
 		}

+                // solves fixture problem
+                // see https://trac.cakephp.org/ticket/6205
+                if (($column['type'] == 'datetime' || $column['type'] == 'timestamp' ) &amp;amp;amp;&amp;amp;amp; isset($column['default']) &amp;amp;amp;&amp;amp;amp; $column['default'] === '') {
+                    $column['default'] = null;
+                }
+                if ( $column['type'] == 'timestamp' &amp;amp;amp;&amp;amp;amp; $column['default'] === 'CURRENT_TIMESTAMP' )
+                    #pr($column);
+                    $column['default'] = null;
+
 		if (isset($column['key']) &amp;amp;amp;&amp;amp;amp; $column['key'] == 'primary' &amp;amp;amp;&amp;amp;amp; $type == 'integer') {
 			$out .= ' ' . $this-&amp;gt;columns['primary_key']['name'];
 		} elseif (isset($column['key']) &amp;amp;amp;&amp;amp;amp; $column['key'] == 'primary') {
@@ -2455,4 +2464,4 @@ class DboSource extends DataSource {
 		return 'string';
 	}
 }
-?&amp;gt;
 No newline at end of file
+?&amp;gt;

diff -r ead5d3b62da7 -r df04a99fec1b cake/tests/lib/cake_test_fixture.php
--- a/cake/tests/lib/cake_test_fixture.php	Mon Aug 10 21:16:09 2009 -0700
+++ b/cake/tests/lib/cake_test_fixture.php	Mon Sep 14 21:08:12 2009 -0700
@@ -114,7 +114,12 @@ class CakeTestFixture extends Object {
 			}
 		}

-		if (!isset($this-&amp;gt;table)) {
+                // solves HABTM problem
+                // see https://trac.cakephp.org/ticket/6468
+                if (isset($model-&amp;gt;table)) {
+                        $this-&amp;gt;table = $model-&amp;gt;table;
+                }
+                elseif (!isset($this-&amp;gt;table)) {
 			$this-&amp;gt;table = Inflector::underscore(Inflector::pluralize($this-&amp;gt;name));
 		}

@@ -190,4 +195,4 @@ class CakeTestFixture extends Object {
 		return $return;
 	}
 }
-?&amp;gt;
 No newline at end of file
+?&amp;gt;

Fixtures

The design and usage of fixtures still seems fickle. For instance, the CakePhp docs and packaged test examples promote the explicit declaration of fields in fixtures. I find this tedious. Happily, it is also unnecessary, thanks to the import property.

To avoid this nuisance, use the import ‘model’ or ‘table’ setting to automatically load the table schema from the existing database. See the example here:

simple_record_fixture.php

A couple other tips for using fixtures:

1. Import all fixtures associated with a model in the unit test for that model
2. If unit testing a plugin, the value in the fixtures property takes this format: plugin.$plugin_name.$model_name
3. Don’t forget to set up an empty database and include a test_suite setting in your database configuration file.

See these links for additional insight:
Cakewell Database Config File
Model Test with Fixtures
CakePhp cake_test_fixture.php

HABTM Relationships

To test models that have HABTM relationships with other models, it is necessary to create fixtures for both models having the HABTM relationship and a fixture to build the join table. See these files from the Cakewell project for examples:

Authwell User-Role HABTM Fixture
Authwell Role-Privilege HABTM Fixture

The Cakewell Authwell plugin include a good example of a complex model unit test that is successfully configured. It includes tests within the plugin directory that autoload a clean test database and successfully test models with HABTM associations. The source can be found here:

Authwell Plugin Test Directory

Advance Model Testing in CakePhp

Project Cakewell

Project Cakewell is a CakePhp prototype application. Its main purpose is to document and demonstrate useful concepts and features of a web application built with the CakePhp framework. These include things like unit testing a component, creating a working fixture , and configuring your application to automatically detect the server environment and set the debug level accordingly.

The code is open source and available at the Cakewell Google Code site at:

http://code.google.com/p/cakewell/

The live demo can be found at:

http://cakewell.klenwell.com/

I’ll add updates here as I progress.

Project Cakewell

Dynamic Validation Error Messages in CakePhp

Problem

In CakePhp, I want to invalidate a field submitted in a form and set the error message dynamically. If the error message is set by a validation parameter within the model, the new message should override that error message and be displayed by the form in the view.

Overview

Consider the following three cases as a developer:

1. You have a textarea field which requires user input of at least 12 words. When the user inputs less than 12 words, you’d like to display a message that states, “This field requires a response of at least 12 words. Your response was $x words long” where $x is the actual number of words in the user’s input.

2. You have a form with three input fields. None of them is required in and of itself. However, for the form to be valid, 2 of the 3 must be filled in.

3. You have an application in which one user submits a form which is then reviewed and approved by a second. The form has a “summary” field which the first user may fill in but does not have to. However, the second user must fill it in if it is empty.

Each of these requirements describes a somewhat complex validation case that CakePhp’s normal method for configuring data validation within the model doesn’t adequately address.

Solution

Rather than using the static form of validation described in the documentation, or even a custom validation rule which, although it may suffice, is still oriented around a single field with a static method, I recommend validating the field dynamically from within the controller or a custom model method using the model’s invalidate field.

The following commented code demonstrates a technique which could be used to satisfy each of the three cases above:

 'pass',
        1 =&gt; 'fail',
        2 =&gt; 'requires further review'
    );
   
    // snipped
   
    // essentially a wrapper for save with some extra circumstantial validation
    function update_record($FormData)
    {
        $UpdateData = $FormData['MyModel'];
        
        // situation-specific validation: this code is only required in this
        // instance so we don't set a rule within the model's validation parameters
        // above.  note: this could also be given its own separate method
        $textarea_wordlen = str_word_count($UpdateData['my_textarea']);
        if ( $textarea_wordlen my_textarea_min_word_req )
        {
            $s_ = ( $textarea_wordlen === 1 ) ? '' : 's';   // for the grammar nazis
            
            // note that it is here we're dynamically setting the error message
            $this-&gt;invalidate('my_textarea',
                "This field requires at least {$this-&gt;my_textarea_min_word_req} words.  Your response was {$textarea_wordlen} word{$s_}.");
        }
        
        // in update only, an internal code is required
        if ( !isset($this-&gt;ValidCodeList[$UpdateData['internal_code']]) )
            $this-&gt;invalidate('internal_code', 'please select a valid review code');
        
        // if the other fields are valid, the model will be saved
        if ( $this-&gt;validates($UpdateData) )
        {
            $this-&gt;create();
            $this-&gt;id = $UpdateData['id'];        // set id to update record
            return  $this-&gt;save($UpdateData);
        }
        // return false (which will lead back to the form)
        else
        {
            return 0;
        }
    }
}

?&gt;

In the end, it’s pretty simple and straightforward. Still, I could not find it clearly spelled out in any one place in the CakePhp documentation or elsewhere on the web.

One caveat to note that will further explain the inclination to forego usage of the model’s validate parameter above the I came across here: http://groups.google.com/group/cake-php/msg/095804b352539b97?hl=en. If you do have a validation rule set for that field within the model, the message will get overridden by the default error message there if the field also fails the validation set there.

Dynamic Validation Error Messages in CakePhp

CakePhp: Custom Pagination Methods

I wanted to use the CakePhp pagination helper to paginate some complex records. The model that the paginator was referencing had both belongsTo and hasMany associations. belongsTo associations are not a problem. hasMany, I discovered, are — which makes sense when I think about.

As the ticket notes:

You can only paginate LEFT JOIN tables by default. hasMany relationships are performed as a separate query, and thus cannot have conditions in the primary paginate() call. If you wish to do this, you need to implement paginate() and paginateCount() in your model.

So that’s what I did. Only it wasn’t quite as simple as I thought it would be.

The CakePhp manual does provide some guidance: 4.9.4 Custom Query Pagination. This comes from the example originally posted here (littlehart.net), where there are additional comments to both confuse and clarify a bit.

One thing that I didn’t see ever made explicit is how exactly you integrate the custom model methods you create, paginate and paginateCount, with the paginator syntax in your controller or helper. Well, it turns out, as you would probably hope, that it’s fairly seamless. You just use the standard pagination markup , and the custom methods override Cake’s default methods (this changeset illustrates).

With hasMany associations, as is the case in the example above from Cake’s website, you are probably going to need to use GROUP BY syntax. For my particular case, this required a custom SQL query, which meant using Cake’s Model class’s query. (I’m a little surprised and concerned that it doesn’t support bind parameters.)

I’ve posted the relevant code to my wiki: http://www.klenwell.com/is/Paste20090117

I hope it helps steers someone to the answer they’re looking for.

CakePhp: Custom Pagination Methods

CakePhp: Radio Select Columns

In HTML forms, I generally prefer radio groups over select menus. One advantage select elements have over radio groups is a smaller visual footprint. A long radio list takes up a lot more screen room than a long select list.

One way to make a radio list more efficient is to break it up into columns. I’ve written a CakePhp helper that accomplishes this. An illustration:

radio group without (l) and with columns (r)
radio group without (l) and with (r) columns

It turns out that CakePhp Form helper’s input method gives me all the tools I needed it to adapt my code pretty easily to it.

I’ve pasted the code over on my wiki: http://www.klenwell.com/is/Paste20090110

The part of the code I think is most interesting is the code I use to divvy up the options array into the columns:

// get option list
$OptionList = explode($this-&gt;separator_marker, $middle);
$num_options = count($OptionList);

// snip

// divvy options into columns (wrote this code a while ago and seems to work)
for( $i = 1; $i &lt;= $num_cols; $i++ )
{
    $COL[$i] = ceil( $num_options/($num_cols+1-$i) );
    $num_options = $num_options - $COL[$i];
}

I look at it now and I know the gist of what it's doing, but still have to really stop and think about it to fully understand it. I think it's interesting because it shows how my mind works better procedurally than it does mathematically.

Here's an illustration showing again the columnized radio list along with the class and id markup that can be used for styling:

radio columns with id/class markup

I’ll eventually add this the cakewell app in my google code repository.

One caveat: this works with the Form helper’s input method (type set to ‘radio’), but does not seem to work with the Form helper’s radio method.

CakePhp: Radio Select Columns