Welcome to The Cookbook

I think some of the queries might be clearer if their output SQL was also shown.

Add some output examples.

my cake is version 1.2.0.6311 beta and i found this:

"Post.title LIKE" => "%magic%" would generate WHERE Post.title LIKE = "%magic%"

when changed it to this: "Post.title" => "LIKE %magic%" it worked (WHERE Post.title LIKE "%magic%")

maybe this just happened in this beta cake version only put i thought it's worth a comment here

It appears that if the statements inside the "or" statement are not each in their own array then the AND operator is applied.

e.g.

array( "or" => array (

"Post.title" => array("First post", "Second post", "Third post"),

"Post.created >" => date('Y-m-d', strtotime("-2 weeks"))

)

)

Should be:

array( "or" => array (

array("Post.title" => array("First post", "Second post", "Third post")),

array("Post.created >" => date('Y-m-d', strtotime("-2 weeks")))

)

)

For complex criteria, a nested structure is required.

Since php arrays are basically maps, we cannot simply append 'AND' clauses to the end, but have to nest them in the last element of the last nested element.

Here's a recursive function that will produce such a criteria array.

function addCriteria(&$condArray, $criteria) {

// get the key of criteria to be inserted

reset ( $criteria );

$critKey = key ( $criteria );

if (array_key_exists ( $critKey, $condArray )) {

// if such a key allready exists, wrap it in an 'AND' clause inside the last element

if (! array_key_exists ( 'AND', $condArray )) {

// no 'AND' clause yet, so add one

$condArray ['AND'] = array ();

}

// recursively append the new criteria to the last 'AND' clause (possibly nested deep)

$this->addCriteria ( $condArray ['AND'], $criteria );

} else {

// no such key, so just append it

$condArray += $criteria;

}

}

Usage:

$conditions = array('Model.field_a' => 'value1');

addCriteria($conditions, array( 'OR' => array ( 'Model.field_b < ' => 'value1', 'Model.field_b > ' => 'value2' ) );

addCriteria($conditions, array( 'OR' => array ( 'Model.field_c < ' => 'value1', 'Model.field_c > ' => 'value2' ) );

addCriteria($conditions, array( 'NOT' => array ( 'Model.field_c ' => NULL ) );

.

.

.

These will all be 'AND'ed together.

Complex queries made easy ;)