Issue with delete button when using combined primary key

spidder

Hi, I guess this will be just something I am missing, so here is the code I have and is not working.... Very same example when using this code without aliases and multiple tables is working. I had 1 table which used alias as well (no joins), once I removed the alias from everywhere the delete works afterwards. The debug information is completely empty {"data":[],"debugSql":[]}

editor3 = new $.fn.dataTable.Editor( {
        ajax: "_content/datatables/dt_email_groups_assign.php",
        table: "#emailsgroups",
        fields: [  {
                label: "Email:",
                name: "eg.emailaddress_id",
                type:"select"
            }, {
                label: "Group:",
                name: "eg.emailgroup_id",
                type:"select"
            }
        ]
    } );
    table3 = $('#emailsgroups').DataTable( {
        dom: "Bfrtip",
        ajax: "_content/datatables/dt_email_groups_assign.php",
        columns: [
            { data: "e.address" },
            { data: "g.name" }
        ],
        select: true,
        buttons: [
            { extend: "create",   editor: editor3 },
            { extend: "edit",   editor: editor3 },
            { extend: "remove",   editor: editor3 }
        ],
    } );

PHP part below

// Alias Editor classes so they are easy to use
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate;
// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'fmsmon.mon_emails_cfg'.$_SESSION['dblink'].' eg', array('eg.emailgroup_id', 'eg.emailaddress_id'))
    ->fields(
        Field::inst( 'eg.emailaddress_id' )
            ->options( Options::inst()
                ->table( 'fmsmon.mon_emailaddresses_cfg'.$_SESSION['dblink'].' e' )
                ->value( 'id' )
                ->label( 'address' ) 
            ),
        Field::inst( 'e.address' ),
        Field::inst( 'eg.emailgroup_id' )
            ->options( Options::inst()
                ->table( 'fmsmon.mon_emailgroups_cfg'.$_SESSION['dblink'].' g' )
                ->value( 'id' )
                ->label( 'name' ) 
            ),
        Field::inst( 'g.name' )
    )
    ->leftJoin( 'fmsmon.mon_emailaddresses_cfg'.$_SESSION['dblink'].' e',     'e.id',          '=', 'eg.emailaddress_id' )
    ->leftJoin( 'fmsmon.mon_emailgroups_cfg'.$_SESSION['dblink'].' g',     'g.id',          '=', 'eg.emailgroup_id' )
    ->debug( true )
    ->process( $_POST )
    ->json();

Everything else works perfect. Of course I did not include the entire code like session_start etc.

Thanks
Marek

allan

Hi Marek,

Could you confirm if you are using Editor 1.6.5 please? Also, are you able to give me a link to the page so I can see the issue happen and inspect the client-side code?

If not, can you show me the data the client is submitting to the server please?

Thanks,
Allan

spidder

action=remove&data%5Brow_21e669d391%5D%5BDT_RowId%5D=row_21e669d391&data%5Brow_21e669d391%5D%5Beg%5D%5Bemailaddress_id%5D=1&data%5Brow_21e669d391%5D%5Beg%5D%5Bemailgroup_id%5D=2&data%5Brow_21e669d391%5D%5Be%5D%5Baddress%5D=FMSMonitoring%40kpn.com&data%5Brow_21e669d391%5D%5Bg%5D%5Bname%5D=kjdlfalkd

I am not able to share the link, since we have it on intranet. Above is the header source. Where can I check the version no?

allan

Ah! I don't see it in the above configuration, but that is a request with a query string, which suggests that it is either a GET or a DELETE request that is being made. Checking the information shown in your browser's network inspector will confirm which.

You can use ajax.deleteBody to tell Editor to submit the delete information in the request body, which would be needed if you are using $_POST on the server-side to get the data (which the code above shows is being used).

I don't see the configuration that would be causing a DELETE to be sent though...

Allan

spidder

Hi, I am attaching the screenshot from the browser inspector and also the original source files which are the key ones. I am really confused cause in the attached scenario I actually use the Id column which is by default the primary key but it does not work... I have been going through my code for ages since I have deletion working in 15 others, but I have issues with 2 only. And this recent one is just very plain and simple, but I cannot find out the issue

If you need any more information please let me know.

Thanks for help in advance

allan

Field::inst( 'a.al_text' )

This isn't going to work. There is no a alias - the options class does its own separate SELECT from the database. Its possible that might be the issue, or it might be that you have aliased the main table to be c - I think the PHP libraries currently require an as statement, although that will change in the next release.

The other issue I see is that the primary key might be called al_id, not just id - which without being able to see the table structure I can't say for sure.

Allan

spidder

Hi, for simplicity I have removed the lookup table, and only have one table, in that table, there are 3 fields... id, al_id and emailaddresses. Please see the attached files (I have added 2 to the end only because I cannot reupload same files). The deletion still does not work. the behaviour is following. Once I select the record and press delete, it removes it from the webpage, so it actually thinks it is deleted... but after refreshing the page the record reappears. Any thoughts?

spidder

And also I tried to include as statement, it is the same issue

allan

Can you show me what the JSON return from the server is after a delete action please? You have the debug(true) statement in place, so it would be interesting to see what SQL is actually being executed.

Also, it would be worth checking the server's error log just in case there is any information shown there.

Allan

spidder

THis is the funny part, the debug shows this, when I try to delete: {"data":[],"debugSql":[]}, while I can see for other table where I do not use aliases, that it shows nicely: {"data":[],"debugSql":[{"query":"DELETE FROM cfg_lang WHERE (id = :where_1 )","bindings":[{"name":":where_1","value":"145","type":null}]}]}. This makes me wonder, if the schema name which is included in this case ("sds.") is not somehow interfering, cause in other table where delete works I do not use schema name as it is in the same one. I will check the logs as well, and will provide details, but need to ask our security department for permissions to access it first

Thanks for all the help so far

spidder

Any idea which error log I should look into? Apache error log does not show anything of interest... I just enabled all logging options to max, and on, also tracking, but I do not see any helpful information anywhere there, of course we restarted apache after changes...

spidder

and maybe this will be helpful too, its from chrome debugger/inspector

spidder

And just as a remined, everything works fine if I remove the alias...

allan

I think you are correct - the libraries aren't correctly handling the schema name combined with an alias. I'm afraid that until proper schema support is added, the workaround is to be verbose at the moment and drop the alias.

Regards,
Allan

spidder

Luckily we found out that one table will not need to have delete functionality, and other one will be used by us developers, so we can handle it via backend for now... If we run into need of having this functionality, we will try to look for a workaround and I will publish it here, if we have it thanks for all the help. Just a very quick side question..., do you plan to fix this within coming release(s)? No urgency though, just asking.

allan

Yes - proper schema support is something I'm planning to introduce. It won't be 1.7 which will ship this month, but it is something that should be addressed in future.

Allan