master detail table update problem

alpaltunel

Hello

I am working on a editor on an existing table.

MasterTable
ID, is_active, sort_order

Detail Table
MasterID, Name, Description, language

detail data:
1, 'name', 'description', 'english'
1, 'adi', 'aciklama', 'turkish'

problem:
when I update is_active field the detail records with two different language options are updated and overwritten.

1, 'adi', 'aciklama', 'turkish'
1, 'adi', 'aciklama', 'turkish'

and english data is updated with turkish data.

$edt->inst($_db, 'MasterTableName')
                    ->fields($flds)
                    ->where("DetailTableName.language", $_SESSION['lng']) //->which is 'turkish'
                    ->leftJoin("DetailTableName", "DetailTableName.MasterID", '=', "MasterTableName.ID")
                    ->process($_POST)
                    ->json();

this does not works. my where condition is completely ignored.

how to seperate these two fields?

allan

The Editor->where() condition is not applied when updating data. It is assumed that the primary key value is unique to the row being edited. Does your table have a unique parameter key?

Allan

alpaltunel

Thanks for quick reply Allan.

Yes both tables has ID fields which are unique and auto_increment.
but I dont know how to handle them?

MasterTable
ID, is_active, sort_order

Detail Table
ID, MasterID, Name, Description, language

how can I fix the server script part?

allan

Can you show me what $flds is and also your Javascript configuration for DataTables and Editor.

Allan

alpaltunel

<script>
(function ($) {
    $(document).ready(function () {
        var editor = new $.fn.dataTable.Editor({
            "ajax": "/admx/ajax/ajax/loadData.html?extension=" + extid + "&p=" + parID + "&en=" + extname,
            "table": "#dttable",
            "fields": [
                // ft: int
              {
                    "label": "Sıra #",
                    "name": "shp_payment_options.sort_order"
                                            , "type": "readonly"
               },
                // ft: int
              {
                    "label": "ID",
                    "name": "shp_payment_options.ID"
                                            , "type": "readonly"
               },
                // ft: decimal
                {
                    "label": "Rate",
                    "name": "shp_payment_options.Rate"
                },
                // ft: decimal
                                    {
                    "label": "Amount",
                    "name": "shp_payment_options.Amount"
                },
                // ft: text
                                    {
                    "label": "Type",
                    "name": "shp_payment_options_detail.Type"
                 },
                // ft: text
                                    {
                    "label": "Name",
                    "name": "shp_payment_options_detail.Name"
                },
                // ft: text
                                    {
                    "label": "Description",
                    "name": "shp_payment_options_detail.Description"
                                                                                        },
                // ft: int
                                                        {
                    label: "Active?",
                                            name: "shp_payment_options.is_active",
                                            type: "checkbox",
                    separator: "|",
                    options: [
                        { label: '', value: 1 }
                    ]
                },              
            ]
        });

        // Edit record
        $('#dttable').on('click', 'a.editor_edit', function (e) {
            e.preventDefault();

            editor.edit($(this).closest('tr'), {
                title: 'Edit record',
                buttons: 'Update'
            });
        });

        // Delete a record
        $('#dttable').on('click', 'a.editor_remove', function (e) {
            e.preventDefault();

            editor.remove($(this).closest('tr'), {
                title: 'Delete record',
                message: 'Are you sure you wish to remove this record?',
                buttons: 'Delete'
            });
        });

        //td:not(:last-child)
        $('#dttable').on('click', 'tbody td:not(.center)', function (e) {
            editor.inline(this, {
                submitOnBlur: true
            });
        });

        var table = $('#dttable').DataTable({
            "dom": 'Tlfrtip',
            "ajax": "/admx/ajax/ajax/loadData.html?extension=" + extid + "&p=" + parID + "&en=" + extname, // ajax source, 
            "columns": [
                                    {
                    "data": "shp_payment_options.sort_order"
                                            , className: "center"
                                        },
                                    {
                    "data": "shp_payment_options.ID"
                                            , className: "center"
                                        },
                                    {
                    "data": "shp_payment_options.Rate"
                                        },
                                    {
                    "data": "shp_payment_options.Amount"
                                        },
                                    {
                    "data": "shp_payment_options_detail.Name"
                                        },
                                                        {
                    "data": "shp_payment_options.is_active",
                    render: function (data, type, row) {
                        if (type === 'display') {
                            return '<input type="checkbox" class="editor-active">';
                        }
                        return data;
                    },
                    className: "center"
                },
                                    {
                    "data": "shp_payment_options_detail.ID"
                                        },
                                    {
                    data: null,
                    className: "center",
                    defaultContent: '<a href="" class="btn btn-sm yellow tooltips editor_edit" data-placement="top" data-original-title="Kayıt Güncelle"><i class="fa fa-pencil"></i></a> <a href="" class="btn btn-sm red tooltips editor_remove"  data-placement="top" data-original-title="Kayıt Sil"><i class="fa fa-times"></i></a>'
                },
            ],
            "lengthMenu": [
                [20, 50, 100, 250, -1],
                [20, 50, 100, 250, "All"] // per page values here
            ],
            "pageLength": 20, // default record count per page
            "tableTools": {
                "sRowSelect": "os",
                "aButtons": [
                    { "sExtends": "editor_create", "editor": editor },
                    { "sExtends": "editor_edit", "editor": editor },
                    { "sExtends": "editor_remove", "editor": editor }
                ]
            },
                            rowCallback: function (row, data) {
                // Set the checked state of the checkbox in the table
                //detail: shp_payment_options_detail
                                    $('input.editor-active', row).prop('checked', data.shp_payment_options.is_active == 1);
                                }
                        });
        $('#dt_refresh').click(function (e) {
            e.preventDefault();
            parIDx = $('#FilterParentIDx').val();
            table.ajax.url("/admx/ajax/ajax/loadData.html?extension=" + extid + "&p=" + parIDx + "&en=" + extname);
            table.ajax.reload();
        });

        $('#FilterParentIDx').on('change', function () {
            table.ajax.url("/admx/ajax/ajax/loadData.html?extension=" + extid + "&p=" + this.value + "&en=" + extname);
            table.ajax.reload();
        });

                    $('#dttable').on('change', 'input.editor-active', function () {
            editor
                    .edit($(this).closest('tr'), false)
                    .set('shp_payment_options.is_active', $(this).prop('checked') ? 1 : 0)
                    .submit();
        });
    });

}(jQuery));

alpaltunel

I am trying to add the DetailTableName.ID field to editor at the moment :)
but it doesnt accept it as a seperate key and continues to update Name field.

alpaltunel

also when I try to add new record the MasterID field on the detail table is set as '0'.
Is there any way to implement all these?

allan

I am trying to add the DetailTableName.ID field to editor at the moment

This is exactly what I was going to suggest as it happens :-). You need to add that as a hidden field in the Javascript Editor and also to the fields in the PHP. That should give the server-side enough information to be able to update the field correctly on the joined table.

Are you getting any errors if you add that?

continues to update Name field.

For both of the child rows?

Allan

alpaltunel

unfortunately it updates both fields again. where clause should be executed if it exists. I will change the php code if necessary. can you suggest another solution?

alpaltunel

the only solution to this situation is to apply the where condition otherwise all records are updated.

in Editor.php file:
private function _insert_or_update ( $id )

at the end of the function:

for($i=0;$i<count($this->_where);$i++) {
            $wh = array($this->_where[$i]['key'] => $this->_where[$i]['value']);
            $where = array_merge($wh, $where);
 }

should be added and where condition should be applied!

allan

I really thought that would work. I'll construct an example here and try to replicate the issue. Will let you know how I get on with that.

Allan

alpaltunel

Hi Allan

did you have any chance to check situation? if helps I can send you database schema and sample data.

allan

I haven't yet I'm afraid. If you could send me the schema that would be really helpful. The full PHP that you are using and also Javascript might be useful as well. allan @ this-domain.net will get to me.

Allan