INSERT/UPDATE with leftJoin and composite primary key
INSERT/UPDATE with leftJoin and composite primary key
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 },
]
},
},
});
});