Issue with Mjoin

Issue with Mjoin

Ellisphere_user1Ellisphere_user1 Posts: 16Questions: 1Answers: 0
edited October 2023 in Editor

Hi,

Actually, I use the join because I want to have in my datatable all columns of the user table and an additionally column role (which show all the role gave to each user). The objective is to manage user and also give/remove one or many role.

Here is tables that I use :

user :  
+--------+-----------+--------------+---------+---------+
| userid | username  | fullname     | comment | enabled |
+========+===========+==============+=========+=========+
| 1      | da_john   | John Lewis   | null    | true    |
+--------+-----------+--------------+---------+---------+
| 2      | da_victor | Victor Wamby | null    | true    |
+--------+-----------+--------------+---------+---------+

role : 
+--------+--------+---------+
| roleid | name   | comment |
+========+========+=========+
| 2      | Admin  | null    |
+--------+--------+---------+
| 3      | Writer | null    |
+--------+--------+---------+

role_attribution : 
+--------+--------+
| userid | roleid |
+========+========+
| 1      | 2      |
+--------+--------+
| 2      | 2      |
+--------+--------+

But I here is the error that I canno't resolve : DataTables warning: table id=datatable_user - Join was performed on the field 'user' which was not included in the Editor field list. The join field must be included as a regular field in the Editor instance.

Here is my php script :

    Editor::inst($db, 'backup_referential.user', 'backup_referential.user.userid')
        ->field(
            Field::inst('backup_referential.user.userid')
                ->get($_SESSION['read'])
                ->set(false),
            Field::inst('backup_referential.user.username')
                ->get($_SESSION['read'])
                ->set($_SESSION['editing_user'])
                ->validator(Validate::notEmpty(ValidateOptions::inst()))
                ->validator(Validate::maxLen(50))
                ->validator(Validate::noTags(ValidateOptions::inst()))
                ->validator(Validate::xss(ValidateOptions::inst())),
            Field::inst('backup_referential.user.fullname')
                ->get($_SESSION['read'])
                ->set($_SESSION['editing_user'])
                ->validator(Validate::notEmpty(ValidateOptions::inst()))
                ->validator(Validate::maxLen(50))
                ->validator(Validate::noTags(ValidateOptions::inst()))
                ->validator(Validate::xss(ValidateOptions::inst())),
            Field::inst('backup_referential.user.comment')
                ->get($_SESSION['read'])
                ->set($_SESSION['editing_user'])
                ->setFormatter(Format::ifEmpty(null))
                ->validator(Validate::noTags(ValidateOptions::inst()))
                ->validator(Validate::xss(ValidateOptions::inst())),
            Field::inst('backup_referential.user.enabled')
                ->get($_SESSION['read'])
                ->set($_SESSION['editing_user'])
                ->setFormatter(function ($val, $data, $opts) {
                    return !$val ? 0 : 1;
                })
        )
        ->join(
            Mjoin::inst('backup_referential.role')
                ->link('backup_referential.user.userid', 'backup_referential.role_attribution.userid')
                ->link('backup_referential.role.roleid', 'backup_referential.role_attribution.roleid')
                ->order('backup_referential.role.name asc')
                ->fields(
                    Field::inst('roleid')
                        ->validator(Validate::required())
                        ->options('backup_referential.role', 'roleid', 'name'),
                    Field::inst('name')
                )
        )
        ->debug(true)
        ->process($_POST)
        ->json();

And here the javascript :

var editor = new DataTable.Editor({
        ajax: '../../controllers/editor/editor_user.php',
        fields: [
            {
                label: 'Username:',
                name: 'backup_referential.user.username'
            },
            {
                label: 'Fullname:',
                name: 'backup_referential.user.fullname'
            },
            {
                label: 'Commentaires:',
                name: 'backup_referential.user.comment'
            },
            {
                label: 'Roles:',
                name: 'backup_referential.role[].id',
                type: 'select',
                multiple: true
            },
            {
                label: 'Enabled:',
                name: 'backup_referential.user.enabled',
                type: 'checkbox',
                separator: '|',
                options: [{ label: '', value: 1 }]
            }
        ],
        table: '#datatable_user'
    });

    var table = $("#datatable_user").DataTable({
        language: {
            url: "assets/language/fr-FR.json"
        },
        ajax: {
            url: '../../controllers/editor/editor_user.php',
            type: 'POST'
        },
        pageLength: 5,
        lengthMenu: [[5, 10, 20, 50, -1], [5, 10, 20, 50, "Tous"]],
        scrollCollapse: true,
        scrollY: 500,
        scrollX: true,
        autoWidth: false,
        colReorder: true,

        select: {
            style: 'os',
            selector: 'td:first-child'
        },
        fixedColumns: {
            left: 2
        },

        dom: "<'row'<'col-sm-12 col-md-8'lB><'col-sm-12 col-md-4'f>>" +
            "<'row'<'col-sm-12'tr>>" +
            "<'row'<'col-sm-12 col-md-5'i><'col-sm-12 col-md-7'p>>",

        buttons: [
            { extend: 'create', editor: editor, className: 'bg-navy' },
            { extend: 'edit', editor: editor, className: 'bg-navy' },
            { extend: 'remove', editor: editor, className: 'bg-navy' },
            { extend: 'colvis', className: 'bg-navy' },
            { extend: "searchBuilder", className: 'bg-navy' }
        ],

        columns: [
            { data: 'backup_referential.user.userid' },
            { data: 'backup_referential.user.username' },
            { data: 'backup_referential.user.fullname' },
            { data: 'backup_referential.user.comment' },
            { data: 'backup_referential.role', render: '[, ].name' },
            {
                data: 'backup_referential.user.enabled',
                render: function (data, type, row) {
                    if (type === 'display') {
                        return '<input type="checkbox" class="editor-active">';
                    }
                    return data;
                },
                className: 'dt-body-center'
            }
        ],

        rowCallback: function (row, data) {
            // Set the checked state of the checkbox in the table
            $('input.editor-active', row).prop('checked', data.active == 1);
        }
    });

I've tried a lot of solution give in forum for similar post but it doesn't work. If someone have any idea about my issue, I'll take it.

Thanks,

Matéo

Answers

  • allanallan Posts: 63,602Questions: 1Answers: 10,486 Site admin

    Hi Matéo,

    I suspect is the the schema scope that is causing the issue. Try:

                ->link('user.userid', 'role_attribution.userid')
                ->link('role.roleid', 'role_attribution.roleid')
    

    I don't think you need the schema scope due to the parent defining it.

    Allan

  • allanallan Posts: 63,602Questions: 1Answers: 10,486 Site admin

    If that doesn't resolve it, could you show me the JSON response from the server when loading the data please?

    Allan

  • Ellisphere_user1Ellisphere_user1 Posts: 16Questions: 1Answers: 0
    edited October 2023

    Thanks for your response. I tried your change but doesn't work :

    ->join(
                Mjoin::inst('backup_referential.role')
                    ->link('user.userid', 'role_attribution.userid')
                    ->link('role.roleid', 'role_attribution.roleid')
                    ->order('role.name asc')
                    ->fields(
                        Field::inst('roleid')
                            ->validator(Validate::required())
                            ->options('role', 'roleid', 'name'),
                        Field::inst('name')
                    )
            )
    

    Here is the JSON response (I don't know why I've not all the error) :

    {
        "fieldErrors": [],
        "error": "An SQL error occurred: SQLSTATE[42601]: Syntax error: 7 ERREUR: erreur de syntaxe sur ou pr`es de << . >>\nLINE 1: ...OM \"backup_referential\".\"user\" backup_referential.user JOI...\n ^",
        "data": [],
        "ipOpts": [],
        "cancelled": [],
        "debug": [
            "Editor PHP libraries - version 2.2.2",
            {
                "query": "SELECT \"backup_referential\".\"user\".\"userid\" as \"backup_referential.user.userid\", \"backup_referential\".\"user\".\"username\" as \"backup_referential.user.username\", \"backup_referential\".\"user\".\"fullname\" as \"backup_referential.user.fullname\", \"backup_referential\".\"user\".\"comment\" as \"backup_referential.user.comment\", \"backup_referential\".\"user\".\"enabled\" as \"backup_referential.user.enabled\" FROM \"backup_referential\".\"user\" ",
                "bindings": []
            },
            {
                "query": "SELECT DISTINCT \"backup_referential\".\"user\".\"userid\" as \"dteditor_pkey\", \"backup_referential\".\"role\".\"roleid\" as \"roleid\", \"backup_referential\".\"role\".\"name\" as \"name\" FROM \"backup_referential\".\"user\" backup_referential.user JOIN \"user\" ON \"backup_referential\".\"user\".\"userid\" = \"user\".\"userid\" JOIN \"backup_referential\".\"role\" ON \"backup_referential\".\"role\".\"roleid\" = \"user\".\"roleid\" WHERE \"backup_referential\".\"user\".\"userid\" IN (:wherein1, :wherein2) ORDER BY \"role\".\"name\" asc ",
                "bindings": [
                    {
                        "name": ":wherein1",
                        "value": "2",
                        "type": null
                    },
                    {
                        "name": ":wherein2",
                        "value": "1",
                        "type": null
                    }
                ]
            }
        ]
    }
    

    Matéo

  • Ellisphere_user1Ellisphere_user1 Posts: 16Questions: 1Answers: 0

    I don'y know why, but the SQL request is weird. The FROM have backup_referential.user two times. It's here by the way where the error.

    SELECT DISTINCT  backup_referential.user.userid as dteditor_pkey, backup_referential.role.roleid as roleid, backup_referential.role.name as name 
    FROM  backup_referential.user backup_referential.user  
    JOIN user ON backup_referential.user.userid = user.userid  
    JOIN backup_referential.role ON backup_referential.role.roleid = user.roleid 
    WHERE backup_referential.user.userid IN (:wherein1, :wherein2)  
    ORDER BY role.name  asc
    

    The JOIN are also not good yet I think the PHP script is correct.

    Matéo

  • Ellisphere_user1Ellisphere_user1 Posts: 16Questions: 1Answers: 0

    I was wondering, is it possible that there is a problem with the function itself (Mjoin) ? I reviewed my PHP script and it once again seems correct to me.
    I'm wondering this because the duplicate inclusion of backup_referential.user in my FORM is surprising and nothing in the script seems to cause it

  • allanallan Posts: 63,602Questions: 1Answers: 10,486 Site admin

    I suspect it probably is. Apologies for the delayed reply to this thread - I'll need to try and recreate the issue here will update you when I've done that (probably tomorrow now).

    Allan

  • Ellisphere_user1Ellisphere_user1 Posts: 16Questions: 1Answers: 0

    Hi allan,

    I would like to ask you again to see if you had time to look on your side and possibly spot any anomalies. Thanks in advance.

    Matéo

  • allanallan Posts: 63,602Questions: 1Answers: 10,486 Site admin

    Hi Matéo,

    Apologies for loosing sight of this issue! It appears to be related to the use of the database name as part of the parameter names - and I've been experimenting a bit, but it isn't 100% clear to me yet what the correct why to address this is going to be (specifically the issue is related to how we alias column names, I think that might need to be reworked completely).

    That said, I think we can get you up and running without too much difficulty - what we need is for you to be able to drop the backup_referential. database qualifier. That can be done by using:

    $db->sql('USE backup_referential');
    

    And then remove the backup_referential. qualifier from your table name and field names. Since you aren't doing any cross database joins, hopefully that should get reading data okay:

    Editor::inst($db, 'user', 'userid')
        ->field(
            Field::inst('user.userid')
                ->get($_SESSION['read'])
                ->set(false),
            Field::inst('user.username')
                ->get($_SESSION['read'])
                ->set($_SESSION['editing_user'])
                ->validator(Validate::notEmpty(ValidateOptions::inst()))
                ->validator(Validate::maxLen(50))
                ->validator(Validate::noTags(ValidateOptions::inst()))
                ->validator(Validate::xss(ValidateOptions::inst())),
            Field::inst('user.fullname')
                ->get($_SESSION['read'])
                ->set($_SESSION['editing_user'])
                ->validator(Validate::notEmpty(ValidateOptions::inst()))
                ->validator(Validate::maxLen(50))
                ->validator(Validate::noTags(ValidateOptions::inst()))
                ->validator(Validate::xss(ValidateOptions::inst())),
            Field::inst('user.comment')
                ->get($_SESSION['read'])
                ->set($_SESSION['editing_user'])
                ->setFormatter(Format::ifEmpty(null))
                ->validator(Validate::noTags(ValidateOptions::inst()))
                ->validator(Validate::xss(ValidateOptions::inst())),
            Field::inst('user.enabled')
                ->get($_SESSION['read'])
                ->set($_SESSION['editing_user'])
                ->setFormatter(function ($val, $data, $opts) {
                    return !$val ? 0 : 1;
                })
        )
        ->join(
            Mjoin::inst('role')
                ->link('user.userid', 'role_attribution.userid')
                ->link('role.roleid', 'role_attribution.roleid')
                ->order('role.name asc')
                ->fields(
                    Field::inst('roleid')
                        ->validator(Validate::required())
                        ->options('role', 'roleid', 'name'),
                    Field::inst('name')
                )
        )
        ->debug(true)
        ->process($_POST)
        ->json();
    

    Let me know how you get on with that.

    Thanks,
    Allan

  • Ellisphere_user1Ellisphere_user1 Posts: 16Questions: 1Answers: 0

    No problem for the late response. Thanks for your suggest, I've tried with PostegreSQL syntax :

    $db->sql('SET search_path TO backup_referential');
    

    But it doesn't work, the name of the table is put two times again. It's surprising, this hasn't already happened to anyone using mjoin ?
    Here is the JSON response :

    {
       "fieldErrors":[
          
       ],
       "error":"An SQL error occurred: SQLSTATE[42601]: Syntax error: 7 ERREUR:  erreur de syntaxe sur ou pr`es de << user >>\nLINE 1: ...as \"roleid\", \"role\".\"name\" as \"name\" FROM  \"user\" user  JOIN...\n                                                             ^",
       "data":[
          
       ],
       "ipOpts":[
          
       ],
       "cancelled":[
          
       ],
       "debug":[
          "Editor PHP libraries - version 2.2.2",
          {
             "query":"SELECT  \"user\".\"userid\" as \"user.userid\", \"user\".\"username\" as \"user.username\", \"user\".\"fullname\" as \"user.fullname\", \"user\".\"comment\" as \"user.comment\", \"user\".\"enabled\" as \"user.enabled\" FROM  \"user\" ",
             "bindings":[
                
             ]
          },
          {
             "query":"SELECT DISTINCT  \"user\".\"userid\" as \"dteditor_pkey\", \"role\".\"roleid\" as \"roleid\", \"role\".\"name\" as \"name\" FROM  \"user\" user  JOIN \"role_attribution\" ON \"user\".\"userid\" = \"role_attribution\".\"userid\"   JOIN \"role\" ON \"role\".\"roleid\" = \"role_attribution\".\"roleid\" WHERE \"user\".\"userid\" IN (:wherein1, :wherein2)  ORDER BY \"name\"  asc ",
             "bindings":[
                {
                   "name":":wherein1",
                   "value":"2",
                   "type":null
                },
                {
                   "name":":wherein2",
                   "value":"1",
                   "type":null
                }
             ]
          }
       ]
    }
    

    More understandable in sql language :

    SELECT DISTINCT  user.userid as dteditor_pkey, role.roleid as roleid, role.name as name 
    FROM  user user  
    JOIN role_attribution ON user.userid = role_attribution.userid 
    JOIN role ON role.roleid = role_attribution.roleid 
    WHERE user.userid IN (:wherein1, :wherein2)  
    ORDER BY name  asc 
    
  • Ellisphere_user1Ellisphere_user1 Posts: 16Questions: 1Answers: 0

    Hi,

    I tried several things including renaming my table for other reasons. With all these changes, my table is displayed and I no longer have any errors. My only problem is for editing the data. I cannot add, modify or delete data.
    Here is the error show in the web console : Uncaught Unable to automatically determine field from source. Please specify the field name. For more information, please refer to https://datatables.net/tn/11
    However, I think I have implemented my fields well. Maybe still a problem with the join?

    Here is the PHP script :

    $db->sql('SET search_path TO backup_referential');
        Editor::inst($db, 'laps_user', 'laps_user.userid')
            ->field(
                Field::inst('laps_user.userid')
                    ->get($_SESSION['read'])
                    ->set(false),
                Field::inst('laps_user.username')
                    ->get($_SESSION['read'])
                    ->set($_SESSION['editing_user'])
                    ->validator(Validate::notEmpty(ValidateOptions::inst()))
                    ->validator(Validate::maxLen(50))
                    ->validator(Validate::noTags(ValidateOptions::inst()))
                    ->validator(Validate::xss(ValidateOptions::inst())),
                Field::inst('laps_user.fullname')
                    ->get($_SESSION['read'])
                    ->set($_SESSION['editing_user'])
                    ->validator(Validate::notEmpty(ValidateOptions::inst()))
                    ->validator(Validate::maxLen(50))
                    ->validator(Validate::noTags(ValidateOptions::inst()))
                    ->validator(Validate::xss(ValidateOptions::inst())),
                Field::inst('laps_user.comment')
                    ->get($_SESSION['read'])
                    ->set($_SESSION['editing_user'])
                    ->setFormatter(Format::ifEmpty(null))
                    ->validator(Validate::noTags(ValidateOptions::inst()))
                    ->validator(Validate::xss(ValidateOptions::inst())),
                Field::inst('laps_user.enabled')
                    ->get($_SESSION['read'])
                    ->set($_SESSION['editing_user'])
                    ->setFormatter(function ($val, $data, $opts) {
                        return !$val ? 0 : 1;
                    })
            )
            ->join(
                Mjoin::inst('laps_role')
                    ->link('laps_user.userid', 'laps_role_attribution.userid')
                    ->link('laps_role.roleid', 'laps_role_attribution.roleid')
                    ->order('name asc')
                    ->fields(
                        Field::inst('roleid')
                            ->validator(Validate::required())
                            ->options('laps_role', 'roleid', 'name'),
                        Field::inst('name')
                    )
            )
            ->debug(true)
            ->process($_POST)
            ->json();
    

    And my javascript :

    $(function () {
        var editor = new DataTable.Editor({
            ajax: '../../controllers/editor/editor_user.php',
            fields: [
                {
                    label: 'Username:',
                    name: 'backup_referential.laps_user.username'
                },
                {
                    label: 'Fullname:',
                    name: 'backup_referential.laps_user.fullname'
                },
                {
                    label: 'Commentaires:',
                    name: 'backup_referential.laps_user.comment'
                },
                {
                    label: 'Roles:',
                    name: 'backup_referential.laps_role[].id',
                    type: 'select',
                    multiple: true
                },
                {
                    label: 'Enabled:',
                    name: 'backup_referential.laps_user.enabled',
                    type: 'checkbox',
                    separator: '|',
                    options: [{ label: '', value: 1 }]
                }
            ],
            table: '#datatable_user'
        });
    
        var table = $("#datatable_user").DataTable({
            language: {
                url: "assets/language/fr-FR.json"
            },
            ajax: {
                url: '../../controllers/editor/editor_user.php',
                type: 'POST'
            },
            pageLength: 5,
            lengthMenu: [[5, 10, 20, 50, -1], [5, 10, 20, 50, "Tous"]],
            scrollCollapse: true,
            scrollY: 500,
            scrollX: true,
            autoWidth: false,
            colReorder: true,
    
            select: {
                style: 'os',
                selector: 'td:first-child'
            },
            fixedColumns: {
                left: 2
            },
    
            dom: "<'row'<'col-sm-12 col-md-8'lB><'col-sm-12 col-md-4'f>>" +
                "<'row'<'col-sm-12'tr>>" +
                "<'row'<'col-sm-12 col-md-5'i><'col-sm-12 col-md-7'p>>",
    
            buttons: [
                { extend: 'create', editor: editor, className: 'bg-navy' },
                { extend: 'edit', editor: editor, className: 'bg-navy' },
                { extend: 'remove', editor: editor, className: 'bg-navy' },
                { extend: 'spacer', style: 'bar' },
                { extend: "copy", className: 'bg-navy' },
                { extend: "csv", className: 'bg-navy' },
                { extend: "excel", className: 'bg-navy' },
                { extend: 'spacer', style: 'bar' },
                { extend: 'colvis', className: 'bg-navy' },
                { extend: "searchBuilder", className: 'bg-navy' }
    
            ],
    
            columns: [
                { data: 'laps_user.userid' },
                { data: 'laps_user.username' },
                { data: 'laps_user.fullname' },
                { data: 'laps_user.comment' },
                { data: 'laps_role', render: '[, ].name' },
                {
                    data: 'laps_user.enabled',
                    render: function (data, type, row) {
                        if (type === 'display') {
                            return '<input type="checkbox" class="editor-active">';
                        }
                        return data;
                    },
                    className: 'dt-body-center'
                }
            ],
    
            rowCallback: function (row, data) {
                // Set the checked state of the checkbox in the table
                $('input.editor-active', row).prop('checked', data.active == 1);
            }
        });
    
        table.on('click', 'tbody td:not(:first-child) ', function (e) {
            editor.inline(this, {
                onBlur: 'submit'
            });
        });
    
        $('#datatable_user').on('change', 'input.editor-active', function () {
            editor
                .edit($(this).closest('tr'), false)
                .set('active', $(this).prop('checked') ? 1 : 0)
                .submit();
        });
    
        editor.on('edit', function () {
            toastr.success('Modification(s) effectuée(s)', 'Succès', { timeOut: 5000 });
        });
    
        editor.on('remove', function () {
            toastr.success('Supression(s) effectuée(s)', 'Succès', { timeOut: 5000 });
        });
    });
    
    
    

    If anyone has any idea, I'm all ears. Thanks in advance :smile:

    Matéo

  • rf1234rf1234 Posts: 3,006Questions: 87Answers: 421
    edited October 2023

    I think something is wrong here:

    ->join(
                Mjoin::inst('laps_role')
                    ->link('laps_user.userid', 'laps_role_attribution.userid')
                    ->link('laps_role.roleid', 'laps_role_attribution.roleid')
                    ->order('name asc')
                    ->fields(
                        Field::inst('roleid')
                            ->validator(Validate::required())
                            ->options('laps_role', 'roleid', 'name'),
                        Field::inst('name')
                    )
    

    "laps_role" is not a field, I guess. It is a table.

    My Mjoins - particularly the options instances - look quite different from yours - but I don't know whether that is important ...

    Here is one:

    ->join(
    Mjoin::inst( 'gov' )
        ->link( 'ctr_installation.id', 'ctr_installation_has_gov.ctr_installation_id' )
        ->link( 'gov.id', 'ctr_installation_has_gov.gov_id' )
        ->order( 'gov.name asc' )
        ->fields(
            Field::inst( 'id' )->set( false )                
                ->options( Options::inst()
                    ->table('gov')
                    ->value('id')
                    ->label( array('name', 'regional_12') )
                    ->render( function ( $row ) {   
                        return $row['name'] . ' ('.$row['regional_12'].')';
                    } )
                    ->order( 'name asc' )
                    ->where( function($q) {
                        //$q ->where('gov.is_client_ctr', 1 );
                    } )
                ),
            Field::inst( 'name' )->set( false ),
            Field::inst( 'regional_12' )->set( false )
        )
    )
    

    Here are examples from the docs. Take a look at the server script and the options instances:
    https://editor.datatables.net/examples/simple/join
    https://editor.datatables.net/examples/advanced/joinArray.html

    Please also take a look at this. Scroll down to "Options".
    https://editor.datatables.net/manual/php/joins#options

  • rf1234rf1234 Posts: 3,006Questions: 87Answers: 421
    edited October 2023

    Some more things on options:

    The following options are (almost) equivalent. The first are retrieved using SQL with Editor's db-handler using Editor's "raw()" method, the second ones are retrieved using Editor's options class.

    I use the first because this is more flexible. The second one is a little less flexible. You can't use inner joins for example and you can't return additional values as I do in the first one (field "report_type" which I need to generate <optgroup> tags at the client).

    Field::inst( 'report.report_type_id' )->validator( 'Validate::notEmpty', array('message' => $msg[0]) )
        ->options( function() use ( $db ) {
            $statement = ('SELECT DISTINCT CONCAT(LPAD(`number`, 2, 0),": ", `label`) AS label, 
                                  `id` AS value, report_type
                             FROM report_type
                            WHERE user_id = :userId 
                         ORDER BY 3, 1 ASC');  
            $result =
            $db ->raw()
                ->bind(':userId', $_SESSION['id'])
                ->exec($statement);
            return $result->fetchAll(PDO::FETCH_ASSOC);
        } )
        ->options( Options::inst()
            ->table('report_type')
            ->value('id')
            ->label( array('label', 'number') ) 
            ->render( function ( $row ) { 
                return sprintf("%02d", $row['number']) . ": " . $row['label'];
            } )
            ->order('number')
            //where clause MUST be a closure function in Options!!!
            ->where( function($q) {                    
                $q ->where( 'user_id', $_SESSION['id'] );
            } )
        ),
    
  • Ellisphere_user1Ellisphere_user1 Posts: 16Questions: 1Answers: 0

    Hi,

    Tanks for your answers rf1234. I redid my JOIN construction like you. I also fixed some other issues on my side.
    First of all, I took the liberty of renaming editor and table by editor_user and table_user. Return it to the initial state to sort out a lot of problems.
    I also forgot to remove backup_referential in my fields in the editor declaration in the javascript.

    Now, the roles are indeed displayed in the table but it is at the editing level that there is a problem. When I want to edit inline for role column (only for this column), I get this error message in the javascript console:
    Uncaught Unable to automatically determine field from source. Please specify the field name. For more information, please refer to https://datatables.net/tn/11

    I obviously checked the datatables error 11 but I can't seem to locate the problem for my code. I also have a problem with editing, select2 is displayed well but without data. When I validate the modification or even an addition, it does not work because the role field is obviously empty.

    These issues are most likely related, so I was wondering if anyone had any idea why the data is not showing in the editor and I am having an inability to edit inline as well.

    Here is the javascript with the modifications I talked about previously :

    $(function () {
        var editor = new DataTable.Editor({
            ajax: '../../controllers/editor/editor_user.php',
            fields: [
                {
                    label: 'Username:',
                    name: 'laps_user.username'
                },
                {
                    label: 'Fullname:',
                    name: 'laps_user.fullname'
                },
                {
                    label: 'Commentaires:',
                    name: 'laps_user.comment'
                },
                {
                    label: 'Roles:',
                    name: 'laps_role[].id',
                    type: 'select2',
                    multiple: true
                }
            ],
            table: '#datatable_user'
        });
    
        editor.field('laps_role[].id').input().addClass('select-editor');
    
        var table = $("#datatable_user").DataTable({
            language: {
                url: "assets/language/fr-FR.json"
            },
            ajax: {
                url: '../../controllers/editor/editor_user.php',
                type: 'POST'
            },
            pageLength: 5,
            lengthMenu: [[5, 10, 20, 50, -1], [5, 10, 20, 50, "Tous"]],
            scrollCollapse: true,
            scrollY: 500,
            scrollX: true,
            autoWidth: false,
            colReorder: true,
    
            select: {
                style: 'os',
                selector: 'td:first-child'
            },
            fixedColumns: {
                left: 2
            },
    
            dom: "<'row'<'col-sm-12 col-md-8'lB><'col-sm-12 col-md-4'f>>" +
                "<'row'<'col-sm-12'tr>>" +
                "<'row'<'col-sm-12 col-md-5'i><'col-sm-12 col-md-7'p>>",
    
            buttons: [
                { extend: 'create', editor: editor, className: 'bg-navy' },
                { extend: 'edit', editor: editor, className: 'bg-navy' },
                { extend: 'remove', editor: editor, className: 'bg-navy' },
                { extend: 'spacer', style: 'bar' },
                { extend: "copy", className: 'bg-navy' },
                { extend: "csv", className: 'bg-navy' },
                { extend: "excel", className: 'bg-navy' },
                { extend: 'spacer', style: 'bar' },
                { extend: 'colvis', className: 'bg-navy' },
                { extend: "searchBuilder", className: 'bg-navy' }
            ],
    
            columns: [
                { data: 'laps_user.userid' },
                { data: 'laps_user.username' },
                { data: 'laps_user.fullname' },
                { data: 'laps_user.comment' },
                { data: 'laps_role', render: '[, ].name', editField: 'laps_role.id' }
            ]
        });
    
        table.on('click', 'tbody td:not(:first-child)', function (e) {
            editor.inline(this, {
                onBlur: 'submit'
            });
        });
    });
    

    And maybe my php script :

    Editor::inst($db, 'laps_user', 'laps_user.userid')
            ->field(
                Field::inst('laps_user.userid')
                    ->get($_SESSION['read'])
                    ->set(false),
                Field::inst('laps_user.username')
                    ->get($_SESSION['read'])
                    ->set($_SESSION['editing_user'])
                    ->validator(Validate::notEmpty(ValidateOptions::inst()))
                    ->validator(Validate::maxLen(50)),
                Field::inst('laps_user.fullname')
                    ->get($_SESSION['read'])
                    ->set($_SESSION['editing_user'])
                    ->validator(Validate::notEmpty(ValidateOptions::inst()))
                    ->validator(Validate::maxLen(50)),
                Field::inst('laps_user.comment')
                    ->get($_SESSION['read'])
                    ->set($_SESSION['editing_user'])
                    ->setFormatter(Format::ifEmpty(null))
            )
            ->join(
                Mjoin::inst('laps_role')
                    ->link('laps_user.userid', 'laps_role_attribution.userid')
                    ->link('laps_role.roleid', 'laps_role_attribution.roleid')
                    ->order('name asc')
                    ->fields(
                        Field::inst('roleid')
                            ->options(
                                Options::inst()
                                    ->table('laps_role')
                                    ->value('roleid')
                                    ->label('name')
                            ),
                        Field::inst('name')->set(false),
                        Field::inst('comment')->set(false)
                    )
            )
            ->debug(true)
            ->process($_POST)
            ->json();
    

    Thanks in advance :smile:

    Matéo

  • rf1234rf1234 Posts: 3,006Questions: 87Answers: 421
    Answer ✓

    I guess the "editField" is called 'laps_role[].id' and not 'laps_role.id'

    I would give that a try.

  • Ellisphere_user1Ellisphere_user1 Posts: 16Questions: 1Answers: 0
    edited October 2023

    Thank you very mutch, that removed that error, I should have thought of that. I now have my select2 displaying correctly. But as for editing, I still have the problem that there is no data in the drop-down list which prevents editing. I will continue to look (especially on the JOIN side) on my side but if you have an idea I am interested

    If ever, here is the errors returned:
    Fatal error: Uncaught TypeError: count(): Argument #1 ($value) must be of type Countable|array, string given in C:\wamp64\www\LAPS\vendor\datatables.net\editor-php\Editor\Join.php on line 796
    TypeError: count(): Argument #1 ($value) must be of type Countable|array, string given in C:\wamp64\www\LAPS\vendor\datatables.net\editor-php\Editor\Join.php on line 796

    And the function where the errors occur (in raw) :
    Locate in Editor.php:857 :

    DataTables\Editor\Join->validate( $errors = [], $editor = class DataTables\Editor { public $version = '2.2.2'; private $_db = class DataTables\Database { private $_dbResource = class PDO { ... }; private $_type = 'Postgres'; private $_debugCallback = class Closure { ... }; private $query_driver = 'DataTables\\Database\\Driver\\PostgresQuery' }; private $_fields = [0 => class DataTables\Editor\Field { ... }, 1 => class DataTables\Editor\Field { ... }, 2 => class DataTables\Editor\Field { ... }, 3 => class DataTables\Editor\Field { ... }]; private $_formData = ['row_12' => [...]]; private $_processData = ['data' => [...], 'action' => 'edit']; private $_idPrefix = 'row_'; private $_join = [0 => class DataTables\Editor\Mjoin { ... }]; private $_pkey = [0 => 'laps_user.userid']; private $_table = [0 => 'laps_user']; private $_readTableNames = []; private $_transaction = TRUE; private $_where = []; private $_write = TRUE; private $_leftJoin = []; private $_whereSet = FALSE; private $_out = ['fieldErrors' => [...], 'error' => '', 'data' => [...], 'ipOpts' => [...], 'cancelled' => [...]]; private $_events = []; private $_debug = TRUE; private $_debugInfo = [0 => 'Editor PHP libraries - version 2.2.2']; private $_debugLog = ''; private $_validator = []; private $_tryCatch = TRUE; private $_leftJoinRemove = FALSE; private $_actionName = 'action' }, $data = ['laps_role' => ''], $action = 'edit' )
    

    Matéo

  • rf1234rf1234 Posts: 3,006Questions: 87Answers: 421
    edited October 2023

    To be honest I have no idea and I am using a different Editor version. Since you have no special messages in your "notEmpty" validators they look a bit odd.

    Could you try this please

    ->validator( Validate::notEmpty() )
    

    Alternatively you can try this:

    ->validator( Validate::notEmpty( ValidateOptions::inst()
        ->message( 'Field may not be empty' )   
    ) )
    
  • Ellisphere_user1Ellisphere_user1 Posts: 16Questions: 1Answers: 0

    Thank for your time, unfortunately it doesn't work.

    Mjoin::inst('laps_role')
                    ->link('laps_user.userid', 'laps_role_attribution.userid')
                    ->link('laps_role.roleid', 'laps_role_attribution.roleid')
                    ->order('name asc')
                    ->fields(
                        Field::inst('roleid')
                            ->validator(Validate::notEmpty(ValidateOptions::inst()->message('Field may not be empty')))
                            ->options(
                                Options::inst()
                                    ->table('laps_role')
                                    ->value('roleid')
                                    ->label('name')
                                    ->order('name asc')
                            ),
                        Field::inst('name')->set(false),
                        Field::inst('comment')->set(false)
                    )
    

    This is surprising because my JOIN sort of works. It displays the role of the user but it is at the time of editing, it is as if select2 had no options available. However, I think I have done everything possible to allow it.

  • rf1234rf1234 Posts: 3,006Questions: 87Answers: 421

    Sorry I am out; I personally use those legacy validators that work fine for me. I guess you will need to make a test case.

    ->validator( 'Validate::notEmpty', array('message' => 'Field may not be empty') )
    
  • Ellisphere_user1Ellisphere_user1 Posts: 16Questions: 1Answers: 0

    Thanks anyway, I'll keep looking on my own. And if I don't find it, I'll stay in the hope of finding someone who has a solution. :)

  • allanallan Posts: 63,602Questions: 1Answers: 10,486 Site admin
    Answer ✓

    Hi folks,

    Sorry I've been out of the loop with this one a little. Mjoin validators are a little different from the regular field ones. Have a look at this part of the docs specifically. If you have a required field, then use the mjoinMinCount validator (i.e. the number selected must be at least 1). Its just a slightly different way of looking at a "required" field (since it is an array of values).

    In terms of the values, If your field is called name: 'laps_role[].roleid' on the client-side, then it should populate with options (it looks you might have it as laps_role[].id - I think that needs to be updated, and the editField value for it changes to match).

    Allan

  • Ellisphere_user1Ellisphere_user1 Posts: 16Questions: 1Answers: 0

    Hi allan,

    This is called a careless error, I was so focused on something else that I didn't pay attention, thank you very much the list of options displays fine now.

    But I still have an error for editing but it is with a function in the Join.php file:

    Any idea where this could come from ? I have to declare somewhere that laps_role.roleid is necessarily a "countable" ?

    The error appears regardless of whether the inclusion of a validator is there :

    Mjoin::inst('laps_role')
                    ->link('laps_user.userid', 'laps_role_attribution.userid')
                    ->link('laps_role.roleid', 'laps_role_attribution.roleid')
                    ->order('name asc')
                    ->validator('laps_role[].roleid', Validate::mjoinMinCount(1))
                    ->fields(
                        Field::inst('roleid')
                            ->options(
                                Options::inst()
                                    ->table('laps_role')
                                    ->value('roleid')
                                    ->label('name')
                                    ->order('name asc')
                            ),
                        Field::inst('name')->set(false),
                        Field::inst('comment')->set(false)
                    )
    

    With the inclusion of validator, the error is the same but located in the Validate.php :

  • allanallan Posts: 63,602Questions: 1Answers: 10,486 Site admin

    That error suggests that it isn't an array of data being sent to the server. Could you pop open your browser's "Inspect", make the request that causes the error and then find that in the "Network" inspector panel and show me the parameters that are being sent to the server please?

    Thanks,
    Allan

  • Ellisphere_user1Ellisphere_user1 Posts: 16Questions: 1Answers: 0
    edited October 2023

    Here are the parameters sent to the server when modifying the fields of a row :

    {
        "data[row_12][laps_user][username]": "usernametest",
        "data[row_12][laps_user][fullname]": "fullnametest",
        "data[row_12][laps_user][comment]": "commenttest",
        "data[row_12][laps_role]": "2",
        "action": "edit"
    }
    

    I also add a data row sent during initialization to be displayed in the table :

    {
       "data":[
          {
             "DT_RowId":"row_1",
             "laps_user":{
                "userid":1,
                "username":"john",
                "fullname":"John Lewis",
                "comment":"Utilisateur imaginaire de test"
             },
             "laps_role":[
                {
                   "roleid":1,
                   "name":"Admin",
                   "comment":"..."
                }
             ]
          },
    ...
    

    Thanks,
    Matéo

  • allanallan Posts: 63,602Questions: 1Answers: 10,486 Site admin
    Answer ✓

    There is something goting wrong with the data being submitted. It should contain a -many-count parameter for laps_role - to tell Editor's server-side components that it is submitting for an Mjoin.

    Can you show me the current field options you are using for laps_role[].roleid please? I'm wondering if there is a separator option or something (there isn't above, but maybe it has changed).

    If that isn't the issue, I'd need a link to the page to be able to trace it through and see what is going wrong.

    Allan

  • Ellisphere_user1Ellisphere_user1 Posts: 16Questions: 1Answers: 0

    Indeed yes, nothing has changed compared to above :

    ->join(
                Mjoin::inst('laps_role')
                    ->link('laps_user.userid', 'laps_role_attribution.userid')
                    ->link('laps_role.roleid', 'laps_role_attribution.roleid')
                    ->order('name asc')
                    ->validator('laps_role[].roleid', Validate::mjoinMinCount(1))
                    ->fields(
                        Field::inst('roleid')
                            ->options(
                                Options::inst()
                                    ->table('laps_role')
                                    ->value('roleid')
                                    ->label('name')
                                    ->order('name asc')
                            ),
                        Field::inst('name')->set(false),
                        Field::inst('comment')->set(false)
                    )
            )
    

    How can I send you a link of the page ? The website is only accessible locally.

    Matéo

  • allanallan Posts: 63,602Questions: 1Answers: 10,486 Site admin

    How can I send you a link of the page ? The website is only accessible locally.

    You can't then. At least not without port forwarding and all that stuff.

    Can you show me the current Javascript for the field please?

    Allan

  • Ellisphere_user1Ellisphere_user1 Posts: 16Questions: 1Answers: 0
    edited October 2023

    We will actually avoid port forwarding. Here is my javascript :

    $(function () {
        var editor = new DataTable.Editor({
            ajax: '../../controllers/editor/editor_user.php',
            fields: [
                {
                    label: 'Username:',
                    name: 'laps_user.username'
                },
                {
                    label: 'Fullname:',
                    name: 'laps_user.fullname'
                },
                {
                    label: 'Commentaires:',
                    name: 'laps_user.comment'
                },
                {
                    label: 'Roles:',
                    name: 'laps_role[].roleid',
                    type: 'select2'
                }
            ],
            table: '#datatable_user'
        });
    
        editor.field('laps_role[].roleid').input().addClass('select-editor');
    
        var table = $("#datatable_user").DataTable({
            language: {
                url: "assets/language/fr-FR.json"
            },
            ajax: {
                url: '../../controllers/editor/editor_user.php',
                type: 'POST'
            },
            pageLength: 5,
            lengthMenu: [[5, 10, 20, 50, -1], [5, 10, 20, 50, "Tous"]],
            scrollCollapse: true,
            scrollY: 500,
            scrollX: true,
            autoWidth: false,
            colReorder: true,
    
            select: {
                style: 'os',
                selector: 'td:first-child'
            },
            fixedColumns: {
                left: 2
            },
    
            dom: "<'row'<'col-sm-12 col-md-8'lB><'col-sm-12 col-md-4'f>>" +
                "<'row'<'col-sm-12'tr>>" +
                "<'row'<'col-sm-12 col-md-5'i><'col-sm-12 col-md-7'p>>",
    
            buttons: [
                { extend: 'create', editor: editor, className: 'bg-navy' },
                { extend: 'edit', editor: editor, className: 'bg-navy' },
                { extend: 'remove', editor: editor, className: 'bg-navy' },
                { extend: "copy", className: 'bg-navy' },
                { extend: "csv", className: 'bg-navy' },
                { extend: "excel", className: 'bg-navy' },
                { extend: 'spacer', style: 'bar' },
                { extend: 'spacer', style: 'bar' },
                { extend: 'colvis', className: 'bg-navy' },
                { extend: "searchBuilder", className: 'bg-navy' }
            ],
    
            columns: [
                { data: 'laps_user.userid' },
                { data: 'laps_user.username' },
                { data: 'laps_user.fullname' },
                { data: 'laps_user.comment' },
                { data: 'laps_role', render: '[, ].name', editField: 'laps_role[].roleid' }
            ]
        });
    
        table.on('click', 'tbody td:not(:first-child)', function (e) {
            editor.inline(this, {
                onBlur: 'submit'
            });
        });
    });
    

    Thanks again for your time.

    Matéo

  • allanallan Posts: 63,602Questions: 1Answers: 10,486 Site admin
    Answer ✓

    type: 'select2'

    That's the issue. Select2 hasn't been configured for multiple selection there. You could try it without Select2:

    type: 'select'
    multiple: true
    

    or configure Select2 for multiple selection:

    type: 'select2',
    opts: {
      multiple: true
    }
    

    Thanks,
    Allan

  • Ellisphere_user1Ellisphere_user1 Posts: 16Questions: 1Answers: 0

    Hi allan,

    Effectively it was the problem, everything works perfectly. Tanks you very mutch for your time and your patience.

    Have a great day.

    Matéo

Sign In or Register to comment.