problem deleting a row from table which is linked to a view

crush123

i have an editor instance with several left joins, one of which is to a view

i can view and edit the data fine, but if i try to delete a row, I get an error

SQLSTATE[HY000]: General error: 1288 The target table vw_wishlist_matching_item of the DELETE is not updatable

my editor instance looks like this

// Build our Editor instance and process the data coming from _POST
  $data = Editor::inst( $db, 'tblitemwishlist', 'WishlistItemID' )//table name and PKey(defaults to ID)
->field(
    Field::inst( 'tblitemwishlist.ItemSizeID' ),
    Field::inst( 'tblitemwishlist.ItemTypeID' ),
    Field::inst( 'tblitemwishlist.DateAdded' )
        ->validator( 'Validate::dateFormat', 'd M Y' )
        ->getFormatter( 'Format::date_sql_to_format', 'd M Y' )
        ->setFormatter( 'Format::date_format_to_sql', 'd M Y' ),
    Field::inst( 'tblitemwishlist.ItemSchoolID' ),
    Field::inst( 'tblitemwishlist.LastEditedBy' )
        ->setValue(isset($_SESSION['UserID'])?$_SESSION['UserID']:66),
    Field::inst( 'refitemtype.ItemTypeDescription' ),
    Field::inst( 'refsize.SizeDescription' ),
    Field::inst( 'refschool.SchoolDescription' ),
    Field::inst( 'vw_wishlist_matching_item.Available' )
        ->set( Field::SET_NONE )
    )

    ->leftJoin( 'refitemtype', 'refitemtype.ItemTypeID', '=', 'tblitemwishlist.ItemTypeID' )
    ->leftJoin( 'refsize', 'refsize.SizeID', '=', 'tblitemwishlist.ItemSizeID' )
    ->leftJoin( 'refschool', 'refschool.SchoolID', '=', 'tblitemwishlist.ItemSchoolID' )
    ->leftJoin( 'vw_wishlist_matching_item', 'tblitemwishlist.WishlistItemID', '=', 'vw_wishlist_matching_item.ItemID' )

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

the view field 'Available' is a column in my datatable, but does not occur in my editor js

editor = new $.fn.dataTable.Editor( {

    ajax: "/plugins/shop/ajax/ajax_wishlist.php",
    table: "#example",
    fields: [ {
            label: "School:",
            name: "tblitemwishlist.ItemSchoolID",
            type:  "select"
        }, {
            label: "Type:",
            name: "tblitemwishlist.ItemTypeID",
            type:  "select"
        }, {
            label: "Size:",
            name: "tblitemwishlist.ItemSizeID",
            type:  "select"
        }
    ]
} );

allan

Does the SQL user that you are connecting to the database with have access permissions to delete content on all 5 tables?

Allan

crush123

the database is run locally, there is only one user, and has all privileges

allan

I think we are going to have to debug this a bit unfortunately - it isn't an SQL error I've encountered with Editor before.

In the driver code for the database (Editor/Database/Drivers/Mysql/Query.php) you will find a commented out call to file_put_contents(). Could you comment this back in - you might also need to update the output path. That will dump the SQL that Editor attempts to run into a file. If you then perform the delete action and view the SQL, what is that SQL showing?

Thanks,
Allan

crush123

the sql generated is...

DELETE FROM  vw_wishlist_matching_item WHERE vw_wishlist_matching_item.ItemID = :where_0 

I know you can't delete from a view, but as the editor is using tblitemwishlist as its main data source, i hoped this would do just that, ie delete from the table tblitemwishlist where wishlistitemid = ?

I haven't tried yet, but if i can't do it like this, I suppose a workaround is just to add a php server behavior to delete a row from the main table and reload the page

allan

Ah I see - yes. I should have spotted that earlier - Editor doesn't support views at the moment - however, I am surprised that this is failing in this manner, and I think that is a bug (it looks like you should be able to use views successfully in a left join as you are in fact - I can't see any technical reason why not, other than the bug you are encountering).

In the Editor.php file you will find a function called _remove_table. Could you replace it with the following, which I think will address the problem:

    function _remove_table ( $table, $ids, $pkey=null )
    {
        if ( $pkey === null ) {
            $pkey = $this->_pkey;
        }

        // Check there is a field which has a set option for this table
        $count = 0;

        foreach ($this->_fields as $field) {
            if (
                $this->_part( $field->dbField(), 'table' ) === $table &&
                $field->set() !== Field::SET_NONE
            ) {
                $count++;
            }
        }

        if ( $count > 0 ) {
            $this->_db
                ->query( 'delete' )
                ->table( $table )
                ->or_where( $pkey, $ids )
                ->exec();
        }
    }

Regards,
Allan

crush123

Perfect !

Works like a charm

allan

Excellent - this will be in the next release :-)

Allan