Issue to create row with column id primary key sequence (postgreSQL)

Mateo09

Hi,

I'm writing a message here because after a lot of searching on the forum, I couldn't find a solution to my problem. I have a table where I want to display some columns of my datatable. In this columns, I show also the 'id' which is the primary key of the table. I use Editor so I have put a button Duplicate (close to Create) for my Datatable. My database is in PostgreSQL.
The problem is for the column 'id', I canno't let the user choose it. So I have two choice :

• After the submit form, get the max id of my database and put max + 1 for each row.
• Implement a sequence for 'id'.

The second solution seems to be the best. So I saw a few threads on the forum about this but it doesn't work. The solution is to put set(false) to the field 'id'. Here is an extract of my code the php script :

Editor::inst($db, 'backup_ref.backup_parameters', 'id')
    ->fields(
        Field::inst('id')->set(false),
        Field::inst('client_name')
            ->validator(Validate::notEmpty(
                ValidateOptions::inst()
                //->message('A client name is required')
            )),
        Field::inst('backup_type')
            ->validator(Validate::notEmpty(
                ValidateOptions::inst()
                //->message('A backup type is required')
            )),
        Field::inst('object_type')
            ->validator(Validate::notEmpty(
                ValidateOptions::inst()
                //->message('A object type is required')
            )),
...

(I also don't put the id in the fields of editor to prevent it from being in the form)

So, when I submit, an error appear :
An SQL error occurred: SQLSTATE[23502]: Not null violation: 7 ERREUR: une valeur NULL viole la contrainte NOT NULL de la colonne << id >> dans la relation << backup_parameters >>DETAIL: La ligne en 'echec contient (null, devlin09.fre.dns, LINUX, FILESYSTEM, DEVLIN09, FULL, DC8_PRO_LINUX, Fset_DEVLIN09_EXCL, B, POOL_DC8_PROBRA07_DEDUP_6M_NOREP, , , , null, null, Y).

The reason is when I put Field::inst('id')->set(false) the value 'id' become null but in PostgreSQL, if we put "null" it doesn't work.

My question, for the second solution is, is there a solution to remove the first column in the insert ? Like this : (devlin09.fre.dns, LINUX, FILESYSTEM, DEVLIN09, FULL, DC8_PRO_LINUX, Fset_DEVLIN09_EXCL, B, POOL_DC8_PROBRA07_DEDUP_6M_NOREP, , , , null, null, Y).
(The database will automatically put the next 'id' of the sequence)

Maybe you have other solution ? If I wasn't clear enought, don't hesitate to ask me for more details.

Thanks in advance and sorry if I made spelling or grammar mistakes in English.

Matéo

allan

Hi Matéo,

 Field::inst('id')->set(false)

should do it. I presume you haven't included an id field in the fields list at the client-side for Editor?

Allan

Mateo09

Hi allan,

Yes actually, I didn't include it :

$(function () {
    var editor = new DataTable.Editor({
        "ajax": '../../controllers/editor/editor_bparameters.php',
        fields: [
            {
                label: 'client_name:',
                name: 'client_name',
            },
            {
                label: 'backup_type',
                name: 'backup_type'
            },
            {
                label: 'object_type:',
                name: 'object_type'
            },
...

I just show the column 'id' for the table :

var table = $("#listSauvegarde").DataTable({
               columns: [
            { data: 'id' },
            { data: 'client_name' },
            { data: 'backup_type' },
            { data: 'object_type' },
         ...
        ],
    });

allan

That's perfect - that's what I think the client-side should be! We need to track down the error on the server-side in that case I'm afraid.

Could you add ->debug(true) just before the ->process(...) call? Then attempt the action that is causing the error and show me the full JSON response from the server to that request. It will include the SQL that is being generated which will hopefully give me a clue as to where to look for further information.

Allan

Mateo09

I found my error, ultimately nothing to do with the code. It was totally my fault, I had poorly implemented the sequence in my database.
Sorry for the waste of time and thank you for your quick responses Allan, such fast and involved support is not always the case and it's a pleasure

allan

Ah! Great to hear you got it tracked down and it is all working now

Allan