/**
* Get comments select by commentAlias row
*
* @param Comments_Model_CommentAlias $commentAlias
* @param $userId
* @param int $key
* @return Zend_Db_Select
*/
public function getSelect(Comments_Model_CommentAlias $commentAlias, $userId, $key = 0)
{
$users = new Users_Model_User_Table();
$select = $this->getDbTable()->select(true);
$select->setIntegrityCheck(false)->joinLeft(array('u' => $users->info('name')), 'userId = u.id', array('login', 'avatar', 'email', 'firstname', 'lastname'))->where('aliasId = ?', $commentAlias->id)->where('comments.status = "' . Comments_Model_Comment::STATUS_ACTIVE . '"' . ' OR (comments.status != "' . Comments_Model_Comment::STATUS_ACTIVE . '"' . ' AND comments.userId = ?)', $userId);
if ($commentAlias->isKeyRequired()) {
$select->where('comments.key = ?', $key);
}
$select->order('created ASC');
return $select;
}
/**
* Get posts
*
* @param integer $categoryId
* @return Zend_Db_Table_Select
*/
public function getPostsSelect($categoryId = null)
{
$users = new Users_Model_User_Table();
$select = $this->select()->from(array('p' => $this->_name), array('*'));
$select->setIntegrityCheck(false)->joinLeft(array('u' => $users->info('name')), 'userId=u.id', array('author' => 'login'));
$select->order('p.created DESC');
$select->group('p.id');
$select->where('p.status=?', Forum_Model_Post::STATUS_ACTIVE);
if ($categoryId) {
$select->where('categoryId=?', $categoryId);
}
return $select;
}
/**
* Get Zend_Db_Table_Select
*
* @param object|integer $category
* @param integer $author
* @param string $date
* @return Zend_Db_Table_Select
*/
public function getSelect($category = null, $author = null, $date = 'NOW')
{
$users = new Users_Model_User_Table();
$categories = new Categories_Model_Category_Table();
$select = $this->select()->setIntegrityCheck(false);
$select->from(array('p' => $this->_name), array('*'))->joinLeft(array('u' => $users->info('name')), 'p.userId = u.id', array('login'))->joinLeft(array('c' => $categories->info('name')), 'c.id = p.categoryId', array('categoryTitle' => 'title', 'categoryAlias' => 'alias'))->group('p.id')->where('p.status=?', Blog_Model_Post::STATUS_PUBLISHED)->order('published DESC');
if ($date) {
if ('NOW' == $date) {
$date = date('Y-m-d H:i:s');
}
$select->where('published <=?', $date);
}
if ($category) {
if (!$category instanceof Zend_Db_Table_Row_Abstract) {
$manager = new Blog_Model_Category_Manager();
$category = $manager->getById($category);
}
//$separator = Categories_Model_Category::PATH_SEPARATOR;
$select->where('c.path LIKE ?', '%' . $category->alias . '%');
}
return $select;
}
