"Join tables - one-to-many join" with error

LoveCode

I'm tring "Join tables - one-to-many join" https://editor.datatables.net/examples/advanced/joinArray.html but when I replicate it in my source I recived :
An SQL error occurred: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'permission' in 'field list'

The Database:

CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE `user_permission` (
  `user_id` int(11) NOT NULL,
  `permission_id` int(11) NOT NULL,
  PRIMARY KEY (`user_id`,`permission_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `permission` (
  `id` int(11) NOT NULL,
  `name` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `permission` (`id`,`name`) VALUES (1,'Permission 1');
INSERT INTO `permission` (`id`,`name`) VALUES (2,'Permission 2');

INSERT INTO `users` (`id`,`name`) VALUES (1,'Roberto');
INSERT INTO `users` (`id`,`name`) VALUES (2,'Alicja');

INSERT INTO `user_permission` (`user_id`,`permission_id`) VALUES (5,1);
INSERT INTO `user_permission` (`user_id`,`permission_id`) VALUES (5,2);

PHP Server testEditor.php:

Editor::inst(parent::getDataTablesEditorDatabase(), 'users')
    ->field(
            Field::inst('users.name')
    )
    ->join(
            Mjoin::inst('permission')
            ->link('users.id', 'user_permission.user_id')
            ->link('permission.id', 'user_permission.permission_id')
            ->order('name asc')
            ->fields(
                    Field::inst('id')
                    ->validator(Validate::required())
                    ->options(Options::inst()
                            ->table('permission')
                            ->value('id')
                            ->label('name')
                    ),
                    Field::inst('name')
            )
    )
    ->process($_POST)
    ->json();

When I call testEditor.php the response is:

{

    "data":[
        {
            "DT_RowId":"row_4",
            "users":{
                "name":"Roberto"
            },
            "permission":[
            ]
        },
        {
            "DT_RowId":"row_5",
            "users":{
                "name":"Alicja"
            },
            "permission":[
                {
                    "id":"1",
                    "name":"Permission 1"
                },
                {
                    "id":"2",
                    "name":"Permission 2"
                }
            ]
        }
    ],
    "options":{
        "permission[].id":[
            {
                "label":"Permission 1",
                "value":"1"
            },
            {
                "label":"Permission 2",
                "value":"2"
            }
        ]
    },
    "files":[
    ]

}{"fieldErrors":[],"error":"An SQL error occurred: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'permission' in 'field list'","data":[],"ipOpts":[],"cancelled":[]}

The first JSON response is OK but there is a second part with an important error that I don't understand.

I'm wrong same think in PHP or Database structure ?
Thanks in Adavance
Roberto

allan

Hi Roberto,

Could you change ->process($_POST) to be ->debug(true)->process($_POST) and then show me the JSON response from the server with the error?

Could you also show me your Editor Javascript code please?

Thanks,
Allan

LoveCode

Hi Allan,
this the response with debug:

{"data":[{"DT_RowId":"row_4","users":{"name":"Roberto"},"permission":[]},{"DT_RowId":"row_5","users":{"name":"Alicja"},"permission":[{"id":"1","name":"Permission 1"},{"id":"2","name":"Permission 2"}]}],"options":{"permission[].id":[{"label":"Permission 1","value":"1"},{"label":"Permission 2","value":"2"}]},"files":[],"debug":[{"query":"SELECT `id` as 'id', `users`.`name` as 'users.name' FROM `users` ","bindings":[]},{"query":"SELECT DISTINCT `users`.`id` as 'dteditor_pkey', `permission`.`id` as 'id', `permission`.`name` as 'name' FROM users as users JOIN `user_permission` ON `users`.`id` = `user_permission`.`user_id` JOIN `permission` ON `permission`.`id` = `user_permission`.`permission_id` ORDER BY `name` asc ","bindings":[]},{"query":"SELECT DISTINCT `id` as 'id', `name` as 'name' FROM `permission` ","bindings":[]}]}{"fieldErrors":[],"error":"An SQL error occurred: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'permission' in 'field list'","data":[],"ipOpts":[],"cancelled":[]}

I try now al three query in debug session an all return data without error.

I call directly the PHPEditor url without javascript for testing. I think there is somethink wrong in PHP or SQL structures.

In any case the generated HTML by my Laravel app is :

editor = new $.fn.dataTable.Editor({
    ajax: "ajax/tableEditor?tableName=users",
    table: "#elementsTable",
    fields: [
        {
            label: "Name:",
            name: "users.name", attr: {
            }
        },
        {
            label: "Permission:",
            name: "permission", 
            type: "checkbox", 
            attr: {
            }
        },
    ],
    formOptions: {
        main: {
            onEsc: false,
            onBackground: false
        }
    }
});

Thanks Allan.

allan

There is something odd going on there. That's actually two lots of JSON - the first one is the data being loaded, and then the second one is:

{
    "fieldErrors": [],
    "error": "An SQL error occurred: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'permission' in 'field list'",
    "data": [],
    "ipOpts": [],
    "cancelled": []
}

which is the one that is throwing the error, but doesn't have any debug information.

Are you using a different ajax option for Editor than from DataTables perhaps? And the debug option wasn't enabled for the Editor one?

Thanks,
Allan

LoveCode

Thanks Allan,Resolved.
There was a bug in my Laravel Controller that call two PHP Editor on the same http request.
The Datatables and Editor work well.
Thanks your for support and for excellent product.