Oops, I'm not sure why I used a default value there. But wow, it looks like I didn't realize how much Zend_Db_Select has come along and can bend to fit all kinds of needs. Thanks for your time Bill. Very useful.
-Adam ----- Original Message ---- From: Bill Karwin <[EMAIL PROTECTED]> To: [email protected] Sent: Thursday, October 4, 2007 5:30:46 PM Subject: RE: [fw-general] Abstracting model queries DIV { MARGIN:0px;} There should be no optimization difference between using Zend_Db_Select and writing your own SQL queries manually. All Zend_Db_Select does is build a SQL string from pieces you specify. In most cases, the resulting SQL is identical to what you would write by hand.. Here's a suggestion to answer your question about making the code more maintainable: class Post extends Zend_Db_Table_Abstract { protected $_name = 'posts'; protected function _queryActive(Zend_Db_Select $select) { $select->where('active = 1'); return $select->query(); } public function getAll() { $select = $this->getAdapter->select() ->from($this->_name, array('id', 'content', 'date')); return $this->_queryActive($select)->fetchAll(); } public function isValid($post_id) { $select = $this->getAdapter->select() ->from($this->_name, array('id')) ->where('post_id = ?', (int) $post_id); return $this->_queryActive($select)->fetchOne(); } } Note I also removed the default value for the $post_id parameter, instead making that parameter mandatory. Do you really want to allow this method to be called without a post id? Regards, Bill Karwin From: Adam Torrino [mailto:[EMAIL PROTECTED] Sent: Thursday, October 04, 2007 2:08 PM To: [email protected] Subject: [fw-general] Abstracting model queries Hey, Quick question for the design gurus of large web apps using ZF (or similar framework): In situations where an app has many special data requirements and non-trivial queries, as well as the wish to optimize queries as much as possible, it may make sense to use raw queries rather than things like Zend_Db_Select, etc. How do you keep growing and complex, raw queries maintainable? Example: In a "Post" model, you may have: # get all active posts public function getAll() { $qryPosts = "SELECT id, content, date FROM posts WHERE active = 1"; return $this->getAdapter()->query($qryPosts)->fetchAll(); } # check if a post is valid (exists in the database and is active) public function isValid($post_id = 0) { $qryIsValid = $this->getAdapter()->quoteInto("SELECT id FROM posts WHERE id = ? AND active = 1", (int)$post_id, 'INTEGER'); return $this->getAdapter()->query($qryIsValid)->fetchOne(); } You see the issues: (1) it's important to have a method like isValid() if somewhere in form validation you want to see if the post a user is trying to edit actually exists, and you want this to be a simple query for the id and not have the database return unneeded columns, but (2) as more methods are written that touch the "posts" table, they all have to include "WHERE active = 1" and any other business requirements associated with posts. How are developers using Zend Framework dealing with this and preserving maintainability? Is it ad-hoc/homebrew or do more elegant solutions exist? Yahoo! oneSearch: Finally, mobile search that gives answers, not web links. ____________________________________________________________________________________ Boardwalk for $500? In 2007? Ha! Play Monopoly Here and Now (it's updated for today's economy) at Yahoo! Games. http://get.games.yahoo.com/proddesc?gamekey=monopolyherenow
