INSERT/UPDATE with leftJoin and composite primary key

INSERT/UPDATE with leftJoin and composite primary key

dte1dte1 Posts: 1Questions: 1Answers: 0

I have a problem with leftJoin and a composite primary key.

"table_a" is supposed to be joined with "table_b" using two fields: "field_1" and "field_2".

Reading works quite well, but editing does not.

In debug mode, I can see that the UPDATE statement for "table_b" is not being generated at all.

When I use leftJoin with 3 parameters, everything is fine—INSERTS and UPDATES are created as expected.

But as soon as I try to complex leftJoin, no INSERT or UPDATE statements are generated.

Is what I want even possible?
This link here makes me think that it should be possible: https://editor.datatables.net/manual/php/joins#Complex-left-joins

$editor = Editor::inst( $db, 'table_a', 'table_a.id')
    ->fields(
        Field::inst( 'table_a.field_1' )->set(false),
        Field::inst( 'table_a.field_2' )->set(false),
        Field::inst( 'table_a.description' )->set(false),
        Field::inst( 'table_b.field_1' )->set(false),
        Field::inst( 'table_b.field_2' )->set(false),
        Field::inst( 'table_b.memo' )
    )
#   ->leftJoin('table_b', 'table_b.field_1 = table_a.field_1 and table_b.field_2 = table_a.field_2') // no INSERT or UPDATE statements are generated
    ->leftJoin('table_b', 'table_b.field_1', '=', 'table_a.field_1') // INSERTS and UPDATES are created as expected
    ->debug(true)
    ->process( $_POST )
    ->json();
const editor = new DataTable.Editor({
    ajax: 'sp.php',
    fields: [
        {
            label: 'field_1:',
            name: 'table_a.field_1'
        },
        {
            label: 'field_2:',
            name: 'table_a.field_2'
        },
        {
            label: 'memo:',
            name: 'table_b.memo'
        },
    ],
    table: '#table'
});


$(document).ready(function () {
    var table = $('#table').DataTable({
        select: true,
        processing: true,
        serverSide: true,
        ajax: {
            url: 'sp.php',
            type: 'POST',
        },         
        "columns": [
            {
                data: 'table_a.field_1',
                title: 'field_1'
            },
            {
                data: 'table_a.field_2',
                title: 'field_2'
            },
            {
                data: 'table_a.description',
                title: 'description'
            },
            {
                data: 'table_b.memo',
                title: 'memo'
            },
        ],    
        layout: {
            topStart: {
                buttons: [
                    { extend: 'edit', editor: editor },
                ]
            },
        },
    });
});
Sign In or Register to comment.