zend framework update from another table

Question

I got model in Zend Framework what extends Zend_Db_Table where $this->_name = 'tableA'.

It is very nice how long I doing insert(), update(), or delete(). How I can realize updating main table based on value from another table.. ?

In raw SQL query it could looks like this:

UPDATE tableA SET fieldA = tableB.newValue
FROM tableB
WHERE tableA.someValue = tableB.someIndex // it will be complicate manipulation
  AND tableA.index = .....

how I can build params for update() method:

parent::update( $data, $where );

score 1 · Accepted Answer · answered Jul 23 '11 at 03:50

There are no possible combinations of how to build params for the parent::update() method to get that final update query. The reason is because the Db Table update method just passes along your $data and $where variables to the Db Adapter's update method. The adapter's update method leaves no room for attaching additional information. You can't hack params at all

If you can't use table relationships with cascade update. Your best bet will be to extend the Db Adapter and create a new method to handle these types of updates. This should work.

/** 
 * Add this method to you custom adapter
 * Direct copy of update method with integration of $from
 * @see Zend_Db_Adapter_Abstract::update
 **/
public function updateFrom($table, $from, array $bind, $where = '')
{
    /**
     * Build "col = ?" pairs for the statement,
     * except for Zend_Db_Expr which is treated literally.
     */
    $set = array();
    $i = 0;
    foreach ($bind as $col => $val) {
        if ($val instanceof Zend_Db_Expr) {
            $val = $val->__toString();
            unset($bind[$col]);
        } else {
            if ($this->supportsParameters('positional')) {
                $val = '?';
            } else {
                if ($this->supportsParameters('named')) {
                    unset($bind[$col]);
                    $bind[':col'.$i] = $val;
                    $val = ':col'.$i;
                    $i++;
                } else {
                    /** @see Zend_Db_Adapter_Exception */
                    require_once 'Zend/Db/Adapter/Exception.php';
                    throw new Zend_Db_Adapter_Exception(get_class($this) ." doesn't support positional or named binding");
                }
            }
        }
        // Reason #1 you can't hack into $data array to pass reference to a table
        $set[] = $this->quoteIdentifier($col, true) . ' = ' . $val;
    }

    $where = $this->_whereExpr($where);

    /**
     * Build the UPDATE statement
     */
    $sql = "UPDATE "
         . $this->quoteIdentifier($table, true)
         . ' SET ' . implode(', ', $set)
         . ' FROM ' . $this->quoteIdentifier($from, true) // My only edit
         . (($where) ? " WHERE $where" : ''); // Reason #2 no room in where clause

    /**
     * Execute the statement and return the number of affected rows
     */
    if ($this->supportsParameters('positional')) {
        $stmt = $this->query($sql, array_values($bind));
    } else {
        $stmt = $this->query($sql, $bind);
    }
    $result = $stmt->rowCount();
    return $result;
}

/** Add this to your extended Zend_Db_Table **/
public function update(array $data, $where)
{
    $tableSpec = ($this->_schema ? $this->_schema . '.' : '') . $this->_name;
    $from      = 'schema.name'; // Get from table name 
    return $this->_db->updateFrom($tableSpec, $from, $data, $where);
}

Note: I didn't test this out, but I am pretty confident it will work as expected. If you have any problems, just let me know. Because I copied the adapter's update method, I went ahead and added notes of the reasons why you can't hack those params.

EDIT I almost forgot to mention. Every adapter is unique so you will have to check with your adapters update method. I just copied from Zend_Db_Abstract.

score 0 · Answer 2 · answered Jul 23 '11 at 02:54

0

I think you need this: http://framework.zend.com/manual/en/zend.db.table.relationships.html

answered Jul 23 '11 at 02:54

Erik

559
1
8
17

zend framework update from another table

2 Answers2