PHP Mjoin: Insert/Edit multiple rows in multiple tables

av-technik

Hello,

I have the following use case:
I want to have a table in which I can get, add and edit information from the main table (“Messreihen”) and 3 more values (individual records) from another table (“Messwerte”).
The data in “Messwerte" points to the ID of “Messreihen”, has a column “val” (the actual value) and a column “num” (numbered from 1 to 3).
I'm using the official DataTales Editor PHP classes, but I can't get it to work as cleanly as I'd like.
For example, I want to use the validation functions regularly, but it doesn't seem to be that easy.
Editing the values of the second table doesn't seem to work because DataTables doesn't seem to know exactly which entry needs to be edited.
Is it possible to better validate/format the values from the second table and e.g. cancel the creation of the main entry if e.g. the validation or insertion of the data records in the second table does not work? Perhaps via transactions or similar?

Part of the PHP code:

    $editor
        ->join(
            Mjoin::inst('Messwerte')
                ->link('Messreihen.id', 'Messwerte.messreihe_id')
                ->field(
                    Field::inst('id')
                        ->set(Field::SET_NONE),
    
                    Field::inst('num')
                        ->set(Field::SET_NONE),
    
                    Field::inst('val')
                        // This never gets used for insert/edit?
    
                        ->validator(function ($val, $data, $field, $host) {
                            return preg_match('/^\d+[,.]?\d*$/', $val) !== false;
                        })
                        ->getFormatter(function ($val, $data, $opts) {
                            if ($val === null) return $val;
                            return str_replace('.', ',', $val);
                        })
                        ->setFormatter(function ($val, $data, $opts) {
                            $val = str_replace(',', '.', $val);
                            return floatval($val);
                        }),
                )
        )
        ->on('writeCreate', function ($editor, $id, $values) use ($db) {
            $messwerte = [];
            for ($i = 0; $i < 3; $i++) {
                $num = $i + 1;
                $val = $values['Messwerte'][$i]['val'] ?? null;
    
                // This should be above (validator, get/setFormatter)?
                if ($val !== null) {
                    $val = str_replace(',', '.', $val);
                    $val = floatval($val);
                }
    
                $messwerte[] = [
                    'messreihe_id' => $id,
                    'num' => $num,
                    'val' => $val,
                ];
            }
    
            foreach ($messwerte as $messwert) {
                $db->insert('Messwerte', $messwert);
            }
        })
        ->on('writeEdit', function ($editor, $id, $values) use ($db) {
            if (!isset($values['Messwerte'])) return;
    
            // $messwert['id'] is non exiting
            // Should I use the array index in combination with column "num"?
            // Is it ensured that the values always arrive here in the correct order? (index 0 = num 1, i 1 = n 2, ...?)
            foreach ($values['Messwerte'] as $key => $messwert) {
                $db->update(
                    'Messwerte',
                    [ 'val' => $messwert['val'] ],
                    [ 'id' => $messwert['id'] ] // [ 'messreihe_id' => $id, 'num' => $key + 1 ] // like this?
                );
            }
        });

Part of JS:

    document.addEventListener('DOMContentLoaded', () => {
        // Editor
        const editor = new DataTable.Editor({
            table: '#table',
            ajax: '******',
            fields: [
                {
                    label: 'Zeitpunkt:',
                    name: 'Messreihen.sampled_at',
                    type: 'datetime',
                    format: 'DD.MM.YYYY HH:mm',
                },
                {
                    label: 'Messwert 1:',
                    name: 'Messwerte.0.val',
                },
                {
                    label: 'Messwert 2:',
                    name: 'Messwerte.1.val',
                },
                {
                    label: 'Messwert 3:',
                    name: 'Messwerte.2.val',
                },
            ],
        });
    
        // Table
        const table = new DataTable('#table', {
            ajax: '******',
            columns: [
                {
                    data: null,
                    orderable: false,
                    render: DataTable.render.select(),
                },
                { data: 'Messreihen.sampled_at' },
                { data: 'Messwerte.0.val' },
                { data: 'Messwerte.1.val' },
                { data: 'Messwerte.2.val' },
                {
                    data: null,
                    defaultContent: '',
                    orderable: false,
                },
            ],
            layout: {
                topStart: {
                    buttons: [
                        { extend: 'create', editor: editor },
                        { extend: 'edit', editor: editor },
                        { extend: 'remove', editor: editor },
                    ],
                },
            },
            order: [[ 1, 'desc' ]],
            select: {
                style: 'os',
                selector: 'td:first-child',
            },
        });
    });

rf1234

I would recommend doing parent - child editing using field type 'datatable':
https://editor.datatables.net/examples/datatables/parentChild.html

If you need a more detailed example including the PHP parts, just let me know. I implemented a couple of these and they work fine.

Just like your "Messreihen" can have multiple "Messwerte" my "orders" can have multiple "payments". You can see that in the screenshot. The child Editor (field type 'datatable') is below the parent Editor.

allan

Yup - no question, if parent / child editing is a UI option, then that is the way to do this in Editor.

Allan