Custom primary key not working for edit and delete

Loren Maxwell

I'm using a custom primary key and my understanding is that the only change needed is to include the custom primary key field as the third parameter when initializing an instance of Editor on the server side:

// Editor instance
Editor::inst( $db, 'wiki_wikiforum_category', 'wfc_category' )

I can add record (which notably doesn't need a primary key), but I get this same error for edit and delete:

An SQL error occurred: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'id' in 'where clause'

Have I overlooked something?

rf1234

Would be good to see your where clause. The "id" field is being used there which doesn't exist and should be called 'wfc_category' because that is what you call your primary key.

Loren Maxwell

I agree.

I didn't have time to look through the Editor code to fish out the sql before I had to leave, but I did have enough to ask if I at least had everything configured properly.

I can do more digging this evening but just curious as to if I've completely missed something.

I've used DataTables quite a bit in this past year, but this is the first time with a table I didn't design (hence, no "id" field).

allan

As long as wfc_category is unique and auto incrementing, then that should be all that is needed. We'd need to see the rest of your code to understand why an id might be getting used.

Allan

Loren Maxwell

OK, I've found the offending piece of code.

I do logging according to my post "Robust audit logging": https://datatables.net/forums/discussion/46968/robust-audit-logging

Here was my code to retrieve the previous values:

$prevValues = [];
 
function getPrevValues ( $db, $table, $id ) {
    global $prevValues;
    $prevValues = $db->select( $table, '*', [ 'id' => $id ] )->fetch();
}

So that's where the 'id' is introduced and the error pops up.

I've changed the code to require the primary key and use it:

$prevValues = [];

function getPrevValues ( $db, $table, $id, $pkey ) { // Change here
    global $prevValues;
    $prevValues = $db->select( $table, '*', [ $pkey => $id ] )->fetch(); // And here
    foreach ($prevValues AS $key => $value) {
        $prevValues[$key] = ($prevValues[$key] ?: '');
    }
}

And now when I call the function I pass the primary key using the Editor (see 'preEdit' and 'preDelete'):

// Editor instance
Editor::inst( $db, 'wiki_wikiforum_category', 'wfc_category' )
    ->fields(
        Field::inst( 'wfc_category' ),
        Field::inst( 'wfc_category_name' ),
        Field::inst( 'wfc_sortkey' )
    )

    // Pre functions
    ->on( 'preCreate', function ( $editor, $values ) {
    } )
    ->on( 'preEdit', function ( $editor, $id, $values ) {
        var_dump($editor->pkey());
        getPrevValues($editor->db(), $editor->table()[0], $id, $editor->pkey()[0]); // Change here
    } )
    ->on( 'preRemove', function ( $editor, $id, $values ) {
        getPrevValues($editor->db(), $editor->table()[0], $id, $editor->pkey()[0]); // And here
    } )
    
    // Log functions
    ->on( 'postCreate', function ( $editor, $id, $values, $row ) {
        logChange( $editor->db(), $editor->table()[0], 'create', $id, $values );
    } ) 
    ->on( 'postEdit', function ( $editor, $id, $values, $row ) {
        logChange( $editor->db(), $editor->table()[0], 'edit', $id, $values );
    } )
    ->on( 'postRemove', function ( $editor, $id, $values ) {
        logChange( $editor->db(), $editor->table()[0], 'delete', $id, $values );
    } )

    ->process( $_POST )
    ->json();