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

By "Zendy" I mean using the abstraction that Zend provides, rather than writing a direct query, as above. — Sonny
– Sonny, Commented Apr 7, 2010 at 22:00

Gordon · Accepted Answer · 2010-04-07 21:52:02Z

4

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.

edited Apr 7, 2010 at 21:52

answered Apr 7, 2010 at 21:44

Gordon

318k76 gold badges548 silver badges566 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sonny Over a year ago

Were there specific ZF components that you especially had performance issues with?

Gordon Over a year ago

@Sonny yes, Zend_Date performed poorly for us compared to DateTime and we use it only to format dates for a certain locale now. Also, the usual suspects: PluginBroker, Zend_Loader and the ViewHelpers when used through the the __call interface. Another wrong choice was using Zend_Config for processing various XML files. It's not that it's a bad component, but in our case DOM was more appropriate and easier. I also find myself using filter_var over Zend_Filter or Zend_Validate. Just to make this clear though: I like ZF and I think it's great when used appropriately.

Collectives™ on Stack Overflow

Zend Database Adapter - Complex MySQL Query

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related