CakePHP - CustomQuery / Pagination
I’ve been looking for a pagination solution that I could use when I needed to write a custom query using CakePhp 1.2.x.x.
Looking at the cakeapps in the wild page and noticed phtagr. Checked out the demo and noticed that it wasn’t using the native cakephp pagination.
I started reading and hacking and ended up with a solution that I just had to share.
Thanks to Sebastian Felis !!
INSTALL
download: custom_query-0.1.tar.gz
copy custom_query.php to controllers/components/
copy cqpagination.php to views/helpers/
Controller
create a skel controller and copy this content to it.
var $helpers = array('html', 'cqpagination');
var $components = array('CustomQuery');
function beforeFilter()
{
parent::beforeFilter();
$this->CustomQuery->controller =& $this;
$this->CustomQuery->parseArgs();
}
function beforeRender()
{
$this->params[’search’] = $this->CustomQuery->getParams();
}
function index()
{
$sql = “SELECT * FROM {$this->CustomQuery->_tp}members as Member “;
$this->CustomQuery->setSqlQuery($sql);
$data = $this->CustomQuery->paginate();
$this->set(’data’, $data);
}
$this->set(’_action’, $this->action);
View
add this to the view.
$cqpagination->initialize();
?>
echo $cqpagination->prev().’ ‘.$cqpagination->numbers().’ ‘.$cqpagination->next();
?>
NOTE:
I needed pagination for a search via $this->data as a result I have to save the sql query to a session to ensure the pagination
maintain the original sql.
if($this->data) {
$this->Session->delete('current.member.sql.query');
}
$_currentSql = $this->Session->read(’current.member.sql.query’);
if(isset($_currentSql)) {
$sql = $_currentSql;
}
else {
$sql = “SELECT members.id, members.username, members.online_status, members.main_image, members.city, members.state,
profiles.page_views, profiles.personal_quote, profiles.here_for “;
$sql .=”FROM members
JOIN profiles ON members.id = profiles.member_id
WHERE active_member = ‘1′
{$age}
{$gender}
{$city}
{$state}
{$picture}
{$orderBy}
“;
//writing the query to a session to prevent from losing it during pagination.
$this->Session->write(’current.member.sql.query’, $sql);
}