PHP SelectQuery类代码示例

 public static function search(TableCtl $controller, $term, $filter = false)
 {
     $object = call_user_func(array(get_class($controller), 'getObject'));
     if (!$object) {
         return false;
     }
     $terms = preg_split('/[ ,]/', $term);
     if (!count($terms)) {
         return false;
     }
     //Check for results containing the word
     $search = array();
     foreach ($terms as $oneTerm) {
         $search[] = '`word` LIKE CONCAT("%", ?, "%")';
     }
     //Check for results with the exact word
     $search[] = '`word` IN (' . implode(', ', array_fill(0, count($terms), '?')) . ')';
     $search = '(' . implode(') OR (', $search) . ')';
     $params = array_merge(array($object->getSource()), $terms, $terms);
     $query = new SelectQuery(get_called_class());
     $query->field('DISTINCT `' . $object->getMeta('table') . '`.*')->leftJoin(get_class($controller), '`' . $object->getMeta('table') . '`.`' . $object->getMeta('id_field') . '` = `table_id`')->filter('`table` = ?')->filter($search)->order('`count` DESC, `sequence`');
     if ($filter) {
         if (is_array($filter)) {
             foreach ($filter as $one_fil) {
                 $query->filter($one_fil);
             }
         } else {
             $query->filter($filter);
         }
     }
     $result = $query->fetchAll($params);
     return $result;
 }

 public function getConnection()
 {
     $q = new SelectQuery();
     $this->assertNull($q->getConnection());
     $q->setPeer(Job::getPeer());
     $this->assertClass($q->getConnection(), 'net.xp_framework.unittest.rdbms.mock.MockConnection');
 }

/**
 * Alter the term DB query.
 *
 * @param SelectQuery $query
 *   The query object ot alter.
 */
function hook_query_rules_autotag_terms_alter($query)
{
    // Join the original term query from rules_autotag with our field table to
    // exclude terms that should not be tagged.
    $query->leftJoin('field_data_field_dont_autotag', 'fd', "t.tid = fd.entity_id AND fd.entity_type = 'taxonomy_term'");
    $query->condition(db_or()->condition('fd.field_dont_autotag_value', NULL)->condition('fd.field_dont_autotag_value', '1', '<>'));
}

 public static function check()
 {
     if (!empty($_COOKIE['remembered'])) {
         $query = new SelectQuery('PersistUser');
         $persist = $query->filter('MD5(CONCAT(`id`, `user_id`, `random`)) = :hash')->fetchAssoc(array(':hash' => $_COOKIE['remembered']));
         if ($persist) {
             //Get User
             $User = self::getObject('BackendUser');
             if (!$User instanceof DBObject) {
                 return false;
             }
             $query = BackendUser::getQuery();
             $query->filter('`backend_users`.`id` = :id');
             $params = array(':id' => $persist['user_id']);
             $User->read(array('query' => $query, 'parameters' => $params, 'mode' => 'object'));
             if ($User->object) {
                 $_SESSION['BackendUser'] = $User->object;
                 //Remove, and reremember
                 if (self::remember($User->object)) {
                     $query = new DeleteQuery('PersistUser');
                     $query->filter('`id` = :id')->limit(1);
                     $query->execute(array(':id' => $persist['id']));
                 } else {
                     Backend::addError('Could not reremember');
                 }
                 return $User->object;
             } else {
                 //Backend::addError('Invalid remembered user');
             }
         }
     }
     return false;
 }

 /**
  * @return string
  */
 private function getToJoin()
 {
     if ($this->tojoin instanceof SelectQuery || $this->tojoin instanceof UnionQuery) {
         return " (" . $this->tojoin->get(false) . ")";
     } else {
         return " " . $this->tojoin;
     }
 }

 /**
  * @return SelectQuery
  **/
 protected function joinHelperTable(SelectQuery $query)
 {
     $uc = $this->container;
     if (!$query->hasJoinedTable($uc->getHelperTable())) {
         $query->join($uc->getHelperTable(), Expression::eq(new DBField($uc->getParentTableIdField(), $uc->getDao()->getTable()), new DBField($uc->getChildIdField(), $uc->getHelperTable())));
     }
     return $query->andWhere(Expression::eq(new DBField($uc->getParentIdField(), $uc->getHelperTable()), new DBValue($uc->getParentObject()->getId())));
 }

    public function action_create()
    {
        if (is_post()) {
            $parameters = get_previous_parameters();
            $object = new CommentObj();
            $object = $object->fromRequest();
            $object['foreign_id'] = empty($object['foreign_id']) ? reset($parameters) : $object['foreign_id'];
            $object['foreign_table'] = empty($object['foreign_table']) ? table_name(get_previous_area()) : $object['foreign_table'];
            //If we don't have a logged in user, create a dummy account
            if (!BackendUser::check()) {
                $query = new SelectQuery('BackendUser');
                $query->filter('`email` = :email');
                if ($old_user = Controller::getVar('user')) {
                    $existing_user = $query->fetchAssoc(array(':email' => $old_user['email']));
                }
                switch (true) {
                    case $existing_user && $existing_user['confirmed'] && $existing_user['active']:
                        //Attribute quote to user? Seems risque, actually, if I know a user's email address, I can just attribute to him. Auth first
                        Backend::addError('Comment not added. Please login first');
                        return false;
                        break;
                    case $existing_user && !$existing_user['confirmed'] && $existing_user['active']:
                        //Unregistered user commented before
                        $object['user_id'] = $existing_user['id'];
                        break;
                    default:
                    case !$existing_user:
                        $user_data = array('name' => $old_user['name'], 'surname' => '', 'email' => $old_user['email'], 'website' => $old_user['website'], 'username' => $old_user['email'], 'password' => get_random(), 'confirmed' => 0, 'active' => 1);
                        $user = self::getObject('BackendUser');
                        if ($user->create($user_data)) {
                            $object['user_id'] = $user->array['id'];
                            $url = SITE_LINK . '/?q=backend_user/confirm/' . $user->array['salt'];
                            $app_name = ConfigValue::get('Title');
                            $message = <<<END
Hi {$user->array['name']}!

Thank you for your comment on {$app_name}. An account has automatically been created for you. To activate it, please click on the following link:

{$url}

Please note that you don't need to do this for your comments to show, but this account will be deleted if it isn't confirmed in a weeks time.

Regards
END;
                            send_email($user->array['email'], 'Thank you for your comment.', $message);
                        } else {
                            Backend::addError('Could not create user to add Comment');
                            return false;
                        }
                        break;
                }
            }
            $object = array_filter($object, create_function('$var', 'return !is_null($var);'));
            Controller::setVar('obj', $object);
        }
        return parent::action_create();
    }

 public function action_filter($pageId = 1)
 {
     $query = new SelectQuery('BackendRequest');
     $query->setFields(array('user_id', 'ip', 'user_agent', 'mode', 'request', 'query', 'COUNT(id) AS `occured`', 'MAX(`added`) AS `last_occured`'));
     $query->setGroup(array('user_id', 'ip', 'user_agent', 'mode', 'request', 'query'));
     $params = $queryFilter = array();
     $parameters = Controller::getVar('params');
     $sort = Controller::getVar('sort');
     if (!empty($parameters['userId'])) {
         $queryFilter[] = 'user_id = :userId';
         $params[':userId'] = $parameters['userId'];
     }
     if (!empty($parameters['query'])) {
         $queryFilter[] = "query LIKE('%{$parameters['query']}%')";
     }
     if (!empty($parameters['ip'])) {
         $queryFilter[] = "ip LIKE('%{$parameters['ip']}%')";
     }
     if (!empty($parameters['user_agent'])) {
         $queryFilter[] = "user_agent LIKE('%{$parameters['user_agent']}%')";
     }
     $query->filter($queryFilter);
     $count = 10;
     if (!empty($sort['field'])) {
         $query->setOrder(array($sort['field'] . '  ' . $sort['order']));
     }
     if ($pageId == 1) {
         $start = 0;
     } elseif ($pageId == 0) {
         $start = false;
         $count = false;
     } else {
         $start = floor(($pageId - 1) * $count);
     }
     $pager = array();
     if ($start === 'all') {
         $limit = 'all';
     } else {
         if ($start || $count) {
             $limit = "{$start}, {$count}";
         } else {
             $limit = false;
         }
     }
     $query->limit($limit);
     $items = $query->fetchAll($params);
     $totalItems = $query->getCount($params);
     $pager = '';
     if ($start || $count) {
         $pager = array('currentPage' => $pageId, 'itemCount' => count($items), 'itemTotal' => $totalItems, 'totalPages' => round(($totalItems - 1) / $count, 0));
     }
     $retArray['pager'] = $pager;
     $retArray['data'] = $items;
     $retArray['params'] = $parameters;
     $retArray['sort'] = $sort;
     return $retArray;
 }

 public function action_check_defines()
 {
     if (!Component::isActive('BackendError')) {
         return false;
     }
     $query = new SelectQuery('BackendError');
     $query->distinct()->field('query')->filter("`string` LIKE 'Undefined index: %'")->filter("`file` LIKE '%\\\\Render.obj.php(%) : eval()\\'d code'")->filter("`query` LIKE 'a_p_i/define/%'");
     return $query->fetchAll(array(), array('column' => 0));
 }

 /** Create SELECT query from $table
  * @param string $table  db table name
  * @param integer $primaryKey  return one row by primary key
  * @return SelectQuery
  */
 public function from($table, $primaryKey = null)
 {
     $query = new SelectQuery($this, $table);
     if ($primaryKey) {
         $tableTable = $query->getFromTable();
         $tableAlias = $query->getFromAlias();
         $primaryKeyName = $this->structure->getPrimaryKey($tableTable);
         $query = $query->where("{$tableAlias}.{$primaryKeyName}", $primaryKey);
     }
     return $query;
 }

 public function process(\SelectQuery $query)
 {
     $result = $query->limit($this->options['limit'])->offset($this->options['page'] * $this->options['limit'])->fetchAll();
     $this->count = count($result);
     // Check to see if total is equal to our limit and check for the real total
     if ($this->options['page'] || $this->count == $this->options['limit']) {
         $this->total = (int) $query->limit(null)->offset(null)->select(null)->select('COUNT(*)')->fetchColumn();
     } else {
         $this->total = $this->count;
     }
     return $result;
 }

 public function action_display($id)
 {
     $query = new SelectQuery('ContentRevision');
     $query->filter('`content_id` = :id')->order('`added` DESC');
     $revisions = $query->fetchAll(array(':id' => $id));
     $content = new ContentObj($id);
     if ($content->object) {
         $content->object->revisions = $revisions;
     } else {
         $content = false;
     }
     return $content;
 }

 protected function cacheByQuery(SelectQuery $query, $object, $expires = Cache::EXPIRES_FOREVER)
 {
     $queryId = $query->getId();
     $semKey = $this->keyToInt($this->indexKey);
     $key = $this->makeQueryKey($query, self::SUFFIX_QUERY);
     $pool = SemaphorePool::me();
     if ($pool->get($semKey)) {
         $this->syncMap($key);
         Cache::me()->mark($this->className)->add($key, $object, $expires);
         $pool->free($semKey);
     }
     return $object;
 }

 /**
  * Initialize this object for a set of authentication tokens. Set the 
  * password to the encrypted version.
  * 
  * @param mixed $tokens
  * @return void
  * @access public
  * @since 3/1/05
  */
 function initializeForTokens($tokens)
 {
     ArgumentValidator::validate($tokens, ArrayValidatorRule::getRule());
     ArgumentValidator::validate($tokens['username'], StringValidatorRule::getRule());
     ArgumentValidator::validate($tokens['password'], StringValidatorRule::getRule());
     $this->_tokens = $tokens;
     $this->_identifier = $tokens['username'];
     // set the password to the encrypted version.
     $dbc = Services::getService("DatabaseManager");
     $dbId = $this->_configuration->getProperty('database_id');
     $passwordQuery = new SelectQuery();
     $passwordQuery->addColumn("SHA1('" . addslashes($tokens['password']) . "')", "encryptedPassword");
     $passwordResult = $dbc->query($passwordQuery, $dbId);
     $this->_tokens['password'] = $passwordResult->field("encryptedPassword");
     $passwordResult->free();
 }

 /**
  * Answer an array of all of the themes known to this source
  * 
  * @return array of Harmoni_Gui2_ThemeInterface
  * @access public
  * @since 5/6/08
  */
 public function getThemes()
 {
     $themes = array();
     $query = new SelectQuery();
     $query->addTable('segue_site_theme');
     $query->addColumn('id');
     $query->addWhereEqual('fk_site', $this->getSiteId());
     $dbMgr = Services::getService("DatabaseManager");
     $result = $dbMgr->query($query, $this->databaseIndex);
     while ($result->hasNext()) {
         $row = $result->next();
         $themes[] = new Segue_Gui2_SiteTheme($this->databaseIndex, $row['id']);
     }
     $result->free();
     return $themes;
 }