PHP Connection::select方法代码示例

 /**
  * Displays a listing of migration messages.
  *
  * Messages are truncated at 56 chars.
  *
  * @return array
  *   A render array as expected by drupal_render().
  */
 public function overview($migration_group, $migration)
 {
     $rows = [];
     $classes = static::getLogLevelClassMap();
     /** @var MigrationInterface $migration */
     $migration = Migration::load($migration);
     $source_id_field_names = array_keys($migration->getSourcePlugin()->getIds());
     $column_number = 1;
     foreach ($source_id_field_names as $source_id_field_name) {
         $header[] = ['data' => $source_id_field_name, 'field' => 'sourceid' . $column_number++, 'class' => [RESPONSIVE_PRIORITY_MEDIUM]];
     }
     $header[] = ['data' => $this->t('Severity level'), 'field' => 'level', 'class' => [RESPONSIVE_PRIORITY_LOW]];
     $header[] = ['data' => $this->t('Message'), 'field' => 'message'];
     $message_table = $migration->getIdMap()->messageTableName();
     $query = $this->database->select($message_table, 'm')->extend('\\Drupal\\Core\\Database\\Query\\PagerSelectExtender')->extend('\\Drupal\\Core\\Database\\Query\\TableSortExtender');
     $query->fields('m');
     $result = $query->limit(50)->orderByHeader($header)->execute();
     foreach ($result as $message_row) {
         $column_number = 1;
         foreach ($source_id_field_names as $source_id_field_name) {
             $column_name = 'sourceid' . $column_number++;
             $row[$column_name] = $message_row->{$column_name};
         }
         $row['level'] = $message_row->level;
         $row['message'] = $message_row->message;
         $row['class'] = [Html::getClass('migrate-message-' . $message_row->level), $classes[$message_row->level]];
         $rows[] = $row;
     }
     $build['message_table'] = ['#type' => 'table', '#header' => $header, '#rows' => $rows, '#attributes' => ['id' => $message_table, 'class' => [$message_table]], '#empty' => $this->t('No messages for this migration.')];
     $build['message_pager'] = ['#type' => 'pager'];
     return $build;
 }

 private function get_sitemap_from_db()
 {
     /** @var SelectInterface $query */
     $query = $this->db->select('custom_sitemap', 's')->fields('s', array('sitemap_string'))->condition('language_code', $this->language->getId());
     $result = $query->execute()->fetchAll();
     return !empty($result[0]->sitemap_string) ? $result[0]->sitemap_string : NULL;
 }

 /**
  * {@inheritdoc}
  */
 public function isAllowed($name, $threshold, $window = 3600, $identifier = NULL)
 {
     if (!isset($identifier)) {
         $identifier = $this->requestStack->getCurrentRequest()->getClientIp();
     }
     $number = $this->connection->select('flood', 'f')->condition('event', $name)->condition('identifier', $identifier)->condition('timestamp', REQUEST_TIME - $window, '>')->countQuery()->execute()->fetchField();
     return $number < $threshold;
 }

 /**
  * @inheritdoc
  */
 public function getUid($authname, $provider)
 {
     $authname = $this->connection->select('authmap', 'am')->fields('am', array('uid'))->condition('authname', $authname)->condition('provider', $provider)->range(0, 1)->execute()->fetchObject();
     if ($authname) {
         return $authname->uid;
     }
     return FALSE;
 }

 /**
  * Implements Drupal\file\FileUsage\FileUsageInterface::listUsage().
  */
 public function listUsage(FileInterface $file)
 {
     $result = $this->connection->select($this->tableName, 'f')->fields('f', array('module', 'type', 'id', 'count'))->condition('fid', $file->id())->condition('count', 0, '>')->execute();
     $references = array();
     foreach ($result as $usage) {
         $references[$usage->module][$usage->type][$usage->id] = $usage->count;
     }
     return $references;
 }

 /**
  * {@inheritdoc}
  */
 public function loadMultiple($nids, $access = TRUE)
 {
     $query = $this->connection->select('node', 'n', array('fetch' => \PDO::FETCH_ASSOC));
     $query->fields('n');
     $query->condition('n.nid', $nids, 'IN');
     if ($access) {
         $query->addTag('node_access');
     }
     return $query->execute();
 }

 /**
  * {@inheritdoc}
  */
 public function getRange($start, $stop = NULL)
 {
     $query = $this->connection->select($this->table, 't')->fields('t', array('value'))->condition('collection', $this->collection)->condition('name', $start, '>=');
     if ($stop !== NULL) {
         $query->condition('name', $stop, '<=');
     }
     $result = $query->orderBy('name', 'ASC')->execute();
     $values = array();
     foreach ($result as $item) {
         $values[] = $this->serializer->decode($item->value);
     }
     return $values;
 }

 public function query($group_by = FALSE)
 {
     $this->ensureMyTable();
     // Use the table definition to correctly add this user ID condition.
     if ($this->table != 'comment_field_data') {
         $subselect = $this->database->select('comment_field_data', 'c');
         $subselect->addField('c', 'cid');
         $subselect->condition('c.uid', $this->argument);
         $entity_id = $this->definition['entity_id'];
         $entity_type = $this->definition['entity_type'];
         $subselect->where("c.entity_id = {$this->tableAlias}.{$entity_id}");
         $subselect->condition('c.entity_type', $entity_type);
         $condition = db_or()->condition("{$this->tableAlias}.uid", $this->argument, '=')->exists($subselect);
         $this->query->addWhere(0, $condition);
     }
 }

 /**
  * {@inheritdoc}
  */
 public function update(CommentInterface $comment)
 {
     // Allow bulk updates and inserts to temporarily disable the maintenance of
     // the {comment_entity_statistics} table.
     if (!$this->state->get('comment.maintain_entity_statistics')) {
         return;
     }
     $query = $this->database->select('comment_field_data', 'c');
     $query->addExpression('COUNT(cid)');
     $count = $query->condition('c.entity_id', $comment->getCommentedEntityId())->condition('c.entity_type', $comment->getCommentedEntityTypeId())->condition('c.field_name', $comment->getFieldName())->condition('c.status', CommentInterface::PUBLISHED)->condition('default_langcode', 1)->execute()->fetchField();
     if ($count > 0) {
         // Comments exist.
         $last_reply = $this->database->select('comment_field_data', 'c')->fields('c', array('cid', 'name', 'changed', 'uid'))->condition('c.entity_id', $comment->getCommentedEntityId())->condition('c.entity_type', $comment->getCommentedEntityTypeId())->condition('c.field_name', $comment->getFieldName())->condition('c.status', CommentInterface::PUBLISHED)->condition('default_langcode', 1)->orderBy('c.created', 'DESC')->range(0, 1)->execute()->fetchObject();
         // Use merge here because entity could be created before comment field.
         $this->database->merge('comment_entity_statistics')->fields(array('cid' => $last_reply->cid, 'comment_count' => $count, 'last_comment_timestamp' => $last_reply->changed, 'last_comment_name' => $last_reply->uid ? '' : $last_reply->name, 'last_comment_uid' => $last_reply->uid))->keys(array('entity_id' => $comment->getCommentedEntityId(), 'entity_type' => $comment->getCommentedEntityTypeId(), 'field_name' => $comment->getFieldName()))->execute();
     } else {
         // Comments do not exist.
         $entity = $comment->getCommentedEntity();
         // Get the user ID from the entity if it's set, or default to the
         // currently logged in user.
         if ($entity instanceof EntityOwnerInterface) {
             $last_comment_uid = $entity->getOwnerId();
         }
         if (!isset($last_comment_uid)) {
             // Default to current user when entity does not implement
             // EntityOwnerInterface or author is not set.
             $last_comment_uid = $this->currentUser->id();
         }
         $this->database->update('comment_entity_statistics')->fields(array('cid' => 0, 'comment_count' => 0, 'last_comment_timestamp' => $entity instanceof EntityChangedInterface ? $entity->getChangedTime() : REQUEST_TIME, 'last_comment_name' => '', 'last_comment_uid' => $last_comment_uid))->condition('entity_id', $comment->getCommentedEntityId())->condition('entity_type', $comment->getCommentedEntityTypeId())->condition('field_name', $comment->getFieldName())->execute();
     }
     // Reset the cache of the commented entity so that when the entity is loaded
     // the next time, the statistics will be loaded again.
     $this->entityManager->getStorage($comment->getCommentedEntityTypeId())->resetCache(array($comment->getCommentedEntityId()));
 }

 /**
  * {@inheritdoc}
  */
 public function count()
 {
     if (!isset($this->count)) {
         $this->count = (int) $this->database->select($this->tableName)->countQuery()->execute();
     }
     return $this->count;
 }

  /**
   * Decrements count of flagged entities.
   *
   * @param \Drupal\flag\Event\FlaggingEvent $event
   *   The flagging Event.
   */
  public function decrementFlagCounts(FlaggingEvent $event) {

    /* @var \Drupal\flag\FlaggingInterface flag */
    $flag = $event->getFlag();
    /* @var \Drupal\Core\Entity\EntityInterface $entity */
    $entity = $event->getEntity();

    $count_result = $this->connection->select('flag_counts')
      ->fields(NULL, ['flag_id', 'entity_id', 'entity_type', 'count'])
      ->condition('flag_id', $flag->id())
      ->condition('entity_id', $entity->id())
      ->condition('entity_type', $entity->getEntityTypeId())
      ->execute()
      ->fetchAll();
    if ($count_result[0]->count == '1') {
      $this->connection->delete('flag_counts')
        ->condition('flag_id', $flag->id())
        ->condition('entity_id', $entity->id())
        ->condition('entity_type', $entity->getEntityTypeId())
        ->execute();
    }
    else {
      $this->connection->update('flag_counts')
        ->expression('count', 'count - 1')
        ->condition('flag_id', $flag->id())
        ->condition('entity_id', $entity->id())
        ->condition('entity_type', $entity->getEntityTypeId())
        ->execute();
    }
    $this->resetLoadedCounts($entity, $flag);
  }

 /**
  * {@inheritdoc}
  */
 public function execute()
 {
     $results = array();
     if (!$this->isSearchExecutable()) {
         return $results;
     }
     $keys = $this->keywords;
     // Replace wildcards with MySQL/PostgreSQL wildcards.
     $keys = preg_replace('!\\*+!', '%', $keys);
     $query = $this->database->select('users')->extend('Drupal\\Core\\Database\\Query\\PagerSelectExtender');
     $query->fields('users', array('uid'));
     if ($this->currentUser->hasPermission('administer users')) {
         // Administrators can also search in the otherwise private email field, and
         // they don't need to be restricted to only active users.
         $query->fields('users', array('mail'));
         $query->condition($query->orConditionGroup()->condition('name', '%' . $this->database->escapeLike($keys) . '%', 'LIKE')->condition('mail', '%' . $this->database->escapeLike($keys) . '%', 'LIKE'));
     } else {
         // Regular users can only search via usernames, and we do not show them
         // blocked accounts.
         $query->condition('name', '%' . $this->database->escapeLike($keys) . '%', 'LIKE')->condition('status', 1);
     }
     $uids = $query->limit(15)->execute()->fetchCol();
     $accounts = $this->entityManager->getStorage('user')->loadMultiple($uids);
     foreach ($accounts as $account) {
         $result = array('title' => $account->getUsername(), 'link' => url('user/' . $account->id(), array('absolute' => TRUE)));
         if ($this->currentUser->hasPermission('administer users')) {
             $result['title'] .= ' (' . $account->getEmail() . ')';
         }
         $results[] = $result;
     }
     return $results;
 }

 /**
  * Queries to find search results, and sets status messages.
  *
  * This method can assume that $this->isSearchExecutable() has already been
  * checked and returned TRUE.
  *
  * @return \Drupal\Core\Database\StatementInterface|null
  *   Results from search query execute() method, or NULL if the search
  *   failed.
  */
 protected function findResults()
 {
     $keys = $this->keywords;
     // Build matching conditions.
     $query = $this->database->select('search_index', 'i', array('target' => 'replica'))->extend('Drupal\\search\\SearchQuery')->extend('Drupal\\Core\\Database\\Query\\PagerSelectExtender');
     $query->join('node_field_data', 'n', 'n.nid = i.sid');
     $query->condition('n.status', 1)->addTag('node_access')->searchExpression($keys, $this->getPluginId());
     // Handle advanced search filters in the f query string.
     // \Drupal::request()->query->get('f') is an array that looks like this in
     // the URL: ?f[]=type:page&f[]=term:27&f[]=term:13&f[]=langcode:en
     // So $parameters['f'] looks like:
     // array('type:page', 'term:27', 'term:13', 'langcode:en');
     // We need to parse this out into query conditions, some of which go into
     // the keywords string, and some of which are separate conditions.
     $parameters = $this->getParameters();
     if (!empty($parameters['f']) && is_array($parameters['f'])) {
         $filters = array();
         // Match any query value that is an expected option and a value
         // separated by ':' like 'term:27'.
         $pattern = '/^(' . implode('|', array_keys($this->advanced)) . '):([^ ]*)/i';
         foreach ($parameters['f'] as $item) {
             if (preg_match($pattern, $item, $m)) {
                 // Use the matched value as the array key to eliminate duplicates.
                 $filters[$m[1]][$m[2]] = $m[2];
             }
         }
         // Now turn these into query conditions. This assumes that everything in
         // $filters is a known type of advanced search.
         foreach ($filters as $option => $matched) {
             $info = $this->advanced[$option];
             // Insert additional conditions. By default, all use the OR operator.
             $operator = empty($info['operator']) ? 'OR' : $info['operator'];
             $where = new Condition($operator);
             foreach ($matched as $value) {
                 $where->condition($info['column'], $value);
             }
             $query->condition($where);
             if (!empty($info['join'])) {
                 $query->join($info['join']['table'], $info['join']['alias'], $info['join']['condition']);
             }
         }
     }
     // Add the ranking expressions.
     $this->addNodeRankings($query);
     // Run the query.
     $find = $query->fields('i', array('langcode'))->groupBy('i.langcode')->limit(10)->execute();
     // Check query status and set messages if needed.
     $status = $query->getStatus();
     if ($status & SearchQuery::EXPRESSIONS_IGNORED) {
         drupal_set_message($this->t('Your search used too many AND/OR expressions. Only the first @count terms were included in this search.', array('@count' => $this->searchSettings->get('and_or_limit'))), 'warning');
     }
     if ($status & SearchQuery::LOWER_CASE_OR) {
         drupal_set_message($this->t('Search for either of the two terms with uppercase <strong>OR</strong>. For example, <strong>cats OR dogs</strong>.'), 'warning');
     }
     if ($status & SearchQuery::NO_POSITIVE_KEYWORDS) {
         drupal_set_message($this->formatPlural($this->searchSettings->get('index.minimum_word_size'), 'You must include at least one keyword to match in the content, and punctuation is ignored.', 'You must include at least one keyword to match in the content. Keywords must be at least @count characters, and punctuation is ignored.'), 'warning');
     }
     return $find;
 }

 /**
  * {@inheritdoc}
  */
 public function unreadTopics($term, $uid)
 {
     $query = $this->connection->select('node_field_data', 'n');
     $query->join('forum', 'f', 'n.vid = f.vid AND f.tid = :tid', array(':tid' => $term));
     $query->leftJoin('history', 'h', 'n.nid = h.nid AND h.uid = :uid', array(':uid' => $uid));
     $query->addExpression('COUNT(n.nid)', 'count');
     return $query->condition('status', 1)->condition('n.default_langcode', 1)->condition('n.created', HISTORY_READ_LIMIT, '>')->isNull('h.nid')->addTag('node_access')->execute()->fetchField();
 }

 /**
  * Tests making use of a custom bundle field.
  */
 public function testCustomBundleFieldUsage()
 {
     entity_test_create_bundle('custom');
     // Check that an entity with bundle entity_test does not have the custom
     // field.
     $storage = $this->entityManager->getStorage('entity_test');
     $entity = $storage->create(['type' => 'entity_test']);
     $this->assertFalse($entity->hasField('custom_bundle_field'));
     // Check that the custom bundle has the defined custom field and check
     // saving and deleting of custom field data.
     $entity = $storage->create(['type' => 'custom']);
     $this->assertTrue($entity->hasField('custom_bundle_field'));
     // Ensure that the field exists in the field map.
     $field_map = \Drupal::entityManager()->getFieldMap();
     $this->assertEqual($field_map['entity_test']['custom_bundle_field'], ['type' => 'string', 'bundles' => ['custom' => 'custom']]);
     $entity->custom_bundle_field->value = 'swanky';
     $entity->save();
     $storage->resetCache();
     $entity = $storage->load($entity->id());
     $this->assertEqual($entity->custom_bundle_field->value, 'swanky', 'Entity was saved correctly');
     $entity->custom_bundle_field->value = 'cozy';
     $entity->save();
     $storage->resetCache();
     $entity = $storage->load($entity->id());
     $this->assertEqual($entity->custom_bundle_field->value, 'cozy', 'Entity was updated correctly.');
     $entity->delete();
     /** @var \Drupal\Core\Entity\Sql\DefaultTableMapping $table_mapping */
     $table_mapping = $storage->getTableMapping();
     $table = $table_mapping->getDedicatedDataTableName($entity->getFieldDefinition('custom_bundle_field'));
     $result = $this->database->select($table, 'f')->fields('f')->condition('f.entity_id', $entity->id())->execute();
     $this->assertFalse($result->fetchAssoc(), 'Field data has been deleted');
     // Create another entity to test that values are marked as deleted when a
     // bundle is deleted.
     $entity = $storage->create(['type' => 'custom', 'custom_bundle_field' => 'new']);
     $entity->save();
     entity_test_delete_bundle('custom');
     $table = $table_mapping->getDedicatedDataTableName($entity->getFieldDefinition('custom_bundle_field'));
     $result = $this->database->select($table, 'f')->condition('f.entity_id', $entity->id())->condition('deleted', 1)->countQuery()->execute();
     $this->assertEqual(1, $result->fetchField(), 'Field data has been deleted');
     // Ensure that the field no longer exists in the field map.
     $field_map = \Drupal::entityManager()->getFieldMap();
     $this->assertFalse(isset($field_map['entity_test']['custom_bundle_field']));
     // @todo Test field purge and table deletion once supported. See
     //   https://www.drupal.org/node/2282119.
     // $this->assertFalse($this->database->schema()->tableExists($table), 'Custom field table was deleted');
 }