CakePHP, wrting a better query string.

Question

In Cakephp is there a better way to write this:

$unread_orders = $this->Order->find('all', array('conditions' => array('Order.status' => 'unread') ));
$read_orders = $this->Order->find('all', array('conditions' => array('Order.status' => 'read') ));
$dispatched = $this->Order->find('all', array('conditions' => array('Order.status' => 'dispatched'), 'limit' => 5));
$canceled = $this->Order->find('all', array('conditions' => array('Order.status' => 'canceled'), 'limit' => 5));

There is a limit on the dispatched and canceled items.

It's seems like there would be a more effcient way of doing this, in one call to the database instead of 4.

Cheers.

Answer 1

One way is to do

$orders_read_unread = $this->Order->find('all', array('conditions' => array('OR' => array(array('Order.status' => 'unread'), array('Order.status' => 'read')))));
$orders_disp_cancel = $this->Order->find('all', array('conditions' => array('OR' => array(array('Order.status' => 'canceled'), array('Order.status' => 'dispatched'))), 'limit' => 5));

EDIT: Updated queries. Thanks Mark for clarifying.

Answer 2

<?php
    ...
    $orders_read_unread = $this->Order->find( 'all', array(
        'conditions' => array( 'Order.status' => array( 'unread', 'read' )),
        'group' => array( 'Order.status' ),
    ));

    /**
     *  Use this if you need 5 of EITHER canceled OR dispatched order
     *  if you need 5 of EACH you need to break it into two queries.
     */

    $orders_dispatched_canceled = $this->Order->find( 'all', array(
        'conditions' => array( 'Order.status' => array( 'canceled', 'dispatched' )),
        'group' => array( 'Order.status' ),
        'limit' => 5
    ));

    /**
     *  Use these if you need 5 of EACH dispatched AND canceled orders
     */

    $orders_dispatched = $this->Order->find( 'all', array(
        'conditions' => array( 'Order.status' => 'dispatched' ),
        'group' => array( 'Order.status' ),
        'limit' => 5
    ));

    $orders_canceled = $this->Order->find( 'all', array(
        'conditions' => array( 'Order.status' => 'canceled' ),
        'group' => array( 'Order.status' ),
        'limit' => 5
    ));
    ...
?>

Should do the trick for you without having to deal with the 'OR' key syntax. It will generate a slightly less efficient IN ARRAY( '..' ,'..' ) syntax but keeps the php a little cleaner.

As an alternative you could look at either sub-queries - which are a pain with Cake. The book has an example of using the query builder via the datasource to inject a query into the conditions array of a normal cake find call.

http://book.cakephp.org/view/1030/Complex-Find-Conditions

And remember both of these finds should be in the model inside a function - you can either define a custom find type or just call a model function directly from your controller.

http://www.pixelastic.com/blog/88:using-custom-find-and-paginate-methods-in-cakephp-1-3