I'm working on a web-based database application, using CakePHP for the back-end, and like most database applications, it needs a search function.

This search function scans the primary table, which is designed in such a way that in addition to the expected unique primary key, each row also features non-unique record_number and record_instance fields, permitting each record, identified by record_number, to have multiple versions, identified by record_instance.

Multiple fields can have criteria specified for searching the database, however, one feature of the search function in particular is causing a problem when trying to implement it with CakePHP.  It has been requested, that by default, the search should only show the most recent instance of any given record.

The typical way to implement this would be by using a subquery, thusly:

SELECT *
FROM table
WHERE record_instance = (SELECT MAX(record_instance)
                           FROM table AS subtable
                          WHERE subtable.record_number = table.record_number)

Sure, it'd be easy enough to implement this using Cake's model.query method, but doing it that way means that the other search criteria will have to be added to the query manually, and they'll also have to be escaped manually to avoid SQL injection attacks.

If I'm doing it all manually, then why exactly am I using Cake again?

Cake's documentation provides information and an example for using subqueries. Unfortunately, in addition to being baroque, Cake's subquery handling don't permit the sub-table to reference the main table, because it wraps the record_number reference from the main table in quotes, thinking it's a literal value, which it isn't.

After scanning the inter-webs for quite some time and coming up empty, I eventually stumbled upon a way to get it to work through trial and error.

The normal way to specify search conditions for the model.find method is to append them like this:

$conditions = array('table.active' => array(0, 1));
$conditions +=  array('AND' => array('table.field1' => 'criteria1'));

Handily, this method can be used to also append the subquery to the conditions:

$conditions = array('table.active' => array(0, 1));
$conditions +=  array('AND' => array('table.field1' => 'criteria1'));
$conditions +=  array('record_instance = (SELECT MAX(record_instance) FROM table AS subtable WHERE subtable.record_number = table.record_number)');

Cake will automatically add the subquery to the WHERE clause using 'AND'.

Admittedly, this does seem to be a bit of a hack, but it feels like less of a hack than not taking advantage of any of Cake's features by generating the entire SQL manually.