PHP Zend_Db_Select类代码示例

 function onInstall()
 {
     // member homepage
     $db = $this->getDb();
     $select = new Zend_Db_Select($db);
     $select->from('engine4_core_pages')->where('name = ?', 'user_index_home')->limit(1);
     $page_id = $select->query()->fetchObject()->page_id;
     // Check if it's already been placed
     $select = new Zend_Db_Select($db);
     $select->from('engine4_core_content')->where('page_id = ?', $page_id)->where('type = ?', 'widget')->where('name = ?', 'social-connect.call-popup-invite');
     $info = $select->query()->fetch();
     if (empty($info)) {
         // container_id (will always be there)
         $select = new Zend_Db_Select($db);
         $select->from('engine4_core_content')->where('page_id = ?', $page_id)->where('type = ?', 'container')->limit(1);
         $container_id = $select->query()->fetchObject()->content_id;
         // middle_id (will always be there)
         $select = new Zend_Db_Select($db);
         $select->from('engine4_core_content')->where('parent_content_id = ?', $container_id)->where('type = ?', 'container')->where('name = ?', 'middle')->limit(1);
         $middle_id = $select->query()->fetchObject()->content_id;
         // tab on profile
         $db->insert('engine4_core_content', array('page_id' => $page_id, 'type' => 'widget', 'name' => 'social-connect.call-popup-invite', 'parent_content_id' => $middle_id, 'params' => '', 'order' => 999));
     }
     parent::onInstall();
 }

 /**
  * Lists all tests which are available in system
  * @return dataset
  */
 public function getAll()
 {
     $obj = new Zend_Db_Select(Zend_Db_Table::getDefaultAdapter());
     $select = $obj->from('tests as t', array('id', 'name'))->order('t.name');
     $result = $select->query()->fetchAll();
     return $result;
 }

 public function indexAction()
 {
     // Users
     $userTable = Engine_Api::_()->getItemTable('user');
     $select = new Zend_Db_Select($userTable->getAdapter());
     $select->from($userTable->info('name'), 'COUNT(user_id) as count');
     $data = $select->query()->fetch();
     $this->view->userCount = (int) $data['count'];
     // Reports
     $reportTable = Engine_Api::_()->getDbtable('reports', 'core');
     $select = new Zend_Db_Select($reportTable->getAdapter());
     $select->from($reportTable->info('name'), 'COUNT(report_id) as count')->where('`read` = ?', 0);
     $data = $select->query()->fetch();
     $this->view->reportCount = (int) $data['count'];
     // Plugins
     $moduleTable = Engine_Api::_()->getDbtable('modules', 'core');
     $select = new Zend_Db_Select($moduleTable->getAdapter());
     $select->from($moduleTable->info('name'), 'COUNT(TRUE) as count')->where('type = ?', 'extra');
     $data = $select->query()->fetch();
     $this->view->pluginCount = (int) $data['count'];
     // Notifications
     // Hook-based
     $event = Engine_Hooks_Dispatcher::_()->callEvent('getAdminNotifications');
     $this->view->notifications = $event->getResponses();
     // Database-based
     $select = Engine_Api::_()->getDbtable('log', 'core')->select()->where('domain = ?', 'admin')->order('timestamp DESC');
     $this->view->paginator = $paginator = Zend_Paginator::factory($select);
     $paginator->setItemCountPerPage(4);
 }

 public function init()
 {
     // Form
     $this->setMethod('GET')->setAction(Zend_Controller_Front::getInstance()->getRouter()->assemble(array()))->addAttribs(array('id' => 'filter_form', 'class' => 'global_form_box'));
     $this->clearDecorators()->addDecorator('FormElements')->addDecorator('Form')->addDecorator('HtmlTag', array('tag' => 'div', 'class' => 'search'))->addDecorator('HtmlTag2', array('tag' => 'div', 'class' => 'clear'));
     // Element: moduleName
     $jobTypesTable = Engine_Api::_()->getDbtable('jobTypes', 'core');
     $modulesTable = Engine_Api::_()->getDbtable('modules', 'core');
     $select = new Zend_Db_Select($jobTypesTable->getAdapter());
     $modules = $select->distinct()->from($jobTypesTable->info('name'), 'module')->joinLeft($modulesTable->info('name'), 'module=name', array('title'))->where($modulesTable->info('name') . '.enabled = ?', 1)->order('title')->query()->fetchAll();
     $multiOptions = array('' => '');
     foreach ($modules as $module) {
         if (!empty($module['title'])) {
             $multiOptions[$module['module']] = $module['title'];
         }
     }
     $this->addElement('Select', 'moduleName', array('label' => 'Module', 'multiOptions' => $multiOptions, 'decorators' => array('ViewHelper', array('Label', array('tag' => null, 'placement' => 'PREPEND')), array('HtmlTag', array('tag' => 'div')))));
     // Element: jobType
     $jobTypes = $jobTypesTable->select()->from($jobTypesTable, array('jobtype_id', 'title', 'type'))->order('title')->query()->fetchAll();
     $multiOptions = array('' => '');
     foreach ($jobTypes as $jobType) {
         $multiOptions[$jobType['jobtype_id']] = $jobType['title'];
     }
     $this->addElement('Select', 'jobtype_id', array('label' => 'Type', 'multiOptions' => $multiOptions, 'decorators' => array('ViewHelper', array('Label', array('tag' => null, 'placement' => 'PREPEND')), array('HtmlTag', array('tag' => 'div')))));
     // Element: state
     $this->addElement('Select', 'state', array('label' => 'State', 'multiOptions' => array('' => '', 'pending' => 'Pending', 'active' => 'Active', 'sleeping' => 'Sleeping', 'failed' => 'Failed', 'cancelled' => 'Cancelled', 'completed' => 'Completed', 'timeout' => 'Timed Out'), 'decorators' => array('ViewHelper', array('Label', array('tag' => null, 'placement' => 'PREPEND')), array('HtmlTag', array('tag' => 'div')))));
     // Element: order
     $this->addElement('Select', 'order', array('label' => 'Order', 'multiOptions' => array('job_id' => 'ID', 'jobtype_id' => 'Type', 'state' => 'State', 'progress' => 'Progress', 'creation_date' => 'Queued Date', 'started_date' => 'Started Date', 'completion_date' => 'Completed Date', 'priority' => 'Priority'), 'decorators' => array('ViewHelper', array('Label', array('tag' => null, 'placement' => 'PREPEND')), array('HtmlTag', array('tag' => 'div')))));
     // Element: direction
     $this->addElement('Select', 'direction', array('label' => 'Direction', 'multiOptions' => array('ASC' => 'A-Z', 'DESC' => 'Z-A'), 'decorators' => array('ViewHelper', array('Label', array('tag' => null, 'placement' => 'PREPEND')), array('HtmlTag', array('tag' => 'div')))));
     // Element: submit
     $this->addElement('Button', 'execute', array('label' => 'Filter', 'ignore' => true, 'type' => 'submit', 'decorators' => array('ViewHelper', array('HtmlTag', array('tag' => 'div', 'class' => 'buttons')), array('HtmlTag2', array('tag' => 'div')))));
 }

 function getById($jobid)
 {
     // do Bacula ACLs
     Zend_Loader::loadClass('Job');
     $table = new Job();
     if (!$table->isJobIdExists($jobid)) {
         return FALSE;
     }
     $select = new Zend_Db_Select($this->db);
     switch ($this->db_adapter) {
         case 'PDO_SQLITE':
             // bug http://framework.zend.com/issues/browse/ZF-884
             $select->distinct();
             $select->from(array('l' => 'Log'), array('logid' => 'LogId', 'jobid' => 'JobId', 'LogTime' => 'Time', 'logtext' => 'LogText'));
             $select->where("JobId = ?", $jobid);
             $select->order(array('LogId', 'LogTime'));
             break;
         default:
             // mysql, postgresql
             $select->distinct();
             $select->from(array('l' => 'Log'), array('LogId', 'JobId', 'LogTime' => 'Time', 'LogText'));
             $select->where("JobId = ?", $jobid);
             $select->order(array('LogId', 'LogTime'));
     }
     //$sql = $select->__toString(); echo "<pre>$sql</pre>"; exit; // for !!!debug!!!
     $stmt = $select->query();
     return $stmt->fetchAll();
 }

 /**
  * Replaces calc_invoice_paid()
  * 
  * @param int $invoice
  */
 public function getPaidAmountForInvoice($invoice)
 {
     $select = new Zend_Db_Select($this->getAdapter());
     $select->from($this->_name, array('amount' => new Zend_Db_Expr("COALESCE(SUM(ac_amount), 0)")));
     $select->where('ac_inv_id=?', $invoice);
     return $this->getAdapter()->fetchOne($select);
 }

 /**
  * appends sql to given select statement
  *
  * @param Zend_Db_Select                $_select
  * @param Tinebase_Backend_Sql_Abstract $_backend
  */
 public function appendFilterSql($_select, $_backend)
 {
     $action = $this->_opSqlMap[$this->_operator];
     if (empty($this->_value) && $this->_value != '0') {
         // prevent sql error
         if ($this->_operator == 'in' || $this->_operator == 'equals') {
             if (Tinebase_Core::isLogLevel(Zend_Log::DEBUG)) {
                 Tinebase_Core::getLogger()->debug(__METHOD__ . '::' . __LINE__ . ' Empty value with "in" operator (model: ' . (isset($this->_options['modelName']) ? $this->_options['modelName'] : 'unknown / no modelName defined in filter options') . ')');
             }
             $_select->where('1=0');
         }
     } else {
         if ($this->_operator == 'equals' && is_array($this->_value)) {
             if (Tinebase_Core::isLogLevel(Zend_Log::NOTICE)) {
                 Tinebase_Core::getLogger()->notice(__METHOD__ . '::' . __LINE__ . ' Unexpected array value with "equals" operator (model: ' . (isset($this->_options['modelName']) ? $this->_options['modelName'] : 'unknown / no modelName defined in filter options') . ')');
             }
             $_select->where('1=0');
         } else {
             $type = $this->_getFieldType($_backend);
             $this->_enforceValueType($type);
             $field = $this->_getQuotedFieldName($_backend);
             // finally append query to select object
             $_select->where($field . $action['sqlop'], $this->_value, $type);
         }
     }
 }

 /**
  * A select used by subclasses to add fields to the select.
  *
  * @param string $login_name
  * @param int $organization
  * @return \Zend_Db_Select
  */
 protected function getUserSelect($login_name, $organization)
 {
     // 'user_group'       => 'gsf_id_primary_group', 'user_logout'      => 'gsf_logout_on_survey',
     $select = new \Zend_Db_Select($this->db);
     $select->from('gems__user_logins', array('user_login_id' => 'gul_id_user', 'user_active' => 'gul_can_login'))->join('gems__respondent2org', 'gul_login = gr2o_patient_nr AND gul_id_organization = gr2o_id_organization', array('user_login' => 'gr2o_patient_nr', 'user_base_org_id' => 'gr2o_id_organization'))->join('gems__respondents', 'gr2o_id_user = grs_id_user', array('user_id' => 'grs_id_user', 'user_email' => 'grs_email', 'user_first_name' => 'grs_first_name', 'user_surname_prefix' => 'grs_surname_prefix', 'user_last_name' => 'grs_last_name', 'user_gender' => 'grs_gender', 'user_locale' => 'grs_iso_lang', 'user_birthday' => 'grs_birthday'))->join('gems__organizations', 'gr2o_id_organization = gor_id_organization', array('user_group' => 'gor_respondent_group'))->join('gems__groups', 'gor_respondent_group = ggp_id_group', array('user_role' => 'ggp_role', 'user_allowed_ip_ranges' => 'ggp_allowed_ip_ranges'))->joinLeft('gems__user_passwords', 'gul_id_user = gup_id_user', array('user_password_reset' => 'gup_reset_required', 'user_resetkey_valid' => new \Zend_Db_Expr('CASE WHEN DATE_ADD(gup_reset_requested, INTERVAL ' . $this->hoursResetKeyIsValid . ' HOUR) >= CURRENT_TIMESTAMP THEN 1 ELSE 0 END'), 'user_password_last_changed' => 'gup_last_pwd_change'))->joinLeft('gems__reception_codes', 'gr2o_reception_code = grc_id_reception_code', array())->where('ggp_group_active = 1')->where('grc_success = 1')->where('gul_can_login = 1')->where('gul_login = ?')->where('gul_id_organization = ?')->limit(1);
     return $select;
 }

 /**
  * appends sql to given select statement
  *
  * @param  Zend_Db_Select                    $_select
  * @param  Tinebase_Backend_Sql_Abstract     $_backend
  */
 public function appendFilterSql($_select, $_backend)
 {
     $correlationName = Tinebase_Record_Abstract::generateUID(30);
     $db = $_backend->getAdapter();
     $_select->joinLeft(array($correlationName => $db->table_prefix . 'adb_list_m_role'), $db->quoteIdentifier($correlationName . '.contact_id') . ' = ' . $db->quoteIdentifier('addressbook.id'), array());
     $_select->where($db->quoteIdentifier($correlationName . '.list_role_id') . ' IN (?)', (array) $this->_value);
 }

 /**
  * appends sql to given select statement
  *
  * @param Zend_Db_Select                $_select
  * @param Tinebase_Backend_Sql_Abstract $_backend
  */
 public function appendFilterSql($_select, $_backend)
 {
     $db = $_backend->getAdapter();
     // prepare value
     $value = $this->_value ? 1 : 0;
     if ($value) {
         // nothing to do -> show all contacts!
         if (Tinebase_Core::isLogLevel(Zend_Log::DEBUG)) {
             Tinebase_Core::getLogger()->debug(__METHOD__ . '::' . __LINE__ . ' Query all account contacts.');
         }
     } else {
         if (Tinebase_Core::isLogLevel(Zend_Log::DEBUG)) {
             Tinebase_Core::getLogger()->debug(__METHOD__ . '::' . __LINE__ . ' Only query visible and enabled account contacts.');
         }
         if (Tinebase_Core::getUser() instanceof Tinebase_Model_FullUser) {
             $where = '/* is no user */ ' . Tinebase_Backend_Sql_Command::getIfIsNull($db, $db->quoteIdentifier('accounts.id'), 'true', 'false') . ' OR /* is user */ (' . Tinebase_Backend_Sql_Command::getIfIsNull($db, $db->quoteIdentifier('accounts.id'), 'false', 'true') . ' AND ' . $db->quoteInto($db->quoteIdentifier('accounts.status') . ' = ?', 'enabled') . " AND " . '(' . $db->quoteInto($db->quoteIdentifier('accounts.visibility') . ' = ?', 'displayed') . ' OR ' . $db->quoteInto($db->quoteIdentifier('accounts.id') . ' = ?', Tinebase_Core::getUser()->getId()) . ')' . ")";
         } else {
             $where = '/* is no user */ ' . Tinebase_Backend_Sql_Command::getIfIsNull($db, $db->quoteIdentifier('accounts.id'), 'true', 'false') . ' OR /* is user */ (' . Tinebase_Backend_Sql_Command::getIfIsNull($db, $db->quoteIdentifier('accounts.id'), 'false', 'true') . ' AND ' . $db->quoteInto($db->quoteIdentifier('accounts.status') . ' = ?', 'enabled') . " AND " . $db->quoteInto($db->quoteIdentifier('accounts.visibility') . ' = ?', 'displayed') . ")";
         }
         $_select->where($where);
         $select = $_select instanceof Zend_Db_Select ? $_select : $_select->getSelect();
         $select = Tinebase_Backend_Sql_Abstract::traitGroup($db, $_backend->getTablePrefix(), $select);
         $_select instanceof Zend_Db_Select ? $_select = $select : $_select->setSelect($select);
         if (Tinebase_Core::isLogLevel(Zend_Log::DEBUG)) {
             Tinebase_Core::getLogger()->debug(__METHOD__ . '::' . __LINE__ . ' contacts query ' . $_select->assemble());
         }
     }
 }

 protected function _addContentEventProfile()
 {
     $db = $this->getDb();
     $select = new Zend_Db_Select($db);
     // Check if it's already been placed
     $select = new Zend_Db_Select($db);
     $hasWidget = $select->from('engine4_core_pages', new Zend_Db_Expr('TRUE'))->where('name = ?', 'event_profile_index')->limit(1)->query()->fetchColumn();
     // Add it
     if (empty($hasWidget)) {
         $db->insert('engine4_core_pages', array('name' => 'event_profile_index', 'displayname' => 'Event Profile', 'title' => 'Event Profile', 'description' => 'This is the profile for an event.', 'custom' => 0));
         $page_id = $db->lastInsertId('engine4_core_pages');
         // containers
         $db->insert('engine4_core_content', array('page_id' => $page_id, 'type' => 'container', 'name' => 'main', 'parent_content_id' => null, 'order' => 1, 'params' => ''));
         $container_id = $db->lastInsertId('engine4_core_content');
         $db->insert('engine4_core_content', array('page_id' => $page_id, 'type' => 'container', 'name' => 'middle', 'parent_content_id' => $container_id, 'order' => 3, 'params' => ''));
         $middle_id = $db->lastInsertId('engine4_core_content');
         $db->insert('engine4_core_content', array('page_id' => $page_id, 'type' => 'container', 'name' => 'left', 'parent_content_id' => $container_id, 'order' => 1, 'params' => ''));
         $left_id = $db->lastInsertId('engine4_core_content');
         // middle column
         $db->insert('engine4_core_content', array('page_id' => $page_id, 'type' => 'widget', 'name' => 'core.container-tabs', 'parent_content_id' => $middle_id, 'order' => 2, 'params' => '{"max":"6"}'));
         $tab_id = $db->lastInsertId('engine4_core_content');
         $db->insert('engine4_core_content', array('page_id' => $page_id, 'type' => 'widget', 'name' => 'event.profile-status', 'parent_content_id' => $middle_id, 'order' => 1, 'params' => ''));
         // left column
         $db->insert('engine4_core_content', array('page_id' => $page_id, 'type' => 'widget', 'name' => 'event.profile-photo', 'parent_content_id' => $left_id, 'order' => 1, 'params' => ''));
         $db->insert('engine4_core_content', array('page_id' => $page_id, 'type' => 'widget', 'name' => 'event.profile-options', 'parent_content_id' => $left_id, 'order' => 2, 'params' => ''));
         $db->insert('engine4_core_content', array('page_id' => $page_id, 'type' => 'widget', 'name' => 'event.profile-info', 'parent_content_id' => $left_id, 'order' => 3, 'params' => ''));
         $db->insert('engine4_core_content', array('page_id' => $page_id, 'type' => 'widget', 'name' => 'event.profile-rsvp', 'parent_content_id' => $left_id, 'order' => 4, 'params' => ''));
         // tabs
         $db->insert('engine4_core_content', array('page_id' => $page_id, 'type' => 'widget', 'name' => 'activity.feed', 'parent_content_id' => $tab_id, 'order' => 1, 'params' => '{"title":"Updates"}'));
         $db->insert('engine4_core_content', array('page_id' => $page_id, 'type' => 'widget', 'name' => 'event.profile-members', 'parent_content_id' => $tab_id, 'order' => 2, 'params' => '{"title":"Guests","titleCount":true}'));
         $db->insert('engine4_core_content', array('page_id' => $page_id, 'type' => 'widget', 'name' => 'event.profile-photos', 'parent_content_id' => $tab_id, 'order' => 3, 'params' => '{"title":"Photos","titleCount":true}'));
         $db->insert('engine4_core_content', array('page_id' => $page_id, 'type' => 'widget', 'name' => 'event.profile-discussions', 'parent_content_id' => $tab_id, 'order' => 4, 'params' => '{"title":"Discussions","titleCount":true}'));
         $db->insert('engine4_core_content', array('page_id' => $page_id, 'type' => 'widget', 'name' => 'core.profile-links', 'parent_content_id' => $tab_id, 'order' => 5, 'params' => '{"title":"Links","titleCount":true}'));
     }
 }

 public function getTotal($type, $start = null, $end = null)
 {
     $select = new Zend_Db_Select($this->getAdapter());
     $select->from($this->info('name'), 'SUM(value) as sum')->where('type = ?', $type);
     // Can pass "today" into start
     switch ($start) {
         case 'day':
             $start = mktime(0, 0, 0, gmdate("n"), gmdate("j"), gmdate("Y"));
             $end = mktime(0, 0, 0, gmdate("n"), gmdate("j") + 1, gmdate("Y"));
             break;
         case 'week':
             $start = mktime(0, 0, 0, gmdate("n"), gmdate("j") - gmdate('N') + 1, gmdate("Y"));
             $end = mktime(0, 0, 0, gmdate("n"), gmdate("j") - gmdate('N') + 1 + 7, gmdate("Y"));
             break;
         case 'month':
             $start = mktime(0, 0, 0, gmdate("n"), gmdate("j"), gmdate("Y"));
             $end = mktime(0, 0, 0, gmdate("n") + 1, gmdate("j"), gmdate("Y"));
             break;
         case 'year':
             $start = mktime(0, 0, 0, gmdate("n"), gmdate("j"), gmdate("Y"));
             $end = mktime(0, 0, 0, gmdate("n"), gmdate("j"), gmdate("Y") + 1);
             break;
     }
     if (null !== $start) {
         $select->where('date >= ?', gmdate('Y-m-d', $start));
     }
     if (null !== $end) {
         $select->where('date < ?', gmdate('Y-m-d', $end));
     }
     $data = $select->query()->fetch();
     if (!isset($data['sum'])) {
         return 0;
     }
     return $data['sum'];
 }

 public function joinComments()
 {
     $select = new Zend_Db_Select($connection = Mage::getSingleton('core/resource')->getConnection('read'));
     $select->from(Mage::getSingleton('core/resource')->getTableName('mdlblog/comment'), array('post_id', 'comment_count' => new Zend_Db_Expr('COUNT(IF(status = 2, post_id, NULL))')))->group('post_id');
     $this->getSelect()->joinLeft(array('comments_select' => $select), 'main_table.post_id = comments_select.post_id', 'comment_count');
     return $this;
 }

 public function onStatistics($event)
 {
     $table = Engine_Api::_()->getDbTable('photos', 'album');
     $select = new Zend_Db_Select($table->getAdapter());
     $select->from($table->info('name'), 'COUNT(*) AS count');
     $event->addResponse($select->query()->fetchColumn(0), 'photo');
 }

 public static function _setSelectRules(Zend_Db_Select $select)
 {
     $select->where('accountStatus != ?', self::STATUS_DELETED);
     if (!is_null(self::getMyType())) {
         $select->where('type = ?', static::getMyType());
     }
 }