Issue with duplication of row (with select option and leftJoin)

Mateo09

Hi,

I use Datatables and Editor to show some columns of a table of my database. I allow some options for the user like editing, duplication, delet, column visibility (colvis), ... All works great, it's almost finished but I have one problem with the duplication option since I have added leftJoin for my Datatable (it worked before). Here is my php script :

Editor::inst($db, 'backup_ref.backup_parameters as bp', 'id')
    ->fields(
        Field::inst('bp.id')->set(false),
        Field::inst('bp.client_name')
            ->options(
                Options::inst()
                    ->table('public.client')
                    ->value('name')
                    ->label('name')
            )
            ->validator('Validate::dbValues'),
        Field::inst('bp.backup_type')
            ->validator(Validate::notEmpty(
                ValidateOptions::inst()
                //->message('A backup type is required')
            )),
       ...
        Field::inst('bp.fileset_name')
            ->options(
                Options::inst()
                    ->table('public.fileset')
                    ->value('fileset')
                    ->label('fileset')
            )
         ...
    )
    ->leftJoin('public.client as pc', 'pc.name = bp.client_name')
    ->leftJoin('public.fileset as pf', 'pf.fileset = bp.fileset_name')
    ->debug(true)
    ->process($_POST)
    ->json();

In the javascript file where I initialize my Datatable and my Editor, I obviously put the "type:select" field.
But why I do a leftJoin ? Because client_name and fileset_name have to exist in the catalog which is in the schema public. The user cannot therefore add one of these two parameters without it existing in the catalog. Hence the drop-down list presenting the options available in the catalog. For this it's okay, I have the dropdown with the value.

The problem is that when I duplicate one or more row, I have this error :
SQLSTATE[42602]: Invalid name: 7 ERREUR: syntaxe du nom invalideLINE 6: pg_class.oid = '"backup_ref"."backup_parameters...^

For the debug, I have the two query for client_name and fileset_name :
Object { query: "SELECT \"name\" as \"name\" FROM \"public\".\"client\" WHERE \"name\" = :where_0 ", bindings: […] }
Object { query: "SELECT \"fileset\" as \"fileset\" FROM \"public\".\"fileset\" WHERE \"fileset\" = :where_0 ", bindings: […] }
with the corresponding bindings.

Finally, I have also the query for the insert :
Object { query: "INSERT INTO \"backup_ref\".\"backup_parameters\" ( \"client_name\", \"backup_type\", \"object_type\", \"object_name\", \"job_level\", \"job_name\", \"fileset_name\", \"job_type\", \"pool_name\" ) VALUES ( :client_name, :backup_type, :object_type, :object_name, :job_level, :job_name, :fileset_name, :job_type, :pool_name )", bindings: […] }
and yet the corresponding bindings.

I didn't understand where is the problem knowing that the modification of one or more row works perfectly.

I think I put the right information in the editor and datatables declaration in the js, example for the fields client_name in editor :

fields: [
            {
                label: 'client_name:',
                name: 'bp.client_name',
                type: 'select',
            },
            ...

and the datatables :

columns: [
            { data: 'bp.id' },
            { data: 'bp.client_name' },
            ...

Thanks in advance if you have any idea of the problem.

Matéo

allan

HI Matéo,

Can you show me the full JSON response for the Ajax request that makes the duplicate request please? It would also be useful to be able to see the parameters that are being sent to the server as part of the request.

Thanks,
Allan

Mateo09

Hi allan,

Of course, here is the entire JSON with the parameters sent :

{
  "fieldErrors": [],
  "error": "SQLSTATE[42602]: Invalid name: 7 ERREUR:  syntaxe du nom invalide\nLINE 6:      pg_class.oid = '\"backup_ref\".\"backup_parameters...\n                            ^",
  "data": [],
  "ipOpts": [],
  "cancelled": [],
  "debug": [
    "Editor PHP libraries - version 2.2.2",
    {
      "query": "SELECT  \"name\" as \"name\" FROM  \"public\".\"client\" WHERE \"name\" = :where_0 ",
      "bindings": [
        {
          "name": ":where_0",
          "value": "bkpclg01.int.dns",
          "type": null
        }
      ]
    },
    {
      "query": "SELECT  \"fileset\" as \"fileset\" FROM  \"public\".\"fileset\" WHERE \"fileset\" = :where_0 ",
      "bindings": [
        {
          "name": ":where_0",
          "value": "Fset_DC1_UATORA01_ORACLE_CIBLPRE1_DB",
          "type": null
        }
      ]
    },
    {
      "query": "INSERT INTO  \"backup_ref\".\"backup_parameters\"  ( \"client_name\", \"backup_type\", \"object_type\", \"object_name\", \"job_level\", \"job_name\", \"fileset_name\", \"job_type\", \"pool_name\" ) VALUES (  :client_name,  :backup_type,  :object_type,  :object_name,  :job_level,  :job_name,  :fileset_name,  :job_type,  :pool_name )",
      "bindings": [
        {
          "name": ":client_name",
          "value": "bkpclg01.int.dns",
          "type": null
        },
        {
          "name": ":backup_type",
          "value": "EXCHANGEE",
          "type": null
        },
        {
          "name": ":object_type",
          "value": "ADPROD",
          "type": null
        },
        {
          "name": ":object_name",
          "value": "ADMEXC01",
          "type": null
        },
        {
          "name": ":job_level",
          "value": "COLD",
          "type": null
        },
        {
          "name": ":job_name",
          "value": "DC1_DEV_EXCHANGE_DEVEXC02",
          "type": null
        },
        {
          "name": ":fileset_name",
          "value": "Fset_DC1_UATORA01_ORACLE_CIBLPRE1_DB",
          "type": null
        },
        {
          "name": ":job_type",
          "value": "B",
          "type": null
        },
        {
          "name": ":pool_name",
          "value": "POOL_DC1_PROBCS02_DEDUP_1A_REP_DC2_PROBCS01",
          "type": null
        }
      ]
    }
  ]
}

Thanks,
Matéo

allan

Hi Matéo,

Many thanks. Do you have a trigger on the backup_parameters table perhaps? The queries there don't contain the line that the error message is indicating as the issue:

pg_class.oid = '\"backup_ref\".\"backup_parameters

I don't think we use pg_class.oid at all in fact.

Allan

Mateo09

Hi allan,

Sorry for the late reply, I was on weekend. I don't have any kind of trigger on my table. Moreover, backup_ref is the schema of the backup_parameters table for information.
What is strange is that the duplication works very well without the leftJoin calls.
I noticed that when I removed the joins as well as the backup_referential.backup_parameters as bp line, the duplication worked well (it was especially when I removed "bp" that the duplication worked).

Matéo

allan

I was wrong - we do use it. Apologies! That's the code to attempt to get the primary key name from a table when doing an insert, so we can make sure to get the value back from it.

I've just committed a change to how that query works, and I wonder if you could replace the contents of that file in your install with the new contents please? Hopefully that will help.

Thanks,
Allan

Mateo09

I made the modifications for this file but unfortunately it does not work (at least for me). It is surprising. I also tried the query directly in the database and it works correctly. Here is the JSOn response (which is similar to the previous one):

{
  "fieldErrors": [],
  "error": "SQLSTATE[42602]: Invalid name: 7 ERREUR:  syntaxe du nom invalide",
  "data": [],
  "ipOpts": [],
  "cancelled": [],
  "debug": [
    "Editor PHP libraries - version 2.2.2",
    {
      "query": "SELECT  \"name\" as \"name\" FROM  \"public\".\"client\" WHERE \"name\" = :where_0 ",
      "bindings": [
        {
          "name": ":where_0",
          "value": "devexc02.adtest.int.dns",
          "type": null
        }
      ]
    },
    {
      "query": "SELECT  \"fileset\" as \"fileset\" FROM  \"public\".\"fileset\" WHERE \"fileset\" = :where_0 ",
      "bindings": [
        {
          "name": ":where_0",
          "value": "Test_Fset_CONTROLEUR_AD",
          "type": null
        }
      ]
    },
    {
      "query": "INSERT INTO  \"backup_ref\".\"backup_parameters\"  ( \"client_name\", \"backup_type\", \"object_type\", \"object_name\", \"job_level\", \"job_name\", \"fileset_name\", \"job_type\", \"pool_name\" ) VALUES (  :client_name,  :backup_type,  :object_type,  :object_name,  :job_level,  :job_name,  :fileset_name,  :job_type,  :pool_name )",
      "bindings": [
        {
          "name": ":client_name",
          "value": "devexc02.adtest.int.dns",
          "type": null
        },
        {
          "name": ":backup_type",
          "value": "EXCHANGE",
          "type": null
        },
        {
          "name": ":object_type",
          "value": "ADPROD",
          "type": null
        },
        {
          "name": ":object_name",
          "value": "DEVEXC02",
          "type": null
        },
        {
          "name": ":job_level",
          "value": "FULL",
          "type": null
        },
        {
          "name": ":job_name",
          "value": "DC4_DEV_EXCHANGE_DEVEXC02",
          "type": null
        },
        {
          "name": ":fileset_name",
          "value": "Test_Fset_CONTROLEUR_AD",
          "type": null
        },
        {
          "name": ":job_type",
          "value": "B",
          "type": null
        },
        {
          "name": ":pool_name",
          "value": "POOL_DC4_PROBCS08_DEDUP_1A_NOREP",
          "type": null
        }
      ]
    }
  ]
}

Matéo

allan

That is surprising! I'm not sure what is different about my system. I do have one more "trick" up my sleeve though - in that same file, replace

if ($this->_type === 'insert') {

with:

if ($this->_type === 'insert' && count($pkey)) {
  $sql .= ' RETURNING ' . $pkey[0] . ' as dt_pkey';
}
else if ($this->_type === 'insert') {

That will bypass that query to get the primary key altogether. We don't need it for your table since we know the primary key is called id.

Allan

Mateo09

Unfortunately, it doesn't work as well. After this bad new, I've a good new. I found the problem !
After a good night's sleep, I had an intuition, I tried deleting the alias for my table :

Editor::inst($db, 'backup_ref.backup_parameters as bp')
    ->fields(
        Field::inst('bp.id')->set(false),
        Field::inst('bp.client_name')
            ->options(
                Options::inst()
                    ->table('public.client')
                    ->value('name')
                    ->label('name')
            )
            ->validator('Validate::dbValues'),
           ...

To :

Editor::inst($db, 'backup_ref.backup_parameters')
    ->fields(
        Field::inst('backup_ref.backup_parameters.id')->set(false),
        Field::inst('backup_ref.backup_parameters.client_name')
            ->options(
                Options::inst()
                    ->table('public.client')
                    ->value('name')
                    ->label('name')
            )
            ->validator('Validate::dbValues'),
           ...

Now, the duplication work perfectly !

In any case, thank you very much for the time you gave me allan.

Matéo

allan

Hi Matéo,

Good to hear that resolved the issue for you. I'm going to look into this more though - it should work! You won't be the last to stumble across this...! Thanks for flagging it up.

Allan