enter new row with unique ID having a complex ID-schema

MadMax76

My data (I cant change, is from a software) has three fields building the unique ID. The software is doing that, the database is not configured but has to be filled right:
- stapelname:
- SUB_ID
- INP_ID

My server-php looks like that:

Editor::inst( $db, 'V_Positionen', ['INP_ID', 'Stapelname', 'SUB_ID'] )
    ->readTable('V_Positionsdaten')
    ->fields(
        Field::inst( 'SUB_ID' ),
        Field::inst( 'INP_ID' ),
        Field::inst( 'Stapelname' ),
        Field::inst( 'Positiontext' )
    )
    ->where( 'Stapelname', $selectedStapelname )
    ->where( 'INP_ID', $selectedINP_ID )
    ->debug(true)
    ->process( $_POST )
    ->json();

I want to create a new row with datatable, for a new row stapelname and INP_ID have to stay the same (the table only shows only entrys from one stapelname+INP_ID), SUB_ID has to be new - I would take max of the existing INP_IDs of the table plus 1.
Here is what I tried:

            fields: [
                { label: "INP ID", name: "INP_ID", default: "__INP_ID__", type: "hidden"},
                { label: "Stapelname", name: "Stapelname", default: "__Stapelname__", type: "hidden"},
                { label: "PosNummer - muss eindeutig sein", name: "SUB_ID",  type: "hidden",
                *default: function( oSettings, aData, iDataIndex ) {
                        var iColumn = 3;
                        var iMin = document.getElementById('min').value * 1;
                        var iMax = document.getElementById('max').value * 1;}},

I would love to implement this with new inlineCreate() ;-) , but that is another story.
So how can I acheibe this?
Thanks
Max

allan

Hi Max,

What you are looking needs a little change in the Editor PHP libraries unfortunately. Previously it was only possible to use values submitted by the client-side, but what you are looking for means that we need to allow for Field->setValue() to be used as well.

This is the change if you want to try it locally. I had planned to do an Editor 2.0.1 release this week, but that has slipped by for various reasons, so it will be next week now which will include this change.

As an example, consider our compound key example. If you wanted the date part of the primary key to be "today", then you would use:

        Field::inst( 'users_visits.visit_date' )
            ->setValue(function () {
                return date('Y-m-d');
            })
            ->getFormatter( Format::dateSqlToFormat( 'Y-m-d' ) ),

Likewise, in your own case you use use a setValue for the function to query the database and get the max value, then add one.

And this will work with inlineCreate() as well .

Allan

MadMax76

thanks, I can wait for 2.0.1!!

do I need to make a view with the max value on the server and look for this with something like

        $newpos = $editor->db()->any( 'V_Positionen_MAX', function ($q)  {
                        $q->where( 'INP ID', $myINP_ID);
                        $q->where( 'Stapelname', $myStapelname);
                    } );

or can i query the max directly?

Thanks!!!
Max

Edited by Allan - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

allan

You can run a normal SQL statement:

$newpos = $editor->db()
  ->raw()
  ->bind(':INP ID', $myINP_ID)
  ->bind(':Stapelname', $myStapelname)
  ->exec('SELECT MAX(...) FROM ... WHERE INP ID = :INP ID AND :Stapelname = Stapelname');

Allan

MadMax76

Hi Allan,

found a different way not needing a new server-request:

                { extend: 'selectedSingle',
                    text: 'Duplicate',
                    action: function () {
                        editor_pos.inlineCreate();

                        var row = table.row({selected: true}).data();
                        var rowlast = table.row( ':last-child' ).data();

                        var fields = editor_pos.displayed();

                        for (var i=0 ; i<fields.length ; i++) {
                            editor_pos.field(fields[i]).val(row[fields[i]]);
                            editor_pos.field(fields[0]).val(rowlast[fields[0]]*1 + 1);
                        } } },

of course this onöly works if the sort is right, in this case not a problem as sorting is disabled.

Thanks!
Max