CakePHP: complex SELECT queries
As the project grows I had to work on some more complex queries to provide users with better searching facilities.
In this case, you might use two options:
- Straightforward find() function from App::Model (where you’ll have to handle the outpu of data yourself, and trying to fit the search results in your websites layout)
- Use Pagination functionality (which is designed for handling big chunks of data for you)
A simple example: from a small search menu, I need to get the data about item’s price, its type etc, so at this point, find() solution would look like:
$condition = array('OR' => array(
'Item.type' => $this->data['Item']['type'],
'Item.qty' => $this->data['Item']['qty'],
),
array('Item.price BETWEEN ? AND ?' => array($start_price,$end_price))
);
$this->('results', $this->Item->find('all', $condition));
Once you set results array in the view template, it will cause you few hours on how to rearrange data presentation, meanwhile you can use Pagination:
/*
* I'm going to use the same $condition
* the difference will be at the view level and the way of setting the data "results"
*/
$this->set('results', $this->paginate('Item', $condition);
And in the view you might add some code like:
echo $paginator->counter(array(
'format' => __('%page% of %pages%, showing %current%
records out of %count% total, starting on record %start%, ending on %end%', true)
));
foreach($results as $i => $item):
/*
* Items output
*/
endforeach;
?>
prev('<< '.__('Previous Page', true),
array(), null, array('class'=>'disabled'));?>
| numbers();?>
next(__('Next Page', true).' >>',
array(), null, array('class'=>'disabled'));?>
Last lines of the code would manage the results listing for you, which has to be defined in your Controller:
var $paginate = array(
'Item' => array(
'limit' => 5,
'order'=> array('Item.added' => 'ASC')
)
)
Done, now you can easily handle your search outputs. 