Multiple databases in Editor

swimitup

It was said that the notation database.table.field would work in Editor, but that's not true.
Thread: https://datatables.net/forums/discussion/74975/multiple-databases-in-a-single-table

The following code:

Editor::inst( $db, 'database1' )
    ->field(
        Field::inst( 'database1.table1.id' ),
        Field::inst( 'database1.table1.name' )
            ->validator( Validate::notEmpty( ValidateOptions::inst()
                ->message( 'Cant be empty' )
            ) ),
        Field::inst( 'database1.table1.entity')
            ->options(
                Options::inst()
                ->table('database2.entities')
                ->value('id')
                ->label('entity_name')
            )
            ->validator(Validate::dbValues()),
        Field::inst( 'database2.entities.entity_name')
    )
    ->leftJoin( 'database2.entities', 'database1.table1.entity', '=', 'database2.entities.id' )
    ->debug(true)
    ->process($_POST)
    ->json();

Will not work. An alert box will be displayed saying "Unknown parameter database2.entities.entity_name".

The db instance is MySQL, and of course it is only connecting to database1 - although the user has privileges to read database2:

$sql_details = array(
     "type" => "Mysql",    // Database type: "Mysql", "Postgres", "Sqlite" or "Sqlserver"
     "user" => MYSQLUSER,    // User name
     "pass" => MYSQLPASSWORD,    // Password
     "host" => MYSQLHOST,    // Database server
     "port" => "3306",    // Database port (can be left empty for default)
     "db"   => MYSQLBANCODEDADOS,    // Database name
     "dsn"  => "charset=utf8"     // PHP DSN extra information. Set as `charset=utf8` if you are using MySQL
 );

Debugging shows the DT_Row object with the database showing as table, and the table showing as a field:

{
  "data": [
    {
      "DT_RowId": "row_1",
      "table1": {
        "id": "1",
        "name": "10/10/2024",
        "entity": null
      },
      "database2": { "entities": { "entity_name": null } }
    }
  ],
  "options": {
  },
  "files": [],
  "debug": [
    {
      "query": "SELECT  `table1`.`id` as 'table1.id', `table1`.`name` as 'table1.name', `table1`.`entity` as 'table1.entity', `database2`.`entities`.`entity` as 'database2.entities.entity' FROM  `table1` LEFT JOIN `database2`.`entities` ON `table1`.`entity` = `database2`.`entities`.`id` ",
      "bindings": []
    },
    {
      "query": "SELECT DISTINCT  `id` as 'id', `entity_name` as 'entity_name' FROM  `database2`.`entities` ",
      "bindings": []
    }
  ]
}

Any ideas?

allan

Thanks for the JSON response with the SQL. That shows that the SQL does execute correctly. What appears to be happening is that the value is null and unless you tell DataTables what to do with a null value, it will throw this error.

Do you have data: 'database2.entities.entity_name' for one of your DataTables Javascript columns? If so, make sure to add columns.defaultContent as well - that will tell DataTables what to show the end user if the value is null. Normally it would just be set to be an empty string:

columns: [
  {
    data: 'database2.entities.entity_name',
    defaultContent: ''
  }
]

Allan