While working on the Central News site, I had to make some more complex queries to my database to pull only the news that should be shown today. Each news post has two date fields, start_showing and end_showing. This way we can allow a particular entry to be in the news for more than a single day without resubmission.
One would think that with CakePHP, it’d be a piece of cake, but it isn’t so straight forward.
My initial, raw sql query looked something like this.
SELECT * FROM `news_posts` WHERE NOW() BETWEEN start_showing AND DATE_ADD(end_showing, INTERVAL 1 DAY) AND 1 = 1;
That did get the date range right, but it pretty much left me high and dry for my NewsRevision/NewsState tables. Thus I had to go down a more Cake-y route.
I found Model->find() syntax to catering to my needs. It’s still a messy ordeal though, because it’s a bunch of nested arrays. My first attempt at this was to mimic my original raw query and hope I would get somewhere.
$finder = array( " BETWEEN " => array('NOW()' => array("NewsPost.start_showing", "NewsPost.end_showing") ) );
Even though the CakePHP documentation for complex queries, claims you can use comparison words like between you cannot do that. CakePHP puts the comparison word inside of quotes so then mysql thinks it is a column name! That’s not good. That isn’t right so, we need the next plan.
So I had to actually do some logic in my brain, which isn’t easy, to get the between word out of the statement. Doing that added some more greater than/less than comparisons. Here is the new CakePHP array for find.
$finder2 = array( "and" => array(
"NewsPost.start_showing <=" => date("Y-m-d"),
"NewsPost.end_showing >=" => date("Y-m-d", strtotime("+1 day"))
));
Looks pretty nice, once you figure it all out, doesn’t it? My requirements are that start showing is smaller than today and today is smaller than end showing plus one day. Those conditions were met with this. Give special notice to the array with the key of and. However, another problem appeared! There was only a single entry at any one time.
The solution came crashing down from CakePHP documentation for findAll which just happens to be deperated. No luck there. Then, if you pause long enough to figure out the Model->find() syntax, you’ll notice you can specify it to find all and use your own conditions. Clever. Here is one I came up with after all of that.
$finder2 = array( "and" => array(
"NewsPost.start_showing <=" => date("Y-m-d"),
"NewsPost.end_showing >=" => date("Y-m-d", strtotime("+1 day"))
));
$results = $this->NewsPost->find("all", array("conditions"=>$finder2));
The solution came slowly but surely. I wish the CakePHP documentation would show some more real world examples. Example cases are so important when learning a new foundation; example cases can’t be neglected for any reason.
Update
Since publishing this post in August of 2008, I’ve cleaned it up just a little bit. I read it over for spelling and also changed a problem with how the sql was being presented (thanks goes out to commenter Colin for pointing that out).
