Pagnation with multiple Models

[Johan @ Notitia.nl]

Hi,

I'm building a search controller which has to retrieve data from
multiple tabels.
Normaly that wouldn't be a problem if you have set the correct
association at the models.
The big problem in this case is the fact that I can't change or alter
the tables. This means I have to create some weird SQL (multi table,
multi join, etc) to retrieve all the data.

Only now, with the custom SQL, I can't use the pagination function
inside CakePHP 1.2 and I need it because I don't like the fact that it
renders a list of +2500 items at once :s

I already tried to create a   var $paginate = array( ... );   but it
doesn't support multiple tables (because Model::find only supports 1
table)
Even when I add all the Models to the Controller or add all the Models
inside one Model (dummy) it doesn't work at all and keeps returning an
error that it can't find the right table at the SQL. And that is
because the tables aren't included at the FROM (only the first model).

This is my current Controller:

?php
class ZoekenController extends AppController {

var $name = 'Zoeken';
var $uses = array('Occasion', 'Merken', 'Modellen', 'MerkModel');
var $helpers = array('Html', 'Form');
var $paginate = array(
'conditions' = array(
#   
'Occasion.merk_modelID' = 'MerkModel.merk_modelID',
#   
'MerkModel.merkID' = 'Merken.merkID',
#   
'MerkModel.modelID' = 'Modellen.modelID',
#   
'merken.actief' = 1,
#   
'Modellen.actief' = 1,

'Occasion.actief' = 1
),
'fields' = array(

'Occasion.occasionID',

'Occasion.gebruikerID',
#   
'merken.merkID',
#   
'merken.naam',
#   
'merken.code',
#   
'merken.logo',
#   
'Modellen.modelID',
#   
'Modellen.naam',

'Occasion.merk_modelID',

'Occasion.postcode_standplaats',

'Occasion.extra_opvallen',

'Occasion.kilometerstand',

'Occasion.bouwmaand',

'Occasion.prijs'

),
'order' = 
'Occasion.occasionID',
'limit' = 15,
'page' = 1
);

function index() {
if (!empty($this-data)) {

#   $this-data['merk'] == 0 || 
$this-paginate['conditions']
['MerkModel.merkID'] = $this-data['merk'];
#   $this-data['model'] == 0 || 
$this-paginate['conditions']
['MerkModel.modelID'] = $this-data['model'];
$this-data['minprijs'] == 0 || 
$this-paginate['conditions']
['Occasion.prijs'] = '= '.$this-data['minprijs'];
$this-data['maxprijs'] == 0 || 
$this-paginate['conditions']
['Occasion.prijs'] = '= '.$this-data['maxprijs'];

$data = $this-paginate();
$this-set('results', $data);
}
/* more functions and stuff */
}
}
?

The above script works, but when I remove one of the # it gives 

Re: Pagnation with multiple Models

[Marcin Domanski aka kabturek]

Hey,
First of all - code in English ;) that comes handy when someone wants
to read and understand your code.
In you case i would do a dummy model and include your code there. When
you look in the paginate() mthod body ( you did you homework, right ?)
you will see that it uses the custom paginate() and paginateCount()
functions in you model - use them ;)

Apart from that - you should think about some more advanced search
libs like Zend_Lucene (bakery article) or Sphinx (http://
www.sphinxsearch.com/) etc

And a self-promotion part:
http://wiki.kabturek.info/paginating_search_results

HTH,
Marcin Domanski aka kabturek

On Jan 11, 3:05 pm, Johan @ Notitia.nl [EMAIL PROTECTED] wrote:
 Hi,

 I'm building a search controller which has to retrieve data from
 multiple tabels.
 Normaly that wouldn't be a problem if you have set the correct
 association at the models.
 The big problem in this case is the fact that I can't change or alter
 the tables. This means I have to create some weird SQL (multi table,
 multi join, etc) to retrieve all the data.

 Only now, with the custom SQL, I can't use the pagination function
 inside CakePHP 1.2 and I need it because I don't like the fact that it
 renders a list of +2500 items at once :s

 I already tried to create a   var $paginate = array( ... );   but it
 doesn't support multiple tables (because Model::find only supports 1
 table)
 Even when I add all the Models to the Controller or add all the Models
 inside one Model (dummy) it doesn't work at all and keeps returning an
 error that it can't find the right table at the SQL. And that is
 because the tables aren't included at the FROM (only the first model).

 This is my current Controller:

 ?php
 class ZoekenController extends AppController {

 var $name = 'Zoeken';
 var $uses = array('Occasion', 'Merken', 'Modellen', 'MerkModel');
 var $helpers = array('Html', 'Form');
 var $paginate = array(
 'conditions' = array(
 # 
   'Occasion.merk_modelID' = 'MerkModel.merk_modelID',
 # 
   'MerkModel.merkID' = 'Merken.merkID',
 # 
   'MerkModel.modelID' = 'Modellen.modelID',
 # 
   'merken.actief' = 1,
 # 
   'Modellen.actief' = 1,
   
   'Occasion.actief' = 1
 ),
 'fields' = array(
   
   'Occasion.occasionID',
   
   'Occasion.gebruikerID',
 # 
   'merken.merkID',
 # 
   'merken.naam',
 # 
   'merken.code',
 # 
   'merken.logo',
 # 
   'Modellen.modelID',
 # 
   'Modellen.naam',
   
   'Occasion.merk_modelID',
   
   'Occasion.postcode_standplaats',
   
   'Occasion.extra_opvallen',
   
   'Occasion.kilometerstand',
   
   'Occasion.bouwmaand',
   
   'Occasion.prijs'

 ),
 'order' = 
 'Occasion.occasionID',
 'limit' = 15,
 'page' = 1
 );

 function index() {
 if