WHERE clause for Editor with composite key

rdueck

I use a lot of tables with a unique key (could just as well be a primary key) composed of two fields, where the first is the table ID (which is hidden from the client-side and cannot be changed), and the second is the row number which I provide as the primary key for DataTables. This means the WHERE clause is needed for SELECT, UPDATE, and DELETE queries, but Editor-PHP-1.5.5 only applies it to the SELECT.

I have patched Editor-PHP-1.5.5/php/Editor/Editor.php as follows to fix DELETE:

function _remove_table ( $table, $ids, $pkey=null )
{
    ...
    if ( $count > 0 ) {
        $query = $this->_db
            ->query( 'delete' )
            ->table( $table )
            ->or_where( $pkey, $ids );
        $this->_get_where ($query);
        $query->exec();
    }
}

Fixing UPDATE required patches to both Editor-PHP-1.5.5/php/Database/Query.php:

public function where ( $key, $value=null, $op="=", $bind=true )
{
    ...
    else if ( is_callable($key) ) {
        $this->_where_group( true, 'AND' );
        call_user_func ($key, $this);
        $this->_where_group( false, 'OR' );
    }
    ...
}

and to Editor-PHP-1.5.5/php/Editor/Editor.php:

public function _get_where ( $query )

and

private function _insert_or_update ( $id, $values )
{
    // Loop over all tables in _table, doing the insert or update as needed
    for ( $i=0, $ien=count( $this->_table ) ; $i<$ien ; $i++ ) {
        if ($id === null) {
            $whereFn = null;
        }
        else {
            $this->where ($this->_pkey, $id);
            $whereFn = array ($this, '_get_where');
        }

        $res = $this->_insert_or_update_table(
            $this->_table[$i],
            $values,
            $whereFn
        );

        // If we don't have an id yet, then the first insert will return
        // the id we want
        if ( $id === null ) {
            $id = $res->insertId();
        }
        else {
            $lastWhere = count ($this->_where) - 1;
            unset($this->_where[$lastWhere]);
        }
    }
    ...
}

and

function _remove_table ( $table, $ids, $pkey=null )
{
    ...
    if ( $count > 0 ) {
        $query = $this->_db
            ->query( 'delete' )
            ->table( $table )
            ->or_where( $pkey, $ids );
        $this->_get_where ($query);
        $query->exec();
    }
}

allan

Superb - thanks for posting this! Support for composite keys is something that will arrive in Editor 1.6.

Regards,
Allan