RSS
 

CakePHP: complex SELECT queries

03 Sep

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;
?>
<?php echo $paginator->prev('<< '.__('Previous Page', true), 
array(), null, array('class'=>'disabled'));?>
 |     <?php echo $paginator->numbers();?>
    <?php echo $paginator->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. ;)

 
No Comments

Posted in Heap, Memo

 

Tags: , , ,

Leave a Reply