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!

Answer 1

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;

Answer 2

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 ?

Answer 3

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