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

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

LoveCodeLoveCode Posts: 7Questions: 1Answers: 0

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

Answers

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    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

  • LoveCodeLoveCode Posts: 7Questions: 1Answers: 0

    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.

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    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

  • LoveCodeLoveCode Posts: 7Questions: 1Answers: 0

    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.

This discussion has been closed.