PHP AdapterInterface::select方法代码示例

 /**
  * Execute full indexation
  *
  * @return void
  */
 public function executeFull()
 {
     $results = [];
     foreach ($this->getTables() as $table => $columns) {
         if (!count($columns)) {
             continue;
         }
         foreach ($columns as $idx => $col) {
             $columns[$idx] = '`' . $col . '`';
         }
         $select = $this->connection->select();
         $fromColumns = new \Zend_Db_Expr('CONCAT(' . implode(",' ',", $columns) . ') as data_index');
         $select->from($table, $fromColumns);
         $result = $this->connection->query($select);
         while ($row = $result->fetch()) {
             $data = $row['data_index'];
             $this->split($data, $results);
         }
     }
     $indexTable = $this->resource->getTableName('mst_misspell_index');
     $this->connection->delete($indexTable);
     $rows = [];
     foreach ($results as $word => $freq) {
         $rows[] = ['keyword' => $word, 'trigram' => $this->text->getTrigram($word), 'frequency' => $freq / count($results)];
         if (count($rows) > 1000) {
             $this->connection->insertArray($indexTable, ['keyword', 'trigram', 'frequency'], $rows);
             $rows = [];
         }
     }
     if (count($rows) > 0) {
         $this->connection->insertArray($indexTable, ['keyword', 'trigram', 'frequency'], $rows);
     }
     $this->connection->delete($this->resource->getTableName('mst_misspell_suggest'));
 }

 /**
  * @magentoDataFixture Magento/Bundle/_files/product.php
  * @covers \Magento\Indexer\Model\Indexer::reindexAll
  * @covers \Magento\Bundle\Model\Product\Type::getSearchableData
  */
 public function testPrepareProductIndexForBundleProduct()
 {
     $this->indexer->reindexAll();
     $select = $this->connectionMock->select()->from($this->resource->getTableName('catalogsearch_fulltext_scope1'))->where('`data_index` LIKE ?', '%' . 'Bundle Product Items' . '%');
     $result = $this->connectionMock->fetchAll($select);
     $this->assertCount(1, $result);
 }

 /**
  * Prepare select statement for specific filter
  *
  * @param Filter $filter
  * @return \Magento\Framework\DB\Select
  */
 protected function prepareSelect($filter)
 {
     $select = $this->connection->select();
     $select->from(self::TABLE_NAME);
     foreach ($filter->getFilter() as $column => $value) {
         $select->where($this->connection->quoteIdentifier($column) . ' IN (?)', $value);
     }
     return $select;
 }

 /**
  * Check that entity has overridden url key for specific store
  *
  * @param int $storeId
  * @param int $entityId
  * @param string $entityType
  * @throws \InvalidArgumentException
  * @return bool
  */
 public function doesEntityHaveOverriddenUrlKeyForStore($storeId, $entityId, $entityType)
 {
     $attribute = $this->eavConfig->getAttribute($entityType, 'url_key');
     if (!$attribute) {
         throw new \InvalidArgumentException(sprintf('Cannot retrieve attribute for entity type "%s"', $entityType));
     }
     $select = $this->connection->select()->from($attribute->getBackendTable(), 'store_id')->where('attribute_id = ?', $attribute->getId())->where('entity_id = ?', $entityId);
     return in_array($storeId, $this->connection->fetchCol($select));
 }

 /**
  * @magentoDataFixture Magento/Review/_files/customer_review_with_rating.php
  */
 public function testAggregate()
 {
     $rating = $this->reviewCollection->getFirstItem();
     $this->reviewResource->aggregate($rating);
     $select = $this->adapter->select()->from($this->resource->getTableName('review_entity_summary'));
     $result = $this->adapter->fetchRow($select);
     $this->assertEquals(1, $result['reviews_count']);
     $this->assertEquals(40, $result['rating_summary']);
 }

 /**
  * Prepare select statement for specific filter
  *
  * @param array $data
  * @return \Magento\Framework\DB\Select
  */
 protected function prepareSelect($data)
 {
     $select = $this->connection->select();
     $select->from($this->resource->getTableName(self::TABLE_NAME));
     foreach ($data as $column => $value) {
         $select->where($this->connection->quoteIdentifier($column) . ' IN (?)', $value);
     }
     return $select;
 }

 /**
  * Remove products from flat that are not exist
  *
  * @param array $ids
  * @param int $storeId
  * @return void
  */
 public function removeDeletedProducts(array &$ids, $storeId)
 {
     $select = $this->connection->select()->from($this->productIndexerHelper->getTable('catalog_product_entity'))->where('entity_id IN(?)', $ids);
     $result = $this->connection->query($select);
     $existentProducts = [];
     foreach ($result->fetchAll() as $product) {
         $existentProducts[] = $product['entity_id'];
     }
     $productsToDelete = array_diff($ids, $existentProducts);
     $ids = $existentProducts;
     $this->deleteProductsFromStore($productsToDelete, $storeId);
 }

 /**
  * Check that entity has overridden url attribute for specific store
  *
  * @param int $storeId
  * @param int $entityId
  * @param string $entityType
  * @param mixed $attributeName
  * @throws \InvalidArgumentException
  * @return bool
  */
 protected function doesEntityHaveOverriddenUrlAttributeForStore($storeId, $entityId, $entityType, $attributeName)
 {
     $attribute = $this->eavConfig->getAttribute($entityType, $attributeName);
     if (!$attribute) {
         throw new \InvalidArgumentException(sprintf('Cannot retrieve attribute for entity type "%s"', $entityType));
     }
     $linkFieldName = $attribute->getEntity()->getLinkField();
     if (!$linkFieldName) {
         $linkFieldName = $this->getMetadataPool()->getMetadata(ProductInterface::class)->getLinkField();
     }
     $select = $this->connection->select()->from(['e' => $attribute->getEntity()->getEntityTable()], [])->join(['e_attr' => $attribute->getBackendTable()], "e.{$linkFieldName} = e_attr.{$linkFieldName}", 'store_id')->where('e_attr.attribute_id = ?', $attribute->getId())->where('e.entity_id = ?', $entityId);
     return in_array($storeId, $this->connection->fetchCol($select));
 }

 /**
  * @dataProvider insertDataProvider
  */
 public function testInsertForce($data)
 {
     $this->assertEquals(1, $this->_connection->insertForce($this->_tableName, $data));
     $select = $this->_connection->select()->from($this->_tableName);
     $result = $this->_connection->fetchRow($select);
     $this->assertEquals($data, $result);
 }

 /**
  * @param FilterInterface $filter
  * @param bool $isNegation
  * @param string $query
  * @return string
  */
 private function processQueryWithField(FilterInterface $filter, $isNegation, $query)
 {
     /** @var \Magento\Catalog\Model\ResourceModel\Eav\Attribute $attribute */
     $attribute = $this->config->getAttribute(Product::ENTITY, $filter->getField());
     if ($filter->getField() === 'price') {
         $resultQuery = str_replace($this->connection->quoteIdentifier('price'), $this->connection->quoteIdentifier('price_index.min_price'), $query);
     } elseif ($filter->getField() === 'category_ids') {
         return 'category_ids_index.category_id = ' . $filter->getValue();
     } elseif ($attribute->isStatic()) {
         $alias = $this->tableMapper->getMappingAlias($filter);
         $resultQuery = str_replace($this->connection->quoteIdentifier($attribute->getAttributeCode()), $this->connection->quoteIdentifier($alias . '.' . $attribute->getAttributeCode()), $query);
     } elseif ($filter->getType() === FilterInterface::TYPE_TERM && in_array($attribute->getFrontendInput(), ['select', 'multiselect'], true)) {
         $alias = $this->tableMapper->getMappingAlias($filter);
         if (is_array($filter->getValue())) {
             $value = sprintf('%s IN (%s)', $isNegation ? 'NOT' : '', implode(',', $filter->getValue()));
         } else {
             $value = ($isNegation ? '!' : '') . '= ' . $filter->getValue();
         }
         $resultQuery = sprintf('%1$s.value %2$s', $alias, $value);
     } else {
         $table = $attribute->getBackendTable();
         $select = $this->connection->select();
         $ifNullCondition = $this->connection->getIfNullSql('current_store.value', 'main_table.value');
         $currentStoreId = $this->scopeResolver->getScope()->getId();
         $select->from(['main_table' => $table], 'entity_id')->joinLeft(['current_store' => $table], 'current_store.attribute_id = main_table.attribute_id AND current_store.store_id = ' . $currentStoreId, null)->columns([$filter->getField() => $ifNullCondition])->where('main_table.attribute_id = ?', $attribute->getAttributeId())->where('main_table.store_id = ?', Store::DEFAULT_STORE_ID)->having($query);
         $resultQuery = 'search_index.entity_id IN (
             select entity_id from  ' . $this->conditionManager->wrapBrackets($select) . ' as filter
         )';
     }
     return $resultQuery;
 }

 /**
  * Return best match (from database)
  *
  * @param string $query
  * @return array
  */
 public function getBestMatch($query)
 {
     $query = trim($query);
     if (!$query) {
         return ['keyword' => $query, 'diff' => 100];
     }
     $len = intval($this->text->strlen($query));
     $trigram = $this->text->getTrigram($this->text->strtolower($query));
     $tableName = $this->resource->getTableName('mst_misspell_index');
     $select = $this->connection->select();
     $relevance = '(-ABS(LENGTH(keyword) - ' . $len . ') + MATCH (trigram) AGAINST("' . $trigram . '"))';
     $relevancy = new \Zend_Db_Expr($relevance . ' + frequency AS relevancy');
     $select->from($tableName, ['keyword', $relevancy, 'frequency'])->order('relevancy desc')->limit(10);
     $keywords = $this->connection->fetchAll($select);
     $maxFreq = 0.0001;
     foreach ($keywords as $keyword) {
         $maxFreq = max($keyword['frequency'], $maxFreq);
     }
     $preResults = [];
     foreach ($keywords as $keyword) {
         $preResults[$keyword['keyword']] = $this->damerau->similarity($query, $keyword['keyword']) + $keyword['frequency'] * (10 / $maxFreq);
     }
     arsort($preResults);
     $keys = array_keys($preResults);
     if (count($keys) > 0) {
         $keyword = $keys[0];
         $keyword = $this->toSameRegister($keyword, $query);
         $diff = $preResults[$keys[0]];
         $result = ['keyword' => $keyword, 'diff' => $diff];
     } else {
         $result = ['keyword' => $query, 'diff' => 100];
     }
     return $result;
 }

 /**
  * Clean unused relation products
  *
  * @param int $storeId
  * @return \Magento\Catalog\Model\Indexer\Product\Flat\AbstractAction
  */
 protected function _cleanRelationProducts($storeId)
 {
     if (!$this->_productIndexerHelper->isAddChildData()) {
         return $this;
     }
     foreach ($this->_getProductTypeInstances() as $typeInstance) {
         /** @var $typeInstance \Magento\Catalog\Model\Product\Type\AbstractType */
         if (!$typeInstance->isComposite(null)) {
             continue;
         }
         $relation = $typeInstance->getRelationInfo();
         if ($relation && $relation->getTable() && $relation->getParentFieldName() && $relation->getChildFieldName()) {
             $select = $this->_connection->select()->distinct(true)->from($this->_productIndexerHelper->getTable($relation->getTable()), "{$relation->getParentFieldName()}");
             $joinLeftCond = ["e.entity_id = t.{$relation->getParentFieldName()}", "e.child_id = t.{$relation->getChildFieldName()}"];
             if ($relation->getWhere() !== null) {
                 $select->where($relation->getWhere());
                 $joinLeftCond[] = $relation->getWhere();
             }
             $entitySelect = new \Zend_Db_Expr($select->__toString());
             /** @var $select \Magento\Framework\DB\Select */
             $select = $this->_connection->select()->from(['e' => $this->_productIndexerHelper->getFlatTableName($storeId)], null)->joinLeft(['t' => $this->_productIndexerHelper->getTable($relation->getTable())], implode(' AND ', $joinLeftCond), [])->where('e.is_child = ?', 1)->where('e.entity_id IN(?)', $entitySelect)->where("t.{$relation->getChildFieldName()} IS NULL");
             $sql = $select->deleteFromSelect('e');
             $this->_connection->query($sql);
         }
     }
     return $this;
 }

 /**
  * Return timestamp for the first transaction related to PV.
  */
 public function getFirstDateForPvTransactions()
 {
     $asAcc = 'paa';
     $asTrans = 'pat';
     $asType = 'pata';
     $tblAcc = $this->_resource->getTableName(Account::ENTITY_NAME);
     $tblTrans = $this->_resource->getTableName(Transaction::ENTITY_NAME);
     $tblType = $this->_resource->getTableName(TypeAsset::ENTITY_NAME);
     // SELECT FROM prxgt_acc_transaction pat
     $query = $this->_conn->select();
     $query->from([$asTrans => $tblTrans], [Transaction::ATTR_DATE_APPLIED]);
     // LEFT JOIN prxgt_acc_account paa ON paa.id = pat.debit_acc_id
     $on = $asAcc . '.' . Account::ATTR_ID . '=' . $asTrans . '.' . Transaction::ATTR_DEBIT_ACC_ID;
     $query->join([$asAcc => $tblAcc], $on, null);
     // LEFT JOIN prxgt_acc_type_asset pata ON paa.asset_type_id = pata.id
     $on = $asAcc . '.' . Account::ATTR_ASSET_TYPE_ID . '=' . $asType . '.' . TypeAsset::ATTR_ID;
     $query->join([$asType => $tblType], $on, null);
     // WHERE
     $where = $asType . '.' . TypeAsset::ATTR_CODE . '=' . $this->_conn->quote(Cfg::CODE_TYPE_ASSET_PV);
     $query->where($where);
     // ORDER & LIMIT
     $query->order($asTrans . '.' . Transaction::ATTR_DATE_APPLIED . ' ASC');
     $query->limit(1);
     // $sql = (string)$query;
     $result = $this->_conn->fetchOne($query);
     return $result;
 }

 /**
  * Move tree node
  *
  * @param Node $node
  * @param Node $newParent
  * @param Node $prevNode
  * @return void
  * @throws \Exception
  * @todo Use adapter for generate conditions
  */
 public function move($node, $newParent, $prevNode = null)
 {
     $position = 1;
     $oldPath = $node->getData($this->_pathField);
     $newPath = $newParent->getData($this->_pathField);
     $newPath = $newPath . '/' . $node->getId();
     $oldPathLength = strlen($oldPath);
     $newLevel = $newParent->getLevel() + 1;
     $levelDisposition = $newLevel - $node->getLevel();
     $data = [$this->_levelField => new \Zend_Db_Expr("{$this->_levelField} + '{$levelDisposition}'"), $this->_pathField => new \Zend_Db_Expr("CONCAT('{$newPath}', RIGHT({$this->_pathField}, LENGTH({$this->_pathField}) - {$oldPathLength}))")];
     $condition = $this->_conn->quoteInto("{$this->_pathField} REGEXP ?", "^{$oldPath}(/|\$)");
     $this->_conn->beginTransaction();
     $reorderData = [$this->_orderField => new \Zend_Db_Expr("{$this->_orderField} + 1")];
     try {
         if ($prevNode && $prevNode->getId()) {
             $reorderCondition = "{$this->_orderField} > {$prevNode->getData($this->_orderField)}";
             $position = $prevNode->getData($this->_orderField) + 1;
         } else {
             $reorderCondition = $this->_conn->quoteInto("{$this->_pathField} REGEXP ?", "^{$newParent->getData($this->_pathField)}/[0-9]+\$");
             $select = $this->_conn->select()->from($this->_table, new \Zend_Db_Expr("MIN({$this->_orderField})"))->where($reorderCondition);
             $position = (int) $this->_conn->fetchOne($select);
         }
         $this->_conn->update($this->_table, $reorderData, $reorderCondition);
         $this->_conn->update($this->_table, $data, $condition);
         $this->_conn->update($this->_table, [$this->_orderField => $position, $this->_levelField => $newLevel], $this->_conn->quoteInto("{$this->_idField} = ?", $node->getId()));
         $this->_conn->commit();
     } catch (\Exception $e) {
         $this->_conn->rollBack();
         throw new \Exception("Can't move tree node due to error: " . $e->getMessage());
     }
 }

 /**
  * Apply diff. between 0 store and current store to temporary flat table
  *
  * @param array $tables
  * @param array $changedIds
  * @param int|string $storeId
  * @param string $valueFieldSuffix
  * @return void
  */
 protected function _updateTemporaryTableByStoreValues(array $tables, array $changedIds, $storeId, $valueFieldSuffix)
 {
     $flatColumns = $this->_productIndexerHelper->getFlatColumns();
     $temporaryFlatTableName = $this->_getTemporaryTableName($this->_productIndexerHelper->getFlatTableName($storeId));
     $linkField = $this->metadataPool->getMetadata(ProductInterface::class)->getLinkField();
     foreach ($tables as $tableName => $columns) {
         foreach ($columns as $attribute) {
             /* @var $attribute \Magento\Eav\Model\Entity\Attribute */
             $attributeCode = $attribute->getAttributeCode();
             if ($attribute->getBackend()->getType() != 'static') {
                 $joinCondition = sprintf('t.%s = e.%s', $linkField, $linkField) . ' AND t.attribute_id=' . $attribute->getId() . ' AND t.store_id = ' . $storeId . ' AND t.value IS NOT NULL';
                 /** @var $select \Magento\Framework\DB\Select */
                 $select = $this->_connection->select()->joinInner(['t' => $tableName], $joinCondition, [$attributeCode => 't.value']);
                 if (!empty($changedIds)) {
                     $select->where($this->_connection->quoteInto('e.entity_id IN (?)', $changedIds));
                 }
                 $sql = $select->crossUpdateFromSelect(['e' => $temporaryFlatTableName]);
                 $this->_connection->query($sql);
             }
             //Update not simple attributes (eg. dropdown)
             if (isset($flatColumns[$attributeCode . $valueFieldSuffix])) {
                 $select = $this->_connection->select()->joinInner(['t' => $this->_productIndexerHelper->getTable('eav_attribute_option_value')], 't.option_id = e.' . $attributeCode . ' AND t.store_id=' . $storeId, [$attributeCode . $valueFieldSuffix => 't.value']);
                 if (!empty($changedIds)) {
                     $select->where($this->_connection->quoteInto('e.entity_id IN (?)', $changedIds));
                 }
                 $sql = $select->crossUpdateFromSelect(['e' => $temporaryFlatTableName]);
                 $this->_connection->query($sql);
             }
         }
     }
 }