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
<http://us.rd.yahoo.com/evt=48252/*http://mobile.yahoo.com/mobileweb/one
search?refer=1ONXIC> , not web links.
