Editor: problems creating new record

WaWJohn · April 2016

Thank you Allan for your response to my previous questions, in which I presented a simplified three-field table. Following your recommendations, that isolated test case worked, as did the rest of my application, until now.

However, when trying to integrate that automatic default sequence auto-increment solution into my real-world problem, I couldn't make it work. Since I'm pretty sure that the problem lies at my end, I've paid a 99€ quick support to compensate you for your time looking at my specific situation.

I'll start by describing what I want to do. I'm writing a budget application for a French-speaking public organisation. Subsidies and expenditures have, in addition to the budget year, foreign-key attributes pointing to three tables: direction for the directorate receiving the subsidy or making the expenditure, pouvoir for the subsidising power which we will use as columns for a cross-tab output, and type_depense as the type of expenditure.

The budget application is actually quite simple. For each year (annee), subsidies are summed by direction, pouvoir, and type_depense. Expenditures are summed by the same three keys, and the budget is simply the difference between the sum of the subsidies minus the sum of the expenditures, grouped by direction, pouvoir, and type_depense.

I'm using PostgreSQL as the backend for this project, so I simply create views with the information I need. I can use cross tab functions to output spreadsheet-like columnar data, or a sub-select to create a virtual column summing the amounts of related child records.

I use Datables to present these views, and Editor to modify the underlying tables and create new records.

This approach has been successful for the subsidies side of the application. I can edit and create these records. These records have an auto-increment sequence global ID gid as a default SQL value, in addition to their five-field compound primary key, so I simply do not submit gid on record creation. The Postgres backend automatically adds the default gid. This has been working properly, after reading your response to my previous question.

For expenditures (depenses) the situation is different: a depense is the actual cash outlay or bank transfer amount for the that expenditure, with an accompanying invoice or receipt. To break down that depense by direction, pouvoir, and type_depense, we (in a very French manner) cut it up into slices or tranches.

I envision the following workflow: the user pays for a service to goods and receives an invoice, which is the basis of a depense entered into the budget system. The user creates a new depense record, and is responsible for giving it a unique alphanumeric code_depense. In this case, I just say that it's the user's responsibility to use a unique key, otherwise the database throws an error. This is the type of hybrid local/remote variable I mentioned at the end of my previous question, but here I'm just tossing the ball back to the user.

They choose the supplier (fournisseur) and the payment method (moyen_de_paiement). They click to create the new depense record. At that point, they must then assign one or more parts of that depense to a triplet of direction, pouvoir, and type_depense in tranche records. This is where each new tranche record is created.

I have set up a DataTables instance and PHP back end that works just as expected: if the user tries to create a record with a duplicate code_depense, Editor throws a SQLSTATE[23505]: Unique violation: 7 ERROR: duplicate key value violates unique constraint error. Then manually modifying this code_depense key and resubmitting also works: the record is created as expected.

But my problem lies with the next level, where we slice up these depenses into tranches. This table has a compound primary key: code_depense pointing to the parent depense record, then the three budgetary keys: direction, pouvoir, and type_depense. Since as far as I know neither DataTables nor Editor can work with compound primary keys, I added a gid field to my tranches table, just as I did for the subsides table described above.

When I submit the new tranches record, it blocks with the message SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input syntax for integer just like I was submitting a text value (it seems to be using the value of code_depense) to the gid field. However, I'm not submitting that field, and I've added ->set(false) as well to the PHP code. I also checked the submission in the browser debugger, and only the fields I want set are sent.

This, here, Allan is where I've been stuck for three days now. I ran the debugger on it, but didn't see anything I could find useful. I'll of course load it again if you want. Should I then send the link to you by private email?

Here's what seems useful to me to describe the environment, but I seem to have attained some kind of post limit. I'll be glad to supply any additional info you need.

This is a view with a virtual column, based on the amount as yet unattributed called depenses_non_ventiles from a sub-select of related records. This view is used successfully to view and modify records in Datables and Editor, and shows the calculated amount based on tranches related to that depense.

DROP VIEW IF EXISTS  v_depenses;
CREATE OR REPLACE VIEW v_depenses
 AS (
select 
    annee,
    code_depense,
    date_depense,
    code_moyen_de_paiement,
    code_fournisseur,
    montant_depense,
    montant_depense - (     
        SELECT sum(montant_tranche)
        FROM tranches
        WHERE depenses.code_depense = tranches.code_depense
    ) AS depenses_non_ventiles
from 
    depenses 
order by 1,2
)
;

var editor_tranches; // use a global for the submit and return data rendering
var editor_tranches_new; // use a global for the submit and return data rendering

$(document).ready(function() {
// --------------------------------------------------------------
    editor_tranches = new $.fn.dataTable.Editor( {
        ajax: "php/tranches-data.php",
        table: "#tranches",
        fields: [  {
                label: "Code dépense:",
                name: "tranches.code_depense",
                type: "select",
                placeholder: "Choissez la code dépense pour associer cette tranche à un autre dépense"
            }, {
                label: "Direction:",
                name: "tranches.code_direction",
                type: "select",
                placeholder: "Choissez la Direction"
            }, {
                label: "Pouvoir:",
                name: "tranches.code_pouvoir",
                type: "select",
                placeholder: "Choissez le Pouvoir Subsidiant"
            }, {
                label: "Type de dépense:",
                name: "tranches.code_type_depense",
                type: "select",
                placeholder: "Choissez le type de dépense"
            }, {
                label: "Montant tranche:",
                name: "tranches.montant_tranche"
            }
        ]
    } );


    $('#tranches').DataTable( {
        dom: "Blfrtip",
        ajax: "php/tranches-data.php",
        columns: [
            { data: "tranches.code_depense" },
            { data: "v_depenses.annee" },
            { data: "v_depenses.date_depense" },
            { data: "moyens_de_paiement.nom_moyen_de_paiement" },
            { data: "fournisseurs.nom_fournisseur" },
            { data: "v_depenses.montant_depense", render: $.fn.dataTable.render.number( '.', ',', 2, '€' ) },
            { data: "v_depenses.depenses_non_ventiles", render: $.fn.dataTable.render.number( '.', ',', 2, '€' ) },
            { data: "tranches.code_direction" },
            { data: "tranches.code_pouvoir" },
            { data: "tranches.code_type_depense" },
            
            { data: "tranches.montant_tranche", render: $.fn.dataTable.render.number( '.', ',', 2, '€' ) },
            
        ],
        select: true,
        buttons: [
            { extend: "create", editor: editor_tranches },
            { extend: "edit",   editor: editor_tranches },
            { extend: "remove", editor: editor_tranches }
        ]
    } );
} );

allan · April 2016

Hi,

Thanks for picking up the DataTables support option and the details of the issue you are facing!

Since as far as I know neither DataTables nor Editor can work with compound primary keys

DataTables itself doesn't really "care" about what the primary key is, or even if there is a primary key. It will just accept an array of data it needs to display. Indeed Editor on the client-side is the same, where it breaks down is Editor's PHP server-side libraries. They currently do not support compound keys (although that will change with Editor 1.6 in a few months time).

So if you only need to view this particular piece of data and not edit it, you could simply query the database directly to get the data (rather than using Editor's PHP libraries to get it). However, if you do need to edit it we'll need to address this issue.

Does the gid have a default value assigned to in the SQL schema (presumably a uuid or a sequence value in this case)? Can you insert into the table if you can't using Editor (i.e. a straight INSERT command)?

Regards,
Allan

WaWJohn · April 2016

So if you only need to view this particular piece of data and not edit it, you could simply query the database directly to get the data (rather than using Editor's PHP libraries to get it).

That's exactly what I do for all the "view-only" tables. I have my usual data access functions which return json data for DataTables. I will later use a separate database user with read-only privileges to access the view-only derived tables.

However, if you do need to edit it we'll need to address this issue.

In this application, I am using Editor to alter only the three underlying data tables: subsides, depenses, and tranches. Both subsides and tranches have multi-column compound keys, and an added gid default sequence for use with Editor only. The subsides table works fine, I just add the record without specifying the gid and it works with Editor. I am only encountering problems with tranches.

Does the gid have a default value assigned to in the SQL schema (presumably a uuid or a sequence value in this case)? Can you insert into the table if you can't using Editor (i.e. a straight INSERT command)?

Let's first look at what we have in the parent depenses table

cles_cdd=> select * from depenses 
where code_depense ilike 'aa%' order by 2;
 annee | code_depense  | date_depense | montant_depense | code_moyen_de_paiement | code_fournisseur
-------+---------------+--------------+-----------------+------------------------+------------------
  2016 | aaman-1002    | 2015-01-15   |         1225.71 | a-completer            | electrabel
  2016 | aanew         | 2016-01-03   |           10.00 | caisse                 | adde
  2016 | aanewtest-001 | 2016-01-03   |           22.00 | caisse                 | adde
  2016 | aanewx        | 2016-01-11   |           10.00 | virement               | adde
(4 rows)

And related tranches child records

cles_cdd=> select * from tranches 
where code_depense ilike 'aa%' order by 1;
 code_depense | code_pouvoir | code_direction | code_type_depense | montant_tranche |   gid
--------------+--------------+----------------+-------------------+-----------------+---------
 aaman-1002   | P06          | DG             | gazmazeleceau     |         1225.71 | 7000134
(1 row)

We can now attempt a tranches insertion with a non-existent depense foreign key:

cles_cdd=> insert into tranches (code_depense,code_pouvoir,code_direction,code_type_depense,montant_tranche) 
values ('does_not_exist','P06','DG','gazmazeleceau',66);
ERROR:  insert or update on table "tranches" violates foreign key constraint "depenses_fk"
DETAIL:  Key (code_depense)=(does_not_exist) is not present in table "depenses".

But when we put in proper values for all foreign keys, but do NOT give a value for gid (or even mention it anywhere):

cles_cdd=> insert into tranches (code_depense,code_pouvoir,code_direction,code_type_depense,montant_tranche) 
values ('aanewx','P06','DG','gazmazeleceau',66);
INSERT 0 1

And we see it here inserted, with a shiny new gid

cles_cdd=> select * from tranches where code_depense ilike 'aa%' order by 1; code_depense | code_pouvoir | code_direction | code_type_depense | montant_tranche |   gid
--------------+--------------+----------------+-------------------+-----------------+---------
 aaman-1002   | P06          | DG             | gazmazeleceau     |         1225.71 | 7000134
 aanewx       | P06          | DG             | gazmazeleceau     |           66.00 | 7000307
(2 rows)

And maybe not particularly useful, here are the definitions of the tables:

CREATE TABLE depenses
(
  annee smallint NOT NULL,
  code_depense text NOT NULL,
  date_depense date NOT NULL,
  montant_depense numeric(10,2),
  code_moyen_de_paiement text,
  code_fournisseur text,
  CONSTRAINT pk_depenses PRIMARY KEY (code_depense),
  CONSTRAINT fournisseurs_fk FOREIGN KEY (code_fournisseur)
      REFERENCES fournisseurs (code_fournisseur) MATCH FULL
      ON UPDATE CASCADE ON DELETE SET NULL,
  CONSTRAINT moyens_de_paiement_fk FOREIGN KEY (code_moyen_de_paiement)
      REFERENCES moyens_de_paiement (code_moyen_de_paiement) MATCH FULL
      ON UPDATE CASCADE ON DELETE SET NULL
)

CREATE TABLE tranches
(
  code_depense text NOT NULL,
  code_pouvoir text NOT NULL,
  code_direction text NOT NULL,
  code_type_depense text NOT NULL,
  montant_tranche numeric(10,2),
  gid integer NOT NULL DEFAULT nextval('gid_sequence'::regclass),
  CONSTRAINT pk_tranches PRIMARY KEY (code_depense, code_pouvoir, code_direction, code_type_depense),
  CONSTRAINT depenses_fk FOREIGN KEY (code_depense)
      REFERENCES depenses (code_depense) MATCH FULL
      ON UPDATE CASCADE ON DELETE SET NULL,
  CONSTRAINT directions_fk FOREIGN KEY (code_direction)
      REFERENCES directions (code_direction) MATCH FULL
      ON UPDATE CASCADE ON DELETE SET NULL,
  CONSTRAINT pouvoirs_fk FOREIGN KEY (code_pouvoir)
      REFERENCES pouvoirs (code_pouvoir) MATCH FULL
      ON UPDATE CASCADE ON DELETE SET NULL,
  CONSTRAINT type_depense_fk FOREIGN KEY (code_type_depense)
      REFERENCES type_depense (code_type_depense) MATCH FULL
      ON UPDATE CASCADE ON DELETE SET NULL,
  CONSTRAINT uk_tranches UNIQUE (code_pouvoir, code_direction, code_type_depense, code_depense)
)

WaWJohn · April 2016

Here's the editor javascript code. Notice that I've actually created two editor variables, with different PHP ajax data sources, one for modifying the record, and one for record creation, in an attempt to get this working. Neither variant works for record creation. I'll include the two PHP scripts later.

var editor_tranches; // use a global for the submit and return data rendering
var editor_tranches_new; // use a global for the submit and return data rendering

$(document).ready(function() {
// --------------------------------------------------------------
    editor_tranches = new $.fn.dataTable.Editor( {
        ajax: "php/tranches-data.php",
        table: "#tranches",
        fields: [  {
                label: "Code dépense:",
                name: "tranches.code_depense",
                type: "select",
                placeholder: "Choissez la code dépense pour associer cette tranche à un autre dépense"
            }, {
                label: "Direction:",
                name: "tranches.code_direction",
                type: "select",
                placeholder: "Choissez la Direction"
            }, {
                label: "Pouvoir:",
                name: "tranches.code_pouvoir",
                type: "select",
                placeholder: "Choissez le Pouvoir Subsidiant"
            }, {
                label: "Type de dépense:",
                name: "tranches.code_type_depense",
                type: "select",
                placeholder: "Choissez le type de dépense"
            }, {
                label: "Montant tranche:",
                name: "tranches.montant_tranche"
            }
        ]
    } );

    editor_tranches_new = new $.fn.dataTable.Editor( {
        ajax: "php/tranches-only-data.php",
        table: "#tranches",
        fields: [  {
                label: "Code dépense:",
                name: "tranches.code_depense",
                type: "select",
                placeholder: "Choissez la code dépense pour associer cette tranche à un autre dépense"
            }, {
                label: "Direction:",
                name: "tranches.code_direction",
                type: "select",
                placeholder: "Choissez la Direction"
            }, {
                label: "Pouvoir:",
                name: "tranches.code_pouvoir",
                type: "select",
                placeholder: "Choissez le Pouvoir Subsidiant"
            }, {
                label: "Type de dépense:",
                name: "tranches.code_type_depense",
                type: "select",
                placeholder: "Choissez le type de dépense"
            }, {
                label: "Montant tranche:",
                name: "tranches.montant_tranche"
            }
        ]
    } );

    $('#tranches').DataTable( {
        dom: "Blfrtip",
        ajax: "php/tranches-data.php",
        columns: [
            { data: "tranches.code_depense" },
            { data: "v_depenses.annee" },
            { data: "v_depenses.date_depense" },
            { data: "moyens_de_paiement.nom_moyen_de_paiement" },
            { data: "fournisseurs.nom_fournisseur" },
            { data: "v_depenses.montant_depense", render: $.fn.dataTable.render.number( '.', ',', 2, '€' ) },
            { data: "v_depenses.depenses_non_ventiles", render: $.fn.dataTable.render.number( '.', ',', 2, '€' ) },
            { data: "tranches.code_direction" },
            { data: "tranches.code_pouvoir" },
            { data: "tranches.code_type_depense" },
            
            { data: "tranches.montant_tranche", render: $.fn.dataTable.render.number( '.', ',', 2, '€' ) },
            
        ],
        select: true,
        buttons: [
            { extend: "create", editor: editor_tranches_new },
            { extend: "edit",   editor: editor_tranches },
            { extend: "remove", editor: editor_tranches }
        ]
    } );
} );

The corresponding HTML for the tranches table

            <table id="tranches" class="display compact" cellspacing="0" width="100%">
                <thead>
                    <tr>
                        <th>Code dépense</th>
                        <th>Annee</th>
                        <th>Date dépense</th>
                        <th>Moyen de paiement</th>
                        <th>Fournisseur</th>
                        <th>Montant dépense</th>
                        <th>Dépenses non ventilés</th>
                        <th>Direction</th>
                        <th>Pouvoir</th>
                        <th>Type de dépense</th>
                        <th>Montant tranche</th>
                    </tr>
                </thead>
                <tfoot>
                    <tr>
                        <th>Code dépense</th>
                        <th>Annee</th>
                        <th>Date dépense</th>
                        <th>Moyen de paiement</th>
                        <th>Fournisseur</th>
                        <th>Montant dépense</th>
                        <th>Dépenses non ventilés</th>
                        <th>Direction</th>
                        <th>Pouvoir</th>
                        <th>Type de dépense</th>
                        <th>Montant tranche</th>
                    </tr>
                </tfoot>
            </table>

And here is the (working) PHP ajax source for viewing and editing, based on a postgres view, in the referenced file php/tranches-data.php :

$editor = Editor::inst( $db, 'tranches', 'gid' )
    ->fields(
        Field::inst( 'tranches.gid' )->set(false),
        Field::inst( 'tranches.code_pouvoir' )
            ->options('pouvoirs','code_pouvoir','nom_pouvoir')
            ->validator( 'Validate::dbValues' ),
            
        Field::inst( 'tranches.code_direction' )
            ->options('directions','code_direction','nom_direction')
            ->validator( 'Validate::dbValues' ),
            
        Field::inst( 'tranches.code_type_depense' )
            ->options('type_depense','code_type_depense','nom_type_depense')
            ->validator( 'Validate::dbValues' ),
            
        Field::inst( 'tranches.code_depense' )
            ->options('v_depenses','code_depense','code_depense')
            ->validator( 'Validate::dbValues' ),
        
        Field::inst( 'v_depenses.annee' )->set( false ),
        
        Field::inst( 'v_depenses.date_depense' )
            ->getFormatter( 'Format::date_sql_to_format', Format::DATE_ISO_8601 )
            ->set( false ),
        
        Field::inst( 'v_depenses.code_moyen_de_paiement' )->set( false ),
        Field::inst( 'moyens_de_paiement.nom_moyen_de_paiement' )->set( false ),

        Field::inst( 'v_depenses.code_fournisseur' )->set( false ),
        Field::inst( 'fournisseurs.nom_fournisseur' )->set( false ),
        
        Field::inst( 'v_depenses.montant_depense' )->set( false ),
        Field::inst( 'v_depenses.depenses_non_ventiles' )->set( false ),
        Field::inst( 'tranches.montant_tranche' )->validator( 'Validate::numeric' )

    )
    
    ->leftJoin( 'v_depenses', 'v_depenses.code_depense', '=', 'tranches.code_depense' )
    ->leftJoin( 'moyens_de_paiement', 'moyens_de_paiement.code_moyen_de_paiement', '=', 'v_depenses.code_moyen_de_paiement' )
    ->leftJoin( 'fournisseurs', 'fournisseurs.code_fournisseur', '=', 'v_depenses.code_fournisseur' )
    ->leftJoin( 'directions', 'directions.code_direction', '=', 'tranches.code_direction' )
    ->leftJoin( 'pouvoirs', 'pouvoirs.code_pouvoir', '=', 'tranches.code_pouvoir' )
    ->leftJoin( 'type_depense', 'type_depense.code_type_depense', '=', 'tranches.code_type_depense' )
    ;
    
// where clause logic

$editor->where('v_depenses.annee',$_SESSION['annee_budget'],'=');

$editor->where( function ( $q ) {
    $q->where_group(true);
    $q->where( 'tranches.code_direction', $_SESSION['direction_dprox'], '=' );
    $q->or_where( 'tranches.code_direction', $_SESSION['direction_dg'], '=' );
    $q->or_where( 'tranches.code_direction', $_SESSION['direction_d2l'], '=' );
    $q->where_group(false);
} );

$editor->where( function ( $q ) {
    $q->where_group(true);
    $q->where( 'type_depense.invest_ou_fonction', $_SESSION['i_f_f'], '=' );
    $q->or_where( 'type_depense.invest_ou_fonction', $_SESSION['i_f_i'], '=' );
    $q->where_group(false);
} );

// we've broken the line to make sure where clauses come before processing
$editor
    ->process( $_POST )
    ->json();

I'll put the the stripped-down Php for record creation, which STILL doesn't work in the next comment, since I've hit the character limit...

WaWJohn · April 2016

Finally, here's the stripped-down PHP for record creation php/tranches-data-only.php, which STILL doesn't work for record creation. I don't mention a view at all, only real-life tables. I tried to remove the mention of gid as the key field, but then editor objected with the complaint that it could not find the "id" field.

This is what is called as the function editor_tranches_new from the "create" button attached to the table #tranches, as described in the javascript above.

// Build our Editor instance and process the data coming from _POST
$editor = Editor::inst( $db, 'tranches', 'gid' )
    ->fields(
        Field::inst( 'tranches.gid' )->set(false),
        Field::inst( 'tranches.code_pouvoir' )
            ->options('pouvoirs','code_pouvoir','nom_pouvoir')
            ->validator( 'Validate::dbValues' ),
            
        Field::inst( 'tranches.code_direction' )
            ->options('directions','code_direction','nom_direction')
            ->validator( 'Validate::dbValues' ),
            
        Field::inst( 'tranches.code_type_depense' )
            ->options('type_depense','code_type_depense','nom_type_depense')
            ->validator( 'Validate::dbValues' ),
            
        Field::inst( 'tranches.code_depense' )
            ->options('depenses','code_depense','code_depense')
            ->validator( 'Validate::dbValues' )

    )
    
    ->leftJoin( 'depenses', 'depenses.code_depense', '=', 'tranches.code_depense' )
    ->leftJoin( 'moyens_de_paiement', 'moyens_de_paiement.code_moyen_de_paiement', '=', 'depenses.code_moyen_de_paiement' )
    ->leftJoin( 'fournisseurs', 'fournisseurs.code_fournisseur', '=', 'depenses.code_fournisseur' )
    ->leftJoin( 'directions', 'directions.code_direction', '=', 'tranches.code_direction' )
    ->leftJoin( 'pouvoirs', 'pouvoirs.code_pouvoir', '=', 'tranches.code_pouvoir' )
    ->leftJoin( 'type_depense', 'type_depense.code_type_depense', '=', 'tranches.code_type_depense' )
    ;
// where clause logic
// nothing here, we just want to create a record!

// we've broken the line to make sure where clauses come before processing
$editor
    ->process( $_POST )
    ->json();

allan · April 2016

Thanks for the extra information! With that I've just tried to create a local test case. I used the following SQL:

create sequence gid_sequence;

CREATE TABLE tranches
(
  code_depense text NOT NULL,
  code_pouvoir text NOT NULL,
  code_direction text NOT NULL,
  code_type_depense text NOT NULL,
  montant_tranche numeric(10,2),
  gid integer NOT NULL DEFAULT nextval('gid_sequence'::regclass),
  CONSTRAINT pk_tranches PRIMARY KEY (code_depense, code_pouvoir, code_direction, code_type_depense),
  CONSTRAINT uk_tranches UNIQUE (code_pouvoir, code_direction, code_type_depense, code_depense)
)

I don't have the external tables, so I removed those constraints and added a new sequence, which I presume basically matches your own.

I've used the HTML and Javascript from above. Changes in my local test case:

Removed the joins and the ->options() in the PHP
Changed the input fields to text in the Editor Javascript

With that I can insert a row without any problems. The gid is inserted correctly and shown in the table. The DataTable complains about the missing columns since there is no join and I haven't altered the initialisation of the table, but that's expected.

So I'm afraid I don't have a good answer for you immediately. We'll need to do a little debugging...

In the file php/Database/Drivers/Postgres/Query.php you'll find a function called _prepare. At the end of that function could you add:

        file_put_contents( '/tmp/editor_sql', $sql."\n", FILE_APPEND );
        file_put_contents( '/tmp/editor_sql', print_r( $this->_bindings )."\n", FILE_APPEND );

You might need to update the file name to be somewhere writable on your server.

That will dump the SQL and values that Editor is using to that file. If you then try to create a new row in the tranches table, what gets put into that file?

Thanks,
Allan

Editor: problems creating new record

Editor: problems creating new record

Replies

Howdy, Stranger!

Categories

DataTables

Editor: problems creating new record

Editor: problems creating new record

Replies

Howdy, Stranger!

Quick Links

Categories

DataTables