Cakephp pagination using temporary table

Question

I have created a temporary table using cakephp $this->Model->query() and inserted some records using the same $this->Model->query(). I want to paginate the temporary table. I have loaded the temporary model as follows.

App::import('Model', $tmpModel);
$this->loadModel($tmpModel);

Ref Url: Create temporary table in CakePHP and load it as a Model

Unfortunately the pagination did not happen and i got the following error.

Database table "table name" for model "model name" was not found. Though the table name and model name were written as per the cakephp convention.

What might be the possible solution for it.

score 1 · Accepted Answer · answered Nov 17 '11 at 13:51

I'm almost certain that you won't be able to do this using temporary tables. This is because's Cake's underlying schema caching uses MySql's 'SHOW TABLES' which doesn't return temporary tables - hence the reason you'll always get the "Database table "table name" for model "model name" was not found"

My suggestion therefore would be to create a standard table and then drop it once you're finished. Here is a example that could be call from a 'Users' controller:

// Define temporary model
$tempModel = 'TempUser';

// Create table
$this->User->query("CREATE TABLE IF NOT EXISTS `". Inflector::tableize($tempModel)  ."` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL DEFAULT '',
  `email` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;");

// load the model
$this->loadModel($tempModel);

/**
 * Prevent caching of database schema and then get
 * the latest schema that includes our new table
 */
$this->$tempModel->cacheSources = false;
$this->$tempModel->schema();

// Save
$this->$tempModel->save(array(
  $tempModel => array(
    'name' => 'Testing',
    'email' => 'joe.bloggs@google.co.uk'
  )
));

// Paginate
$data = $this->paginate($tempModel);

// Drop Table
$this->User->query("DROP TABLE `". Inflector::tableize($tempModel)  ."`");

What is the impact in terms of performance on creating and then dropping a table for each query of that type? Would this method scale well for multiple users at the same time? — aviemet, Nov 29 '13 at 06:48

score 0 · Answer 2 · edited May 23 '17 at 12:12

It is possible to have pagination work, the 'secret' is to create a model for the temporary table and override the 'setSource' function in the Model to not check the tablename against a cached list of tables (which was created much earlier).

Please see my answer with more details here: CakePHP can't find table after creating a table

Many thanks to whomever posted http://web2.0goodies.com/blog/uncategorized/mysql-temporary-tables-and-cakephp-1-3/ for the clue.

Note that this method also works for non-temporary tables created on the fly.

score 0 · Answer 3 · answered Nov 17 '11 at 12:44

I sugest to insert the records with save after you created the tmp model.

The problem here is the cache, try removing the cache and it might work.

to prevent cake to cache your queries you may use cacheQueries = false; i haven't tried this last thing though but you cache is the problem since it cache the current schema (without temp table) and then when it does exist it can't find it since it is using a cache :S

Cakephp pagination using temporary table

3 Answers3