在 Zend_Db_Table_Row::save() 上应用 SQL 函数

Question

保存 Zend_Db_Table_Row 时是否可以使 ZF 对一列应用 SQL 函数？

例如，如果 $row->save() 默认生成此 SQL 查询：

UPDATE table SET field = ? WHERE id = ?;

我希望它自动在此字段上应用 GeomFromText() 函数：

UPDATE table SET field = GeomFromText(?) WHERE id = ?;

谢谢有关如何使用 Zend_Db 执行此操作的任何提示！

Answer 1

在您的类中定义一个继承自 Zend_Db_Table（而不是来自 Zend_Db_Table_Row）的自定义 update 方法，并使用 Zend_Db_Expr将列设置为函数返回值。

请参阅此处的文档： http://framework.zend.com/manual/en/zend.db.table.html#zend.db.table.extending.insert-update。

Answer 2

我只是猜测，但你可以尝试这个：

<?php
class MyTable extends Zend_Db_Table_Abstract 
{
  protected $_name = 'my_table';

  public function update(array $data, $where) {
       /**
         * Build "col = ?" pairs for the statement,
         * except for Zend_Db_Expr which is treated literally.
         */
        $set = array();
        $i = 0;
        foreach ($data as $col => $val) {
            if ($val instanceof Zend_Db_Expr) {
                $val = $val->__toString();
                unset($data[$col]);
            } else {
                if ($this->_db->supportsParameters('positional')) {
                    $val = ($col == 'field') ? 'GeomFromText(?)' : '?';
                } else {
                    if ($this->_db->supportsParameters('named')) {
                        unset($data[$col]);
                        $data[':col'.$i] = $val;
                        $val = ($col == 'field') ? 'GeomFromText(:col'.$i.')' : ':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");
                    }
                }
            }
            $set[] = $this->_db->quoteIdentifier($col, true) . ' = ' . $val;
        }

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

        /**
         * Build the UPDATE statement
         */
        $sql = "UPDATE "
             . $this->_db->quoteIdentifier($this->_name , true)
             . ' SET ' . implode(', ', $set)
             . (($where) ? " WHERE $where" : '');

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

    protected function _whereExpr($where)
    {
        if (empty($where)) {
            return $where;
        }
        if (!is_array($where)) {
            $where = array($where);
        }
        foreach ($where as $cond => &$term) {
            // is $cond an int? (i.e. Not a condition)
            if (is_int($cond)) {
                // $term is the full condition
                if ($term instanceof Zend_Db_Expr) {
                    $term = $term->__toString();
                }
            } else {
                // $cond is the condition with placeholder,
                // and $term is quoted into the condition
                $term = $this->quoteInto($cond, $term);
            }
            $term = '(' . $term . ')';
        }

        $where = implode(' AND ', $where);
        return $where;
    }
}
?>