I GOT IT!
After some exhaustive researches I found the solution... And here is
how to paginate a custom query using plain SQL:
in the controller:
$userQuery = 'user terms';
$results = $this->paginate(array('conditions'=>array('query'=>
$userQuery')));
the paginate() function:
function paginate($conditions, $fields, $order, $limit, $page = 1,
$recursive = null, $extra = array()) {
$recursive = -1;
$query = $conditions['query']; // the user query
$sql = $this->__generateSearchQuery($query); // made by me to
generate a plain SQL query with UNION to merge many tables
$sql .= ' LIMIT '.$limit.' OFFSET '.(($page-1)*$limit); //
limit and offset must be calculated by you
return $this->query($sql);
}
the paginateCount() function:
function paginateCount($conditions = null, $recursive = 0, $extra
= array()) {
$recursive = -1;
$query = $conditions['query']; // the user query
$sql = $this->__generateSearchQuery($query); // made by me to
generate a plain SQL query with UNION to merge many tables
$sql = 'SELECT count(*) FROM ('.$sql.') as sub';
$count = $this->query($sql);
return $count[0][0]['count'];
}
I'm using PostgreSQL. Syntax may vary for other databases.
Thank you all!
Cheers!
On 15 jun, 14:21, cricket <[email protected]> wrote:
> On Jun 15, 10:17 am, Lucca Mordente <[email protected]> wrote:
>
> > SearchableBehavior seems to be very nice indeed.
>
> > I'd have to create a setup to index the existing data and modify it to
> > match my needs, that which would spend a lot of time.
> > Yes, I'll consider to use this behavior.
>
> > However, I have no enough time to swap right now, facing the time
> > spent creating my own.
>
> > Isn't there a way to use CakePHP paginator to paginate a plain SQL?
>
> I've no experience with that, although i'm sure it's come up before.
> Can it be done? Dunno. This page seems to be at least related:
>
> http://book.cakephp.org/view/249/Custom-Query-Pagination
>
> Make sure to check the comments.
>
> There seems to be a few other pages out there. Google "cakephp
> paginate custom" and see if anything that comes up helps.
Check out the new CakePHP Questions site http://cakeqs.org and help others with
their CakePHP related questions.
You received this message because you are subscribed to the Google Groups
"CakePHP" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected] For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
