Is it possible, when saving a Zend_Db_Table_Row, to make ZF apply a SQL function on one column?
For example, if $row->save() generates by default this SQL query:
UPDATE table SET field = ? WHERE id = ?;
I would like it to automatically apply the GeomFromText() function on this field:
UPDATE table SET field = GeomFromText(?) WHERE id = ?;
Thanks for any hint on how to do this with Zend_Db!
Define a custom update method in your class that inherits from Zend_Db_Table (not from the Zend_Db_Table_Row) and use a Zend_Db_Expr to set the column to the function return value.
See the docs here: http://framework.zend.com/manual/en/zend.db.table.html#zend.db.table.extending.insert-update.
$row->column = new Zend_Db_Expr('GeomFromText("...")') and it worked fine. The drawback is that I need to manually escape the data though.I am just guessing but you could try this:
<?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;
}
}
?>
