Zend数据库适配器-复杂的MySQL查询 [英] Zend Database Adapter - Complex MySQL Query

问题描述

我已经在我的导航模型中定义了一个执行查询的函数，我想知道是否还有更多的"Zendy"方式来生成/执行查询.我正在使用的查询是 Bill Karwin 提出的/questions/481789/maintaining-updating-record-order-in-mysql/481837#481837>此处的另一个线程用于设置任意记录顺序.我尝试使用准备好的语句，但是SIGN()函数中的值被加了引号.

I have defined a function in my Navigation model that executes a query, and I was wondering if there's a more "Zendy" way of generating/executing the query. The query I'm using was proposed by Bill Karwin on another thread here for setting arbitrary record order. I tried using a prepared statement, but the values in the SIGN() function got quoted.

我正在使用MySQL的PDO适配器.

I'm using the PDO adapter for MySQL.

/**
 *
 */
public function setPosition($parentId, $oldPosition, $newPosition)
{
    $parentId = intval($parentId);
    $oldPosition = intval($oldPosition);
    $newPosition = intval($newPosition);
    $this->getAdapter()->query("
        UPDATE `navigation`
        SET `position` = CASE `position`
            WHEN $oldPosition THEN $newPosition
            ELSE `position` + SIGN($oldPosition - $newPosition)
            END
        WHERE `parent_id` = $parentId
        AND `position` BETWEEN LEAST($oldPosition, $newPosition)
            AND GREATEST($oldPosition, $newPosition)
    ");
    return $this;
}

推荐答案

您可以使用Zend_Db_Select和/或Zend_Db_Expr，，但是如果可以正常使用，请勿更改.确实没有必要仅仅因为它们存在而使用任何ZF组件，或者使您的代码更像 Zendy .用它们来解决特定问题.

You could use Zend_Db_Select and/or Zend_Db_Expr, but if it works like it is, don't change it. There is really no need to use any of the ZF components just because they exist or to make your code more Zendy. Use them to solve a specific problem.

请记住，每次抽象都会使您的代码慢一些.可能不多，但也可能没有必要.我可以从自己在一个项目中的经验中说出来，尽管我们可以不用或更简单地完成工作，但我们尽量使用尽可能多的ZF组件.没有回报，我们发现自己后来重构了很多.

Keep in mind that every abstraction will make your code some degrees slower. Might not be much, but also might not be necessary. I can speak from my own experience from a project where we succumbed to use as many ZF components as possible, even though we could have done without and simpler. Didn't pay off and we found ourselves refactoring out a lot of them later.

这篇关于Zend数据库适配器-复杂的MySQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！