1

I'm working with cakePHP 3.7

I'd like to create a temporary table from a controller, and then use it (with some conditions and joins) to search some data to be displayed.

The table structure is for the moment statically defined, i.e. the fields it has are fixed, so I'm able to define an entity or a set of fields. The content, though, varies with the user input. This means I want to define the table with a prepared SQL statement, whose variables I will bind to user input.

I know how to handle prepared statements.

But I've not understood yet precisely what's the correct way to proceed, to let cakePHP create my temporary table and load a model for it.

I've found a useful answer by @ndm at CakePHP 3: Best Practice for Temporary SQL Tables and, in the tries I've made, I've specified the setTemporary(true) for the table.

Yet I keep getting "table doesn't exist" exceptions, so I suppose I'm not structuring the project classes and code correctly. I can't create the table, as @ndm suggests, by using queries got with

$schema->createSql($connection);

because the sql is dependent to the user input.

Other found stackoverflow answers are about older versions of cakePHP (2.x) which are different from 3.x

Could someone please give me some more detailed hints about which is the way to create and use temporary tables, created with prepared statements, from within my cakePHP project?

Ruth
  • 546
  • 1
  • 6
  • 12
  • I've both tried to create the temporary table in the table class itself, and to create it from the controller method before loading the model – Ruth Aug 27 '19 at 07:17

1 Answers1

0

Solved this way:

  1. I've created a table in the database corresponding to my desired temporary table
  2. I've run the cake bake model command to let cakePHP generate the entity and the table class
  3. I've dropped the table from the DB

  4. in the controller:

    4.a I've prepared and executed the statement to create the temporary table

    4.b I've loaded the model and used it

The issue I had was (what a shame!) a typographical error, because at a certain moment in a join query I used the name of the model instead of the name of the table in the conditions array.

I.e. I was asking wrongly for: 

->join([
                    'table' => 'tempLogs',
                    'type' => 'INNER',
                    'conditions' => 'tempLogs.Job = Istanze.Job',
                ])

instead of the correct:

->join([
                    'table' => 'temp_logs',
                    'type' => 'INNER',
                    'conditions' => 'temp_logs.Job = Istanze.Job',
                ])

temp_logs is the temporary table name, TempLogs is the name of the table class

Ruth
  • 546
  • 1
  • 6
  • 12