Custom primary key not working for edit and delete

Custom primary key not working for edit and delete

Loren MaxwellLoren Maxwell Posts: 382Questions: 93Answers: 10

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?

Answers

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406

    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 MaxwellLoren Maxwell Posts: 382Questions: 93Answers: 10
    edited February 2018

    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).

  • allanallan Posts: 61,431Questions: 1Answers: 10,048 Site admin

    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 MaxwellLoren Maxwell Posts: 382Questions: 93Answers: 10

    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();
    
This discussion has been closed.