Zend_Db_Select用于更新/删除查询

While working on a mapping structure for our applications we ran into some trouble regarding the code consistency. While it's easy to make select query's with the Zend_Db_Select class (with functions like: $select->from('table')->where('id=?,1), it wouldn't work for update/delete query's. There isn't a class like Zend_Db_Update or Zend_Db_Delete to build the update and delete query's like you build the select. To fix this we extended the Zend_Db_Select class as you can see in the code below. The code shows the custom class that extends the Zend_Db_Select class with some minimal example code at the bottom to show how it's used.

在为我们的应用程序开发映射结构时，我们在代码一致性方面遇到了一些麻烦。虽然很容易使用Zend_Db_Select类进行选择查询（函数如：$ select-> from（'table'） - > where（'id =？，1），但它不适用于更新/删除查询。不是像Zend_Db_Update或Zend_Db_Delete这样的类来构建更新和删除查询就像构建select一样。为了解决这个问题，我们扩展了Zend_Db_Select类，如下面的代码所示。代码显示了扩展Zend_Db_Select类的自定义类底部有一些最小的示例代码，以显示它是如何使用的。

<?php
class Unica_Model_Statement extends Zend_Db_Select
{
    public function __construct($oMapper)
    {
        parent::__construct($oMapper->getAdapter());
        $this->from($oMapper->getTableName());
    }


    /**
     * @desc Make a string that can be used for updates and delete
     *       From the string "SELECT `column` FROM `tabelnaam` WHERE `id` = 1" only the part "`id = `" is returned. 
     * @return string 
     */
    public function toAltString()
    {
        $sQuery = $this->assemble();        // Get the full query string
        $sFrom = $this->_renderFrom('');    // Get the FROM part of the string

        // Get the text after the FROM (and therefore not using the "SELECT `colname`" part)
        $sString = strstr($sQuery,$sFrom);

        // Delete the FROM itself from the query (therefore deleting the "FROM `tabelnaam`" part)
        $sString = str_replace($sFrom, '', $sString);

        // Delete the word "WHERE" from the string.
        $sString = str_replace('WHERE', '', $sString);

        return $sString;
    }
}

################################################
# Below code is just to demonstrate the usage. #
################################################

class Default_IndexController extends Zend_Controller_Action
{
    public function indexAction()
    {
        $person = new Unica_Model_Person_Entity();
        $statement = new Unica_Model_Statement($person->getMapper());
        $statement->where('id = ?' , 1);
        $person->getMapper()->delete($statement);
    }
}

class Unica_Model_Person_Mapper
{
    public function delete($statement)
    {
        $where = $statement->toAltString();
        $this->getAdapter()->delete($this->_tableName,$where);
    }
}

Everything works fine using this class but it got us wondering if we were maybe missing something. Is there a reason there aren't default update/delete classes like there is for select and will using this class give us trouble elsewhere?

使用这个课程一切正常，但它让我们想知道我们是否可能遗漏了一些东西。有没有原因没有默认的更新/删除类，就像有选择，并将使用这个类给别的麻烦吗？

Advice would be appreciated. Thanks in advance,

建议将不胜感激。提前致谢，

Ilians

Ilians

2 个解决方案

#1

The class is fine if you are sure you are not going to make it evolve too much in the future. I assume your approach is to benefit from the automatic quoting in the Zend_Db_Select class. In my humble opinion, however, it has some design pitfalls that could lead to extensibility troubles if you need to modify or extend it:

如果你确定你将来不会让它进化太多，那么这门课很好。我假设您的方法是受益于Zend_Db_Select类中的自动引用。但是，根据我的拙见，它有一些设计缺陷，如果你需要修改或扩展它，可能会导致可扩展性问题：

• It's receiving some data that is discarded afterwards (the entity object, to use the "from" clause).
• 它正在接收一些之后被丢弃的数据（实体对象，使用“from”子句）。
• It's manipulating directly the SQL output of the select query, which can be something dangerous to rely upon. If the format changes, and also if you need to include more elements to the where clause, your code could get quite "muddy" to adapt to the changes.
• 它直接操作select查询的SQL输出，这可能是一个危险的依赖。如果格式发生变化，并且如果您需要在where子句中包含更多元素，那么您的代码可能会非常“混乱”以适应更改。

My approach would just be to use the where clauses directly in the code, and quote them wherever it's necessary. It doesn't look particularly less clean to me. Something like the following:

我的方法只是直接在代码中使用where子句，并在必要时引用它们。它对我来说看起来不那么干净。类似于以下内容：

$db->delete('tablename', 'id = ' . $db->quote('1'));

Eventually, you could even abstract it into a method or class, to avoid having to spread $db->quote() calls all over the place, something like that:

最终，您甚至可以将其抽象为方法或类，以避免在整个地方传播$ db-> quote（）调用，类似于：

private function myDelete($tableName, $fieldName, $fieldValue)
{
    $this->db->delete($tableName, $fieldName . ' = ' . $db->quote($fieldValue));
}

EDIT: Including multiple clauses in the where part would make it a bit more involved, and how flexible you want to be will depend on your particular application. For instance, a possible solution for several "ANDed" clauses could be the following:

编辑：在where部分中包含多个子句会使其更加复杂，并且您希望的灵活性取决于您的特定应用程序。例如，几个“ANDed”子句的可能解决方案可能如下：

private function myDelete($tableName, array $fields)
{
    $whereClauses = array();
    foreach ($fields as $fieldName => $fieldValue) {
       $whereClauses[] = $fieldName . ' = ' . $db->quote($fieldValue);
    }
    $this->db->delete($tableName, implode(' AND ', $whereClauses));
}

$this->myDelete('tablename', array('id' => '1', 'name' => 'john'));

Hope that helps,

希望有所帮助，

#2

I dont know the exact reasoning behind Zend_Db_Select not offering CUD methods. The obvious explanation is that Select means you should use it for just that: dynamic query building. To insert, update and delete you would either use Zend_Db_Adapter or the proxy methods to it in Zend_Db_Table and Zend_Db_Table_Row or the generic Zend_Db_Statement.

我不知道Zend_Db_Select背后没有提供CUD方法的确切原因。显而易见的解释是，Select意味着你应该只使用它：动态查询构建。要插入，更新和删除，您可以在Zend_Db_Table和Zend_Db_Table_Row或通用Zend_Db_Statement中使用Zend_Db_Adapter或其代理方法。

However, with that said, I think there is nothing wrong with extending Zend_Db_Select and adjusting it your needs (just like any other component that doesnt do what you want initially)

然而，话虽如此，我认为扩展Zend_Db_Select并调整它的需求没有任何问题（就像任何其他组件最初没有做你想做的那样）