In my previous post I commented that CakePHP doesn’t like “~” as an operator when building queries for PostgreSQL. Well I got to digging around and found that the fix is relatively simple, however I’m not sure that it follows the Cake intentions. PostgreSQL uses “~”, “~*”, “!~”, “!~*” as operators for regular expressions. To be able to use these we need to add these to Cake’s valid operators, which is an array in cake/libs/model/datasources/dbo_source.php. Inside there look for the array “$__sqlOps” and add these four operators to it like so:(mine is on line 73)


var $__sqlOps = array('like', 'ilike', 'or', 'not', 'in', 'between', 'regexp', 'similar to', '~', '~*', '!~', '!~*');

Now if you return to your query and try again you should be able to use the following syntax:


'conditions'=>array( 
	"Table.field ~*"=>$my_param 
)

I will try to drop this in to the Cake squad, but not sure if this starts to vary off their path for “standard sql”, although REGEXP operator is already used and it is only used with MySQL. We shall see!