PHP Connection::query方法代码示例

 /**
  * {@inheritdoc}
  */
 public function read($sid)
 {
     // @todo Remove global in https://www.drupal.org/node/2228393
     global $_session_user;
     // Handle the case of first time visitors and clients that don't store
     // cookies (eg. web crawlers).
     $cookies = $this->requestStack->getCurrentRequest()->cookies;
     if (empty($sid) || !$cookies->has($this->getName())) {
         $_session_user = new UserSession();
         return '';
     }
     $values = $this->connection->query("SELECT u.*, s.* FROM {users_field_data} u INNER JOIN {sessions} s ON u.uid = s.uid WHERE u.default_langcode = 1 AND s.sid = :sid", array(':sid' => Crypt::hashBase64($sid)))->fetchAssoc();
     // We found the client's session record and they are an authenticated,
     // active user.
     if ($values && $values['uid'] > 0 && $values['status'] == 1) {
         // Add roles element to $user.
         $rids = $this->connection->query("SELECT ur.roles_target_id as rid FROM {user__roles} ur WHERE ur.entity_id = :uid", array(':uid' => $values['uid']))->fetchCol();
         $values['roles'] = array_merge(array(AccountInterface::AUTHENTICATED_ROLE), $rids);
         $_session_user = new UserSession($values);
     } elseif ($values) {
         // The user is anonymous or blocked. Only preserve two fields from the
         // {sessions} table.
         $_session_user = new UserSession(array('session' => $values['session'], 'access' => $values['access']));
     } else {
         // The session has expired.
         $_session_user = new UserSession();
     }
     return $_session_user->session;
 }

 /**
  * {@inheritdoc}
  */
 public function save($source, $alias, $langcode = LanguageInterface::LANGCODE_NOT_SPECIFIED, $pid = NULL)
 {
     if ($source[0] !== '/') {
         throw new \InvalidArgumentException(sprintf('Source path %s has to start with a slash.', $source));
     }
     if ($alias[0] !== '/') {
         throw new \InvalidArgumentException(sprintf('Alias path %s has to start with a slash.', $alias));
     }
     $fields = array('source' => $source, 'alias' => $alias, 'langcode' => $langcode);
     // Insert or update the alias.
     if (empty($pid)) {
         $query = $this->connection->insert('url_alias')->fields($fields);
         $pid = $query->execute();
         $fields['pid'] = $pid;
         $operation = 'insert';
     } else {
         // Fetch the current values so that an update hook can identify what
         // exactly changed.
         $original = $this->connection->query('SELECT source, alias, langcode FROM {url_alias} WHERE pid = :pid', array(':pid' => $pid))->fetchAssoc();
         $fields['pid'] = $pid;
         $query = $this->connection->update('url_alias')->fields($fields)->condition('pid', $pid);
         $pid = $query->execute();
         $fields['original'] = $original;
         $operation = 'update';
     }
     if ($pid) {
         // @todo Switch to using an event for this instead of a hook.
         $this->moduleHandler->invokeAll('path_' . $operation, array($fields));
         Cache::invalidateTags(['route_match']);
         return $fields;
     }
     return FALSE;
 }

 /**
  * Gets a redirect for given path, query and language.
  *
  * @param string $source_path
  *   The redirect source path.
  * @param array $query
  *   The redirect source path query.
  * @param $language
  *   The language for which is the redirect.
  *
  * @return \Drupal\redirect\Entity\Redirect
  *   The matched redirect entity.
  *
  * @throws \Drupal\redirect\Exception\RedirectLoopException
  */
 public function findMatchingRedirect($source_path, array $query = [], $language = Language::LANGCODE_NOT_SPECIFIED)
 {
     $hashes = [Redirect::generateHash($source_path, $query, $language)];
     if ($language != Language::LANGCODE_NOT_SPECIFIED) {
         $hashes[] = Redirect::generateHash($source_path, $query, Language::LANGCODE_NOT_SPECIFIED);
     }
     // Add a hash without the query string if using passthrough querystrings.
     if (!empty($query) && $this->config->get('passthrough_querystring')) {
         $hashes[] = Redirect::generateHash($source_path, [], $language);
         if ($language != Language::LANGCODE_NOT_SPECIFIED) {
             $hashes[] = Redirect::generateHash($source_path, [], Language::LANGCODE_NOT_SPECIFIED);
         }
     }
     // Load redirects by hash. A direct query is used to improve performance.
     $rid = $this->connection->query('SELECT rid FROM {redirect} WHERE hash IN (:hashes[]) ORDER BY LENGTH(redirect_source__query) DESC', [':hashes[]' => $hashes])->fetchField();
     if (!empty($rid)) {
         // Check if this is a loop.
         if (in_array($rid, $this->foundRedirects)) {
             throw new RedirectLoopException('/' . $source_path, $rid);
         }
         $this->foundRedirects[] = $rid;
         $redirect = $this->load($rid);
         // Find chained redirects.
         if ($recursive = $this->findByRedirect($redirect, $language)) {
             // Reset found redirects.
             $this->foundRedirects = [];
             return $recursive;
         }
         return $redirect;
     }
     return NULL;
 }

 /**
  * {@inheritdoc}
  */
 public function getMultiple(&$cids, $allow_invalid = FALSE)
 {
     $cid_mapping = array();
     foreach ($cids as $cid) {
         $cid_mapping[$this->normalizeCid($cid)] = $cid;
     }
     // When serving cached pages, the overhead of using ::select() was found
     // to add around 30% overhead to the request. Since $this->bin is a
     // variable, this means the call to ::query() here uses a concatenated
     // string. This is highly discouraged under any other circumstances, and
     // is used here only due to the performance overhead we would incur
     // otherwise. When serving an uncached page, the overhead of using
     // ::select() is a much smaller proportion of the request.
     $result = array();
     try {
         $result = $this->connection->query('SELECT cid, data, created, expire, serialized, tags, checksum FROM {' . $this->connection->escapeTable($this->bin) . '} WHERE cid IN ( :cids[] ) ORDER BY cid', array(':cids[]' => array_keys($cid_mapping)));
     } catch (\Exception $e) {
         // Nothing to do.
     }
     $cache = array();
     foreach ($result as $item) {
         // Map the cache ID back to the original.
         $item->cid = $cid_mapping[$item->cid];
         $item = $this->prepareItem($item, $allow_invalid);
         if ($item) {
             $cache[$item->cid] = $item;
         }
     }
     $cids = array_diff($cids, array_keys($cache));
     return $cache;
 }

 public function preRender(&$values)
 {
     $uids = array();
     $this->items = array();
     foreach ($values as $result) {
         $uids[] = $this->getValue($result);
     }
     if ($uids) {
         $roles = user_roles();
         $result = $this->database->query('SELECT u.entity_id as uid, u.roles_target_id as rid FROM {user__roles} u WHERE u.entity_id IN ( :uids[] ) AND u.roles_target_id IN ( :rids[] )', array(':uids[]' => $uids, ':rids[]' => array_keys($roles)));
         foreach ($result as $role) {
             $this->items[$role->uid][$role->rid]['role'] = $roles[$role->rid]->label();
             $this->items[$role->uid][$role->rid]['rid'] = $role->rid;
         }
         // Sort the roles for each user by role weight.
         $ordered_roles = array_flip(array_keys($roles));
         foreach ($this->items as &$user_roles) {
             // Create an array of rids that the user has in the role weight order.
             $sorted_keys = array_intersect_key($ordered_roles, $user_roles);
             // Merge with the unsorted array of role information which has the
             // effect of sorting it.
             $user_roles = array_merge($sorted_keys, $user_roles);
         }
     }
 }

 /**
  * {@inheritdoc}
  */
 public function load($id)
 {
     $batch = $this->connection->query("SELECT batch FROM {batch} WHERE bid = :bid AND token = :token", array(':bid' => $id, ':token' => \Drupal::csrfToken()->get($id)))->fetchField();
     if ($batch) {
         return unserialize($batch);
     }
     return FALSE;
 }

 /**
  * {@inheritdoc}
  */
 public function load($id)
 {
     // Ensure that a session is started before using the CSRF token generator.
     $this->session->start();
     $batch = $this->connection->query("SELECT batch FROM {batch} WHERE bid = :bid AND token = :token", array(':bid' => $id, ':token' => $this->csrfToken->get($id)))->fetchField();
     if ($batch) {
         return unserialize($batch);
     }
     return FALSE;
 }

 /**
  * {@inheritdoc}
  */
 public function buildForm(array $form, FormStateInterface $form_state)
 {
     $num_nodes = $this->database->query("SELECT COUNT(*) FROM {crm_core_contact} WHERE type = :type", array(':type' => $this->entity->id()))->fetchField();
     if ($num_nodes) {
         $caption = \Drupal::translation()->formatPlural($num_nodes, '%type is used by one contact on your site. You can not remove this contact type until you have removed all of the %type contacts.', '%type is used by @count contacts on your site. You may not remove %type until you have removed all of the %type contacts.', array('%type' => $this->entity->label()));
         $form['#title'] = $this->getQuestion();
         $form['description'] = array('#markup' => '<p>' . $caption . '</p>');
         return $form;
     }
     return parent::buildForm($form, $form_state);
 }

 /**
  * {@inheritdoc}
  */
 public function buildForm(array $form, FormStateInterface $form_state)
 {
     $num_nodes = $this->database->query("SELECT COUNT(*) FROM {node} WHERE type = :type", array(':type' => $this->entity->id()))->fetchField();
     if ($num_nodes) {
         $caption = '<p>' . format_plural($num_nodes, '%type is used by 1 piece of content on your site. You can not remove this content type until you have removed all of the %type content.', '%type is used by @count pieces of content on your site. You may not remove %type until you have removed all of the %type content.', array('%type' => $this->entity->label())) . '</p>';
         $form['#title'] = $this->getQuestion();
         $form['description'] = array('#markup' => $caption);
         return $form;
     }
     return parent::buildForm($form, $form_state);
 }

 function title()
 {
     if (!$this->argument) {
         $title = \Drupal::config('user.settings')->get('anonymous');
     } else {
         $title = $this->database->query('SELECT u.name FROM {users} u WHERE u.uid = :uid', array(':uid' => $this->argument))->fetchField();
     }
     if (empty($title)) {
         return t('No user');
     }
     return String::checkPlain($title);
 }

 /**
  * {@inheritdoc}
  */
 public function save($source, $alias, $langcode = LanguageInterface::LANGCODE_NOT_SPECIFIED, $pid = NULL)
 {
     if ($source[0] !== '/') {
         throw new \InvalidArgumentException(sprintf('Source path %s has to start with a slash.', $source));
     }
     if ($alias[0] !== '/') {
         throw new \InvalidArgumentException(sprintf('Alias path %s has to start with a slash.', $alias));
     }
     $fields = array('source' => $source, 'alias' => $alias, 'langcode' => $langcode);
     // Insert or update the alias.
     if (empty($pid)) {
         $try_again = FALSE;
         try {
             $query = $this->connection->insert(static::TABLE)->fields($fields);
             $pid = $query->execute();
         } catch (\Exception $e) {
             // If there was an exception, try to create the table.
             if (!($try_again = $this->ensureTableExists())) {
                 // If the exception happened for other reason than the missing table,
                 // propagate the exception.
                 throw $e;
             }
         }
         // Now that the table has been created, try again if necessary.
         if ($try_again) {
             $query = $this->connection->insert(static::TABLE)->fields($fields);
             $pid = $query->execute();
         }
         $fields['pid'] = $pid;
         $operation = 'insert';
     } else {
         // Fetch the current values so that an update hook can identify what
         // exactly changed.
         try {
             $original = $this->connection->query('SELECT source, alias, langcode FROM {url_alias} WHERE pid = :pid', array(':pid' => $pid))->fetchAssoc();
         } catch (\Exception $e) {
             $this->catchException($e);
             $original = FALSE;
         }
         $fields['pid'] = $pid;
         $query = $this->connection->update(static::TABLE)->fields($fields)->condition('pid', $pid);
         $pid = $query->execute();
         $fields['original'] = $original;
         $operation = 'update';
     }
     if ($pid) {
         // @todo Switch to using an event for this instead of a hook.
         $this->moduleHandler->invokeAll('path_' . $operation, array($fields));
         Cache::invalidateTags(['route_match']);
         return $fields;
     }
     return FALSE;
 }

 function title()
 {
     if (!$this->argument) {
         $title = \Drupal::config('user.settings')->get('anonymous');
     } else {
         $title = $this->database->query('SELECT name FROM {users_field_data} WHERE uid = :uid AND default_langcode = 1', array(':uid' => $this->argument))->fetchField();
     }
     if (empty($title)) {
         return $this->t('No user');
     }
     return $title;
 }

 /**
  * @param Profile $profile
  * @param int $qid
  *
  * @return JsonResponse
  */
 public function explainAction(Profile $profile, $qid)
 {
     $query = $this->getQuery($profile, $qid);
     $data = [];
     $result = $this->database->query('EXPLAIN ' . $query['query'], (array) $query['args'])->fetchAllAssoc('table');
     $i = 1;
     foreach ($result as $row) {
         foreach ($row as $key => $value) {
             $data[$i][$key] = $value;
         }
         $i++;
     }
     return new JsonResponse(['data' => $data]);
 }

 /**
  * {@inheritdoc}
  */
 public function updateIndex(NodeInterface $node)
 {
     $nid = $node->id();
     $count = $this->database->query("SELECT COUNT(cid) FROM {comment_field_data} c INNER JOIN {forum_index} i ON c.entity_id = i.nid WHERE c.entity_id = :nid AND c.field_name = 'comment_forum' AND c.entity_type = 'node' AND c.status = :status AND c.default_langcode = 1", array(':nid' => $nid, ':status' => CommentInterface::PUBLISHED))->fetchField();
     if ($count > 0) {
         // Comments exist.
         $last_reply = $this->database->queryRange("SELECT cid, name, created, uid FROM {comment_field_data} WHERE entity_id = :nid AND field_name = 'comment_forum' AND entity_type = 'node' AND status = :status AND default_langcode = 1 ORDER BY cid DESC", 0, 1, array(':nid' => $nid, ':status' => CommentInterface::PUBLISHED))->fetchObject();
         $this->database->update('forum_index')->fields(array('comment_count' => $count, 'last_comment_timestamp' => $last_reply->created))->condition('nid', $nid)->execute();
     } else {
         // Comments do not exist.
         // @todo This should be actually filtering on the desired node language
         $this->database->update('forum_index')->fields(array('comment_count' => 0, 'last_comment_timestamp' => $node->getCreatedTime()))->condition('nid', $nid)->execute();
     }
 }

 /**
  * {@inheritdoc}
  */
 public function read($sid)
 {
     global $user;
     // Handle the case of first time visitors and clients that don't store
     // cookies (eg. web crawlers).
     $insecure_session_name = $this->sessionManager->getInsecureName();
     $cookies = $this->requestStack->getCurrentRequest()->cookies;
     if (!$cookies->has($this->getName()) && !$cookies->has($insecure_session_name)) {
         $user = new UserSession();
         return '';
     }
     // Otherwise, if the session is still active, we have a record of the
     // client's session in the database. If it's HTTPS then we are either have a
     // HTTPS session or we are about to log in so we check the sessions table
     // for an anonymous session with the non-HTTPS-only cookie. The session ID
     // that is in the user's cookie is hashed before being stored in the
     // database as a security measure. Thus, we have to hash it to match the
     // database.
     if ($this->requestStack->getCurrentRequest()->isSecure()) {
         // Try to load a session using the HTTPS-only secure session id.
         $values = $this->connection->query("SELECT u.*, s.* FROM {users} u INNER JOIN {sessions} s ON u.uid = s.uid WHERE s.ssid = :ssid", array(':ssid' => Crypt::hashBase64($sid)))->fetchAssoc();
         if (!$values) {
             // Fallback and try to load the anonymous non-HTTPS session. Use the
             // non-HTTPS session id as the key.
             if ($cookies->has($insecure_session_name)) {
                 $values = $this->connection->query("SELECT u.*, s.* FROM {users} u INNER JOIN {sessions} s ON u.uid = s.uid WHERE s.sid = :sid AND s.uid = 0", array(':sid' => Crypt::hashBase64($cookies->get($insecure_session_name))))->fetchAssoc();
             }
         }
     } else {
         // Try to load a session using the non-HTTPS session id.
         $values = $this->connection->query("SELECT u.*, s.* FROM {users} u INNER JOIN {sessions} s ON u.uid = s.uid WHERE s.sid = :sid", array(':sid' => Crypt::hashBase64($sid)))->fetchAssoc();
     }
     // We found the client's session record and they are an authenticated,
     // active user.
     if ($values && $values['uid'] > 0 && $values['status'] == 1) {
         // Add roles element to $user.
         $rids = $this->connection->query("SELECT ur.rid FROM {users_roles} ur WHERE ur.uid = :uid", array(':uid' => $values['uid']))->fetchCol();
         $values['roles'] = array_merge(array(DRUPAL_AUTHENTICATED_RID), $rids);
         $user = new UserSession($values);
     } elseif ($values) {
         // The user is anonymous or blocked. Only preserve two fields from the
         // {sessions} table.
         $user = new UserSession(array('session' => $values['session'], 'access' => $values['access']));
     } else {
         // The session has expired.
         $user = new UserSession();
     }
     return $user->session;
 }