Zend Database Adapter - Complex MySQL Query

Question

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.

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;
}

Answer 1

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.

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.