PHP Zend_Db_Select::from方法代码示例

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

 /**
  * init the select
  */
 private function _initSelector()
 {
     if (!$this->_tableName) {
         throw new Exception("if you wanna use model, please specify the table name first");
     }
     $this->_selector->reset();
     $this->_selector->from($this->_tableName);
 }

 /**
  * Retrieve cms page collection array
  *
  * @param unknown_type $storeId
  * @return Zend_Db_Statement_Interface
  */
 public function getCollection($storeId, $includeEEHierarchy = false)
 {
     $this->_select = $this->_getWriteAdapter()->select();
     $this->_select->from(array('main_table' => $this->getMainTable()), array($this->getIdFieldName(), 'DATE(main_table.update_time) as updated_at'))->join(array('store_table' => $this->getTable('cms/page_store')), 'main_table.page_id=store_table.page_id', array())->where('main_table.is_active=1')->where('store_table.store_id IN(?)', array(0, $storeId));
     if ($includeEEHierarchy) {
         $this->_addHierarchy();
     } else {
         $this->_select->columns('main_table.identifier as url');
     }
     //        die((string)$this->_select);
     $query = $this->_getWriteAdapter()->query($this->_select);
     return $query;
 }

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

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

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

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

 /**
  * Saving information about customer
  *
  * @param   Mage_Log_Model_Visitor $visitor
  *
  * @return  Mage_Log_Model_Resource_Visitor
  */
 protected function _saveCustomerInfo($visitor)
 {
     $adapter = $this->_getWriteAdapter();
     if ($visitor->getDoCustomerLogout() && ($logId = $visitor->getCustomerLogId())) {
         $resource = Mage::getSingleton('core/resource');
         $connection = $resource->getConnection('core_read');
         $select = new Zend_Db_Select($connection);
         $select->from($resource->getTableName('log/customer'));
         $select->reset(Zend_Db_Select::COLUMNS);
         $select->columns('login_at');
         $select->where('log_id = ?', $logId);
         $loginAt = $connection->fetchOne($select);
         if (!$loginAt) {
             return parent::_saveCustomerInfo($visitor);
         }
         $data = new Varien_Object(array('login_at' => $loginAt, 'logout_at' => Mage::getSingleton('core/date')->gmtDate(), 'store_id' => (int) Mage::app()->getStore()->getId()));
         $bind = $this->_prepareDataForTable($data, $this->getTable('log/customer'));
         $condition = array('log_id = ?' => (int) $logId);
         $adapter->update($this->getTable('log/customer'), $bind, $condition);
         $visitor->setDoCustomerLogout(false);
         $visitor->setCustomerId(null);
         $visitor->setCustomerLogId(null);
     } else {
         return parent::_saveCustomerInfo($visitor);
     }
     return $this;
 }

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

 /**
  * 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);
 }

 /**
  * Returns the select object to be used for validation.
  * @return Zend_Db_Select $select
  */
 public function getSelect()
 {
     if (null === $this->_select) {
         $db = $this->getAdapter();
         // Zend_Debug::dump($this->_excludeId ); // die(0);
         $select = new Zend_Db_Select($db);
         $select->from($this->_table, array($this->_field), $this->_schema);
         if ($db->supportsParameters('named')) {
             $select->where($db->quoteIdentifier($this->_field, true) . ' = :value');
             // named
         } else {
             $select->where($db->quoteIdentifier($this->_field, true) . ' = ?');
             // positional
         }
         $select->limit(1);
         if (!empty($this->_meetingId)) {
             $select->where($db->quoteInto('`meeting_id` = ?', $this->_meetingId));
         }
         if (!empty($this->_excludeId)) {
             $select->where($db->quoteInto('`id` != ?', $this->_excludeId));
         }
         $this->_select = $select;
     }
     return $this->_select;
 }

 /**
  * Run query and returns matches, or null if no matches are found.
  *
  * @param  String $value
  * @return Array when matches are found.
  */
 protected function _query($value)
 {
     /**
      * Check for an adapter being defined. if not, fetch the default adapter.
      */
     if ($this->_adapter === null) {
         $this->_adapter = Zend_Db_Table_Abstract::getDefaultAdapter();
     }
     /**
      * Build select object
      */
     $select = new Zend_Db_Select($this->_adapter);
     $select->from($this->_table, array($this->_field), $this->_schema)->where($this->_adapter->quoteIdentifier($this->_field) . ' = ?', $value);
     if ($this->_exclude !== null) {
         if (is_array($this->_exclude)) {
             $select->where($this->_adapter->quoteIdentifier($this->_exclude['field']) . ' != ?', $this->_exclude['value']);
         } else {
             $select->where($this->_exclude);
         }
     }
     $select->limit(1);
     /**
      * Run query
      */
     $result = $this->_adapter->fetchRow($select, array(), Zend_Db::FETCH_ASSOC);
     return $result;
 }

 /**
  * 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 onStatistics($event)
 {
     $table = Engine_Api::_()->getItemTable('ynfeedback_idea');
     $select = new Zend_Db_Select($table->getAdapter());
     $select->from($table->info('name'), 'COUNT(*) AS count');
     $event->addResponse($select->query()->fetchColumn(0), 'feedback');
 }