From time to time when querying the database, you have to break the mold. CakePHP gives many options when it comes to complex conditions, but sometimes you need to push the boundaries. Recently I needed to return a subquery as a field name. It wasn't a high demand query, so I wasn't too concerned with performance. Here was my first approach to "hack" my way through Cake's setup:
$paging = array( 'fields'=>array('User.id', 'User.email', '((select max(ul2.login) from "user_logins" as "ul2" where "ul2"."user_id"="User"."id") >= \''.$two_mo_ago.'\') as "User__alive"'), 'order'=>'User.email asc', 'contain'=>array(), 'limit'=>20 );
I've changed my query a bit for simplicity, but the basic concept is to get a true/false if the user has logged in within the past two months. This works for some databases, but I use sqlite for development, and it complained with "User__alive". To properly group the fields cake uses the Model__field naming, but oddly Sqlite didn't like me using this. So how can you get Cake to handle all this for you? Simple, use virtualFields with your model. The solution ended up being cleaner than the example above. Here's what I ended up with:
$this->User->virtualFields['alive'] = '((select max(ul2.login) from "user_logins" "ul2" where "ul2"."user_id"="User"."id") >= \''.$two_mo_ago.'\')'; $paging = array( 'fields'=>array('User.id', 'User.email', 'User.alive'), 'order'=>'User.email asc', 'contain'=>array(), 'limit'=>20 );
Cake knows how to handle piecing the fields together across databases, so cross platform issue is solved, plus cake's pagination knows what to do when you sort by this field. Of course you will want to assign these virtualFields in your model so they're available everywhere, but for simplicity I showed using/assigning it in the controller.
Adding another level of Cake goodness you may want to consider using the model's buildStatement to generate subquery strings. This will almost eliminate the need to write sql statements at all, thus let Cake do more cross database sql generation for you. I may tackle this in another post. Enjoy!