CakePHP: create temporary table and use it

Question

I'm trying to create a temporary table within CakePHP 2.x, but I always receive the message "Error: Call to a member function query() on a non-object".

I did some research and found a solution, but this one is not working for me: Create temporary table in CakePHP and load it as a Model

EDIT: The following code now produces a different error: "Error: Table devices_cls for model DevicesCl was not found in datasource default."

Here is my code:

class DevicesController extends AppController {

public $uses = array('Device','Client');

public function index(){
    $conditions = array();

    $tmpModel = 'DevicesCl';
    $tmpTable = 'devices_cls';

    $this->loadModel('DevicesCl');
    $this->DevicesCl->query("CREATE TEMPORARY TABLE IF NOT EXISTS devices_cls AS (SELECT uuid,ownerUuid,status,os,imei,updatedOn,msisdn,model FROM device)" );

As I'm quite new to CakePHP, do I need to add an additional model class? I don't think so, as this should be handled by the temporary table - right?

Thanks for your support!

yes, it is mysql. btw, the anser from MouradK solve the problem — rolando, Nov 24 '14 at 07:52

score 2 · Accepted Answer · answered Nov 21 '14 at 13:51

2

class DevicesController extends AppController {

public $uses = array('Device','Client');

public function index(){
    $conditions = array();

    $tmpModel = 'DevicesCl';
    $tmpTable = 'devices_cls';

    $this->loadModel('DevicesCl');
    $this->DevicesCl->useTable=false;
    $this->DevicesCl->query("CREATE TEMPORARY TABLE IF NOT EXISTS devices_cls AS (SELECT uuid,ownerUuid,status,os,imei,updatedOn,msisdn,model FROM device)" );
    $this->DevicesCl->useTable = $tmpTable;

answered Nov 21 '14 at 13:51

MouradK

1,527
1
11
14

with useTable = false => tell to cakephp that DevicesCL has not table, and after with useTable = tableName, it tell which is used – MouradK Nov 21 '14 at 13:52
it was exactly the problem with the temp table -> "useTable", now running as expected - thanks for your support – rolando Nov 24 '14 at 07:55
a final note: saving data to the temp table was not possible, I had to create a model class, disabling the database model check -> http://web2.0goodies.com/blog/uncategorized/mysql-temporary-tables-and-cakephp-1-3/ – rolando Nov 24 '14 at 11:02

score 0 · Answer 2 · answered Nov 21 '14 at 11:03

0

The problem is that the Model "DevicesCl" does not exist yet.

in your example on the link : the Model exist

so if you change

$this->DevicesCl->query(...)

to

$this->Model->query(...)

But i don t see why you need a functionnality like that... i think there are surely best solutions no ?

answered Nov 21 '14 at 11:03

MouradK

1,527
1
11
14

No, I still have the same error when changing DevicesCl to Model. Regarding better solutions: I will use this temp table to add other data from another table later in the code for sorting and paginate, I didn't found a better solutions – rolando Nov 21 '14 at 11:17
ok, thanks, this already brings me forward, but to a new problem: I adapted the code to: $this->loadModel($tmpModel); $this->DevicesCl->query("CREATE TEMPORARY TABLE IF NOT EXISTS devices_cls ... but not I receive: "Error: Table devices_cls for model DevicesCl was not found in datasource default." So it seems temp table will not be found - any idea? – rolando Nov 21 '14 at 11:24
can you edit your post and post the new PHP please :D – MouradK Nov 21 '14 at 11:26
the problem is : you call a Model which don t have yet a table, so you had to create your table and after call the model. Use $this->Model->query (Model not DevicesCl) and $this->loadModel('Model'); – MouradK Nov 21 '14 at 11:42
This change resolves in: "Error: Table models for model Model was not found in datasource default." – rolando Nov 21 '14 at 11:47

score 0 · Answer 3 · answered Jul 11 '19 at 10:21

In Cakephp 3, you can do the following:

Model/Table/MyTempTable.php
<?php
// ......

class MyTempTable extends Table{

    public function initDownload(){

        $connection = ConnectionManager::get('default');

        $connection->execute('CREATE TEMPORARY TABLE temptemp as (select * from refernece_db)');
        // Do your thing
    }
}


// When use the temp table, first should do: 
$this->MyTempTable->initDownload();

// Then, can use Cakephp3 Table syntax normally as if it is a normal table

CakePHP: create temporary table and use it

3 Answers3

Linked