custom query pagination without using custom paginate
Hello I have a model whose controller serves many pages using normal paginate. Now I need to use a custom query and cannot use the normal find method (or I am not able to) But I do not know how to paginate. The query is something as follows: http://pastebin.com/p0bux1ig The cookbook suggests to override the paginate method, but this would mess up everything in many other views How can I paginate the custom query? Thanks. N. -- Like Us on FaceBook https://www.facebook.com/CakePHP Find us on Twitter http://twitter.com/CakePHP --- You received this message because you are subscribed to the Google Groups "CakePHP" group. To unsubscribe from this group and stop receiving emails from it, send an email to cake-php+unsubscr...@googlegroups.com. To post to this group, send email to cake-php@googlegroups.com. Visit this group at http://groups.google.com/group/cake-php. For more options, visit https://groups.google.com/groups/opt_out.
Re: Custom query Pagination in cakephp
Hi Sricharan,
You haven't given enough information for us to be able to help you.
Without knowing more about your models, joins, queries, parameters and
data in the database we would be making guesses as to where your
problem lies.
The only thing I can say is it does not look as though you are using
paginate at all, your simply running a custom query. I've been using
Cake for around 3 years now and never yet failed to get what I want
using Cake's built-in pagination (never had to create a custom
paginate - have had to force some joins from time to time) so I highly
reccomend you go back and read about paginate some more.
http://book.cakephp.org/view/1231/Pagination
http://book.cakephp.org/view/1237/Custom-Query-Pagination
HTH, Paul.
On Aug 29, 7:01 am, sricharan wrote:
> Hi,
>
> I am using a custom query pagination in cakephp and getting problem
> with the model name.
>
> i am using join query to retrieve the data from 5 tables. I am using
> the query as :
>
> $lim=@$this->passedArgs['limit'];
> if(empty($lim))
> {
> $lim=20;
> }
> i am passing the limit from the.ctp page and
>
> $result_wait=$this->Waitingroom->query("My sql query...LIMIT $lim");
> $this->set(compact('result_wait', 'lim'));
> $num=count($result_wait);
>
> $this->set('waitingrooms', $this->paginate());
>
> Here actually i had to get the count from the join query, but i am
> getting the count from the model name 'waitingrooms'.
>
> Actually i had to get the count as 3 records, but it is showing the
> count as 24 records..
>
> Please help me to fix this problem. Thanks in Advance
--
Our newest site for the community: CakePHP Video Tutorials
http://tv.cakephp.org
Check out the new CakePHP Questions site http://ask.cakephp.org and help others
with their CakePHP related questions.
To unsubscribe from this group, send email to
cake-php+unsubscr...@googlegroups.com For more options, visit this group at
http://groups.google.com/group/cake-php
Custom query Pagination in cakephp
Hi,
I am using a custom query pagination in cakephp and getting problem
with the model name.
i am using join query to retrieve the data from 5 tables. I am using
the query as :
$lim=@$this->passedArgs['limit'];
if(empty($lim))
{
$lim=20;
}
i am passing the limit from the.ctp page and
$result_wait=$this->Waitingroom->query("My sql query...LIMIT $lim");
$this->set(compact('result_wait', 'lim'));
$num=count($result_wait);
$this->set('waitingrooms', $this->paginate());
Here actually i had to get the count from the join query, but i am
getting the count from the model name 'waitingrooms'.
Actually i had to get the count as 3 records, but it is showing the
count as 24 records..
Please help me to fix this problem. Thanks in Advance
--
Our newest site for the community: CakePHP Video Tutorials
http://tv.cakephp.org
Check out the new CakePHP Questions site http://ask.cakephp.org and help others
with their CakePHP related questions.
To unsubscribe from this group, send email to
cake-php+unsubscr...@googlegroups.com For more options, visit this group at
http://groups.google.com/group/cake-php
Re: Custom query pagination
In this case just use plain SQL in paginate() and paginateCount(). On Aug 31, 9:52 pm, Dean C wrote: > Hi folks, > > I can't use a View for the union query as it requires parameters. Even > if I take away the fulltext, and then do a fulltext search on the > view, MySQL can't then delegate the indexes across the union within > the view, so it doesn't work. Any more ideas or sample pieces of code > would be appreciated :) > > Dean > > On Aug 28, 9:09 am, "Dr. Loboto" wrote: > > > > > If you can do view for your union query you will be able to work with > > it as with normal table. > > > On Aug 28, 5:58 am, Dean C wrote: > > > > Hi folks, > > > > Before I begin, yes I've read this > > > page:http://book.cakephp.org/view/249/Custom-Query-Pagination > > > > I've also read many of the other pages on Google but I'm still stuck! > > > > First of all if I may introduce you to my > > > query:http://bin.cakephp.org/view/1064020598 > > > > It's a union query that allows me to search across two tables and > > > return the results, ordered by relevance. To put it simply I want to > > > paginate these results, but I haven't a clue how to do it. I tried, > > > and failed, over-riding the paginate and paginateCount methods in my > > > model, but I shan't even begin to post my long list of errors. > > > > Where should I start? > > > > Thanks! > > > > Dean --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "CakePHP" group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to cake-php+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
Re: Custom query pagination
Hi folks, I can't use a View for the union query as it requires parameters. Even if I take away the fulltext, and then do a fulltext search on the view, MySQL can't then delegate the indexes across the union within the view, so it doesn't work. Any more ideas or sample pieces of code would be appreciated :) Dean On Aug 28, 9:09 am, "Dr. Loboto" wrote: > If you can do view for your union query you will be able to work with > it as with normal table. > > On Aug 28, 5:58 am, Dean C wrote: > > > > > Hi folks, > > > Before I begin, yes I've read this > > page:http://book.cakephp.org/view/249/Custom-Query-Pagination > > > I've also read many of the other pages on Google but I'm still stuck! > > > First of all if I may introduce you to my > > query:http://bin.cakephp.org/view/1064020598 > > > It's a union query that allows me to search across two tables and > > return the results, ordered by relevance. To put it simply I want to > > paginate these results, but I haven't a clue how to do it. I tried, > > and failed, over-riding the paginate and paginateCount methods in my > > model, but I shan't even begin to post my long list of errors. > > > Where should I start? > > > Thanks! > > > Dean --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "CakePHP" group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to cake-php+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
Re: Custom query pagination
Hi,
This is haw I paginate and it works OK,
I have 3 join tables ( I can set different type of joins ),
Did you understand what is happening ?
function MyFunctionThatReturnPaginateArrayInfo {
code code code
...
return array(
'MortgageDetail' => array(
'limit'=> 10,
'fields' => array(
'id',
'mortgage_provider_id',
'name',
'mortgage_category_id',
.
.
'( (' . $initial_monthly_payment_formula . ' )/
' .
(float)$m_borrow/1000 . ' ) as cpt',
.
.
'name',
'specialoffer',
'summary',
'directlink',
'ads_link',
'MortgageProvider.name'
// 'UNIX_TIMESTAMP(createddate) as created,
UNIX_TIMESTAMP(updatedate) as updated'
),
'recursive' => -1,
'joins' => array(
array(
'table' => 'mortgage_categories',
'alias' => 'MortgageCategory',
'type' => 'LEFT',
'conditions' =>
array('MortgageDetail.mortgage_category_id =
MortgageCategory.id')
),
array(
'table' => 'mortgage_providers',
'alias' => 'MortgageProvider',
'type' => 'LEFT',
'conditions' =>
array('MortgageDetail.mortgage_provider_id =
MortgageProvider.id')
),
array(
'table' =>
'mortgage_providers_general_limits',
'alias' =>
'MortgageProvidersGeneralLimit',
'type' => 'INNER',
'conditions' =>
array('MortgageProvidersGeneralLimit.mortgage_provider_id =
MortgageProvider.id') ),
),
'conditions' =>
array("{$sWhereCond}"),
'order' => array('initialrate')
)
wrote:
>
> Hi folks,
>
> Before I begin, yes I've read this page:
> http://book.cakephp.org/view/249/Custom-Query-Pagination
>
> I've also read many of the other pages on Google but I'm still stuck!
>
> First of all if I may introduce you to my query:
> http://bin.cakephp.org/view/1064020598
>
> It's a union query that allows me to search across two tables and
> return the results, ordered by relevance. To put it simply I want to
> paginate these results, but I haven't a clue how to do it. I tried,
> and failed, over-riding the paginate and paginateCount methods in my
> model, but I shan't even begin to post my long list of errors.
>
> Where should I start?
>
> Thanks!
>
> Dean
>
> >
>
--
Paul-Cosmin Constandachi
Tel: 0723.46.46.42
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"CakePHP" group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to
cake-php+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
Re: Custom query pagination
If you can do view for your union query you will be able to work with it as with normal table. On Aug 28, 5:58 am, Dean C wrote: > Hi folks, > > Before I begin, yes I've read this > page:http://book.cakephp.org/view/249/Custom-Query-Pagination > > I've also read many of the other pages on Google but I'm still stuck! > > First of all if I may introduce you to my > query:http://bin.cakephp.org/view/1064020598 > > It's a union query that allows me to search across two tables and > return the results, ordered by relevance. To put it simply I want to > paginate these results, but I haven't a clue how to do it. I tried, > and failed, over-riding the paginate and paginateCount methods in my > model, but I shan't even begin to post my long list of errors. > > Where should I start? > > Thanks! > > Dean --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "CakePHP" group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to cake-php+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
Custom query pagination
Hi folks, Before I begin, yes I've read this page: http://book.cakephp.org/view/249/Custom-Query-Pagination I've also read many of the other pages on Google but I'm still stuck! First of all if I may introduce you to my query: http://bin.cakephp.org/view/1064020598 It's a union query that allows me to search across two tables and return the results, ordered by relevance. To put it simply I want to paginate these results, but I haven't a clue how to do it. I tried, and failed, over-riding the paginate and paginateCount methods in my model, but I shan't even begin to post my long list of errors. Where should I start? Thanks! Dean --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "CakePHP" group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to cake-php+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
Re: Custom Query Pagination
I also found that the group by value was correct for some paginate
calls and what I needed was the effected rows, I modified the
paginateCount method override as follows :-
function paginateCount($conditions = null, $recursive = 0, $extra = array()) {
$parameters = compact('conditions');
$this->recursive = $recursive;
$count = $this->find('count', array_merge($parameters, $extra));
if (isset($extra['group'])) {
$count = $this->getAffectedRows();
}
return $count;
}
Hope this helps.
Brett Wilton
http://wiltonsoftware.com
On Tue, Dec 9, 2008 at 10:34 AM, Brett Wilton <[EMAIL PROTECTED]> wrote:
> You need to override the pageCount() method with a custom count query,
> for a similar type of query to yours I used the following method in my
> model...
>
> function paginateCount($conditions = null, $recursive = 0, $extra = array()) {
>$parameters = compact('conditions');
>$this->recursive = $recursive;
>$count = $this->find('count', array_merge($parameters));
>return $count;
> }
>
> Essentially this is the same as the default count method but I ignore
> the extra field which contains the group by clause i.e. $extra is not
> passed to the find().
>
>
> Brett Wilton
> http://wiltonsoftware.com
>
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"CakePHP" group.
To post to this group, send email to cake-php@googlegroups.com
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
-~--~~~~--~~--~--~---
Re: Custom Query Pagination
You need to override the pageCount() method with a custom count query,
for a similar type of query to yours I used the following method in my
model...
function paginateCount($conditions = null, $recursive = 0, $extra = array()) {
$parameters = compact('conditions');
$this->recursive = $recursive;
$count = $this->find('count', array_merge($parameters));
return $count;
}
Essentially this is the same as the default count method but I ignore
the extra field which contains the group by clause i.e. $extra is not
passed to the find().
Brett Wilton
http://wiltonsoftware.com
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"CakePHP" group.
To post to this group, send email to cake-php@googlegroups.com
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
-~--~~~~--~~--~--~---
Custom Query Pagination
Hi all,
I have used a custom query with group by in pagination.
I got the the grouped output, but the record count is different (i.e.
I am getting the record-count as the total number of records without
grouping).
The format I have used for pagination is:
$this->paginate['Model'] =
array(
'conditions' => 'some condition',
'fields'=>array('user_id','type_id','country_id','COUNT(*)','SUM
(duration)','SUM(rate)'),
'group'=>array('User.office_id','country_id'),
'limit' => $this->paginate['limit']
);
One more thing is that, I am using the same Model for many pages.
Please give me a suggestion to fix this...
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"CakePHP" group.
To post to this group, send email to cake-php@googlegroups.com
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
-~--~~~~--~~--~--~---
