PHP DB\Select类代码示例

 /**
  * Render WHERE section
  *
  * @param Select $select
  * @param string $sql
  * @return string
  */
 public function render(Select $select, $sql = '')
 {
     if ($select->getPart(Select::FROM) && $select->getPart(Select::WHERE)) {
         $sql .= ' ' . Select::SQL_WHERE . ' ' . implode(' ', $select->getPart(Select::WHERE));
     }
     return $sql;
 }

 /**
  * Render FROM & JOIN's section
  *
  * @param Select $select
  * @param string $sql
  * @return string
  * @throws \Zend_Db_Select_Exception
  */
 public function render(Select $select, $sql = '')
 {
     /*
      * If no table specified, use RDBMS-dependent solution
      * for table-less query.  e.g. DUAL in Oracle.
      */
     $source = $select->getPart(Select::FROM);
     if (empty($source)) {
         $source = [];
     }
     $from = [];
     foreach ($source as $correlationName => $table) {
         $tmp = '';
         $joinType = $table['joinType'] == Select::FROM ? Select::INNER_JOIN : $table['joinType'];
         // Add join clause (if applicable)
         if (!empty($from)) {
             $tmp .= ' ' . strtoupper($joinType) . ' ';
         }
         $tmp .= $this->getQuotedSchema($table['schema']);
         $tmp .= $this->getQuotedTable($table['tableName'], $correlationName);
         // Add join conditions (if applicable)
         if (!empty($from) && !empty($table['joinCondition'])) {
             $tmp .= ' ' . Select::SQL_ON . ' ' . $table['joinCondition'];
         }
         // Add the table name and condition add to the list
         $from[] = $tmp;
     }
     // Add the list of all joins
     if (!empty($from)) {
         $sql .= ' ' . Select::SQL_FROM . ' ' . implode("\n", $from);
     }
     return $sql;
 }

 /**
  * {@inheritdoc}
  */
 public function build(ScoreBuilder $scoreBuilder, Select $select, RequestQueryInterface $query, $conditionType)
 {
     /** @var $query \Magento\Framework\Search\Request\Query\Match */
     $queryValue = $this->prepareQuery($query->getValue(), $conditionType);
     $fieldList = [];
     foreach ($query->getMatches() as $match) {
         $fieldList[] = $match['field'];
     }
     $resolvedFieldList = $this->resolver->resolve($fieldList);
     $fieldIds = [];
     $columns = [];
     foreach ($resolvedFieldList as $field) {
         if ($field->getType() === FieldInterface::TYPE_FULLTEXT && $field->getAttributeId()) {
             $fieldIds[] = $field->getAttributeId();
         }
         $column = $field->getColumn();
         $columns[$column] = $column;
     }
     $matchQuery = $this->fulltextHelper->getMatchQuery($columns, $queryValue, $this->fulltextSearchMode);
     $scoreBuilder->addCondition($matchQuery, true);
     if ($fieldIds) {
         $matchQuery = sprintf('(%s AND search_index.attribute_id IN (%s))', $matchQuery, implode(',', $fieldIds));
     }
     $select->where($matchQuery);
     return $select;
 }

 protected function setUp()
 {
     parent::setUp();
     $this->setCollectionFactory = $this->getMock('Magento\\Eav\\Model\\ResourceModel\\Entity\\Attribute\\Set\\CollectionFactory', ['create'], [], '', false);
     $this->setCollection = $this->getMock('Magento\\Eav\\Model\\ResourceModel\\Entity\\Attribute\\Set\\Collection', ['setEntityTypeFilter'], [], '', false);
     $this->setCollectionFactory->expects($this->any())->method('create')->will($this->returnValue($this->setCollection));
     $this->setCollection->expects($this->any())->method('setEntityTypeFilter')->will($this->returnValue([]));
     $this->attrCollectionFactory = $this->getMock('Magento\\Catalog\\Model\\ResourceModel\\Product\\Attribute\\CollectionFactory', ['create', 'addFieldToFilter'], [], '', false);
     $this->attrCollectionFactory->expects($this->any())->method('create')->will($this->returnSelf());
     $this->attrCollectionFactory->expects($this->any())->method('addFieldToFilter')->willReturn([]);
     $this->entityModel = $this->getMock('Magento\\CatalogImportExport\\Model\\Import\\Product', ['getErrorAggregator', 'getNewSku', 'getOldSku', 'getNextBunch', 'isRowAllowedToImport', 'getRowScope'], [], '', false);
     $this->entityModel->method('getErrorAggregator')->willReturn($this->getErrorAggregatorObject());
     $this->params = [0 => $this->entityModel, 1 => 'grouped'];
     $this->links = $this->getMock('Magento\\GroupedImportExport\\Model\\Import\\Product\\Type\\Grouped\\Links', [], [], '', false);
     $entityAttributes = [['attribute_set_name' => 'attribute_id', 'attribute_id' => 'attributeSetName']];
     $this->connection = $this->getMock('Magento\\Framework\\DB\\Adapter\\Pdo\\Mysql', ['select', 'fetchAll', 'fetchPairs', 'joinLeft', 'insertOnDuplicate', 'delete', 'quoteInto'], [], '', false);
     $this->select = $this->getMock('Magento\\Framework\\DB\\Select', ['from', 'where', 'joinLeft', 'getConnection'], [], '', false);
     $this->select->expects($this->any())->method('from')->will($this->returnSelf());
     $this->select->expects($this->any())->method('where')->will($this->returnSelf());
     $this->select->expects($this->any())->method('joinLeft')->will($this->returnSelf());
     $this->connection->expects($this->any())->method('select')->will($this->returnValue($this->select));
     $connectionMock = $this->getMock('Magento\\Framework\\DB\\Adapter\\Pdo\\Mysql', [], [], '', false);
     $connectionMock->expects($this->any())->method('quoteInto')->will($this->returnValue('query'));
     $this->select->expects($this->any())->method('getConnection')->willReturn($connectionMock);
     $this->connection->expects($this->any())->method('insertOnDuplicate')->willReturnSelf();
     $this->connection->expects($this->any())->method('delete')->willReturnSelf();
     $this->connection->expects($this->any())->method('quoteInto')->willReturn('');
     $this->connection->expects($this->any())->method('fetchAll')->will($this->returnValue($entityAttributes));
     $this->resource = $this->getMock('\\Magento\\Framework\\App\\ResourceConnection', ['getConnection', 'getTableName'], [], '', false);
     $this->resource->expects($this->any())->method('getConnection')->will($this->returnValue($this->connection));
     $this->resource->expects($this->any())->method('getTableName')->will($this->returnValue('tableName'));
     $this->grouped = $this->objectManagerHelper->getObject('Magento\\GroupedImportExport\\Model\\Import\\Product\\Type\\Grouped', ['attrSetColFac' => $this->setCollectionFactory, 'prodAttrColFac' => $this->attrCollectionFactory, 'resource' => $this->resource, 'params' => $this->params, 'links' => $this->links]);
 }

 public function testAddUnsecureUrlsFilter()
 {
     $this->collection->expects($this->at(0))->method('_translateCondition')->with('endpoint', ['like' => 'http:%'])->will($this->returnValue('endpoint like \'http:%\''));
     $this->collection->expects($this->at(1))->method('_translateCondition')->with('identity_link_url', ['like' => 'http:%'])->will($this->returnValue('identity_link_url like \'http:%\''));
     $this->select->expects($this->once())->method('where')->with($this->equalTo('(endpoint like \'http:%\') OR (identity_link_url like \'http:%\')'), $this->equalTo(null), $this->equalTo(\Magento\Framework\DB\Select::TYPE_CONDITION));
     $this->collection->addUnsecureUrlsFilter();
 }

 /**
  * Render DISTINCT section
  *
  * @param Select $select
  * @param string $sql
  * @return string
  */
 public function render(Select $select, $sql = '')
 {
     if ($select->getPart(Select::DISTINCT)) {
         $sql .= ' ' . Select::SQL_DISTINCT . ' ';
     }
     return $sql;
 }

 public function testRender()
 {
     $sql = 'SELECT';
     $expectedResult = $sql . ' ' . Select::SQL_DISTINCT . ' ';
     $this->selectMock->expects($this->once())->method('getPart')->with(Select::DISTINCT)->willReturn(true);
     $this->assertEquals($expectedResult, $this->model->render($this->selectMock, $sql));
 }

 /**
  * Add JOINs to original select.
  *
  * @param \Magento\Framework\DB\Select $select
  * @return \Magento\Framework\DB\Select
  */
 public function modifySelect(\Magento\Framework\DB\Select $select)
 {
     /* aliases for tables ... */
     $tblEntity = 'e';
     // this is alias for 'catalog_product_entity' table
     $tblStockItem = $this->_resource->getTableName(self::TBL_STOCK_ITEM);
     $tblWrhsQty = $this->_resource->getTableName(self::TBL_WRHS_QTY);
     /* ... and fields */
     $fldStockItemProdId = StockItem::PRODUCT_ID;
     $fldStockItemId = StockItem::ITEM_ID;
     $fldEntityId = \Magento\Eav\Model\Entity::DEFAULT_ENTITY_ID_FIELD;
     $fldQty = self::FLD_QTY;
     $fldStockItemRef = Quantity::ATTR_STOCK_ITEM_REF;
     /* LEFT JOIN `cataloginventory_stock_item` */
     $on = "`{$tblStockItem}`.`{$fldStockItemProdId}`=`{$tblEntity}`.`{$fldEntityId}`";
     $fields = [];
     $select->joinLeft($tblStockItem, $on, $fields);
     /* LEFT JOIN `prxgt_wrhs_qty` */
     $on = "`{$tblWrhsQty}`.`{$fldStockItemRef}`=`{$tblStockItem}`.`{$fldStockItemId}`";
     $fields = [$fldQty => $this->getEquationQty()];
     $select->joinLeft($tblWrhsQty, $on, $fields);
     /* GROUP BY */
     $select->group("{$tblEntity}.{$fldEntityId}");
     return $select;
 }

 /**
  * @param array $columns
  * @param string $sql
  * @param string $expectedResult
  * @dataProvider renderDataProvider
  */
 public function testRender($columns, $sql, $expectedResult)
 {
     $mapValues = [['column', null, '`column`'], [['table', 'column'], null, '`table`.`column`'], [['table', 'column'], 'alias', '`table`.`column` AS `alias`']];
     $this->quoteMock->expects($this->any())->method('quoteColumnAs')->willReturnMap($mapValues);
     $this->selectMock->expects($this->exactly(2))->method('getPart')->with(Select::COLUMNS)->willReturn($columns);
     $this->assertEquals($expectedResult, $this->model->render($this->selectMock, $sql));
 }

 /**
  * Render FOR UPDATE section
  *
  * @param Select $select
  * @param string $sql
  * @return string
  * @throws \Zend_Db_Select_Exception
  */
 public function render(Select $select, $sql = '')
 {
     if ($select->getPart(Select::FOR_UPDATE)) {
         $sql .= ' ' . Select::SQL_FOR_UPDATE;
     }
     return $sql;
 }

 /**
  * Apply price rule price to price index table
  *
  * @param \Magento\Framework\DB\Select $select
  * @param array|string $indexTable
  * @param string $entityId
  * @param string $customerGroupId
  * @param string $websiteId
  * @param array $updateFields       the array of fields for compare with rule price and update
  * @param string $websiteDate
  * @return \Magento\CatalogRule\Model\ResourceModel\Rule\Product\Price
  */
 public function applyPriceRuleToIndexTable(\Magento\Framework\DB\Select $select, $indexTable, $entityId, $customerGroupId, $websiteId, $updateFields, $websiteDate)
 {
     if (empty($updateFields)) {
         return $this;
     }
     if (is_array($indexTable)) {
         foreach ($indexTable as $k => $v) {
             if (is_string($k)) {
                 $indexAlias = $k;
             } else {
                 $indexAlias = $v;
             }
             break;
         }
     } else {
         $indexAlias = $indexTable;
     }
     $select->join(['rp' => $this->getMainTable()], "rp.rule_date = {$websiteDate}", [])->where("rp.product_id = {$entityId} AND rp.website_id = {$websiteId} AND rp.customer_group_id = {$customerGroupId}");
     foreach ($updateFields as $priceField) {
         $priceCond = $this->getConnection()->quoteIdentifier([$indexAlias, $priceField]);
         $priceExpr = $this->getConnection()->getCheckSql("rp.rule_price < {$priceCond}", 'rp.rule_price', $priceCond);
         $select->columns([$priceField => $priceExpr]);
     }
     $query = $select->crossUpdateFromSelect($indexTable);
     $this->getConnection()->query($query);
     return $this;
 }

 protected function setUp()
 {
     parent::setUp();
     $this->entityModel = $this->getMock('Magento\\CatalogImportExport\\Model\\Import\\Product', ['getErrorAggregator', 'getBehavior', 'getNewSku', 'getNextBunch', 'isRowAllowedToImport', 'getRowScope', 'getConnection'], [], '', false);
     $this->entityModel->method('getErrorAggregator')->willReturn($this->getErrorAggregatorObject());
     $this->connection = $this->getMock('Magento\\Framework\\DB\\Adapter\\Pdo\\Mysql', ['select', 'fetchAll', 'fetchPairs', 'joinLeft', 'insertOnDuplicate', 'delete', 'quoteInto', 'fetchAssoc'], [], '', false);
     $this->select = $this->getMock('Magento\\Framework\\DB\\Select', [], [], '', false);
     $this->select->expects($this->any())->method('from')->will($this->returnSelf());
     $this->select->expects($this->any())->method('where')->will($this->returnSelf());
     $this->select->expects($this->any())->method('joinLeft')->will($this->returnSelf());
     $this->select->expects($this->any())->method('getConnection')->willReturn($this->connection);
     $this->connection->expects($this->any())->method('select')->will($this->returnValue($this->select));
     $this->initFetchAllCalls();
     $this->connection->expects($this->any())->method('insertOnDuplicate')->willReturnSelf();
     $this->connection->expects($this->any())->method('delete')->willReturnSelf();
     $this->connection->expects($this->any())->method('quoteInto')->willReturn('');
     $this->resource = $this->getMock('Magento\\Framework\\App\\ResourceConnection', ['getConnection', 'getTableName'], [], '', false);
     $this->resource->expects($this->any())->method('getConnection')->will($this->returnValue($this->connection));
     $this->resource->expects($this->any())->method('getTableName')->will($this->returnValue('tableName'));
     $this->attrSetColFac = $this->getMock('Magento\\Eav\\Model\\ResourceModel\\Entity\\Attribute\\Set\\CollectionFactory', ['create'], [], '', false);
     $this->setCollection = $this->getMock('Magento\\Eav\\Model\\ResourceModel\\Entity\\Attribute\\Set\\Collection', ['setEntityTypeFilter'], [], '', false);
     $this->attrSetColFac->expects($this->any())->method('create')->will($this->returnValue($this->setCollection));
     $this->setCollection->expects($this->any())->method('setEntityTypeFilter')->will($this->returnValue([]));
     $this->prodAttrColFac = $this->getMock('Magento\\Catalog\\Model\\ResourceModel\\Product\\Attribute\\CollectionFactory', ['create'], [], '', false);
     $attrCollection = $this->getMock('\\Magento\\Catalog\\Model\\ResourceModel\\Product\\Attribute\\Collection', [], [], '', false);
     $attrCollection->expects($this->any())->method('addFieldToFilter')->willReturn([]);
     $this->prodAttrColFac->expects($this->any())->method('create')->will($this->returnValue($attrCollection));
     $this->params = [0 => $this->entityModel, 1 => 'bundle'];
     $this->bundle = $this->objectManagerHelper->getObject('Magento\\BundleImportExport\\Model\\Import\\Product\\Type\\Bundle', ['attrSetColFac' => $this->attrSetColFac, 'prodAttrColFac' => $this->prodAttrColFac, 'resource' => $this->resource, 'params' => $this->params]);
 }

 public function testRender()
 {
     $sql = 'SELECT';
     $expectedResult = $sql . ' ' . Select::SQL_HAVING . ' having1 having2';
     $mapValues = [[Select::FROM, true], [Select::HAVING, ['having1', 'having2']]];
     $this->selectMock->expects($this->any())->method('getPart')->willReturnMap($mapValues);
     $this->assertEquals($expectedResult, $this->model->render($this->selectMock, $sql));
 }

 protected function setUp()
 {
     $this->entityModel = $this->getMock('\\Magento\\CatalogImportExport\\Model\\Import\\Product', [], [], '', false);
     $attrSetColFactory = $this->getMock('\\Magento\\Eav\\Model\\ResourceModel\\Entity\\Attribute\\Set\\CollectionFactory', ['create'], [], '', false);
     $attrSetCollection = $this->getMock('\\Magento\\Eav\\Model\\ResourceModel\\Entity\\Attribute\\Set\\Collection', [], [], '', false);
     $attrColFactory = $this->getMock('\\Magento\\Catalog\\Model\\ResourceModel\\Product\\Attribute\\CollectionFactory', ['create'], [], '', false);
     $attributeSet = $this->getMock('\\Magento\\Eav\\Model\\Entity\\Attribute\\Set', [], [], '', false);
     $attrCollection = $this->getMock('\\Magento\\Eav\\Model\\ResourceModel\\Entity\\Attribute\\Collection', ['addFieldToFilter'], [], '', false);
     $attribute = $this->getMock('\\Magento\\Eav\\Model\\Entity\\Attribute', ['getAttributeCode', 'getId', 'getIsVisible', 'getIsGlobal', 'getIsRequired', 'getIsUnique', 'getFrontendLabel', 'isStatic', 'getApplyTo', 'getDefaultValue', 'usesSource', 'getFrontendInput'], [], '', false);
     $attribute->expects($this->any())->method('getIsVisible')->willReturn(true);
     $attribute->expects($this->any())->method('getIsGlobal')->willReturn(true);
     $attribute->expects($this->any())->method('getIsRequired')->willReturn(true);
     $attribute->expects($this->any())->method('getIsUnique')->willReturn(true);
     $attribute->expects($this->any())->method('getFrontendLabel')->willReturn('frontend_label');
     $attribute->expects($this->any())->method('getApplyTo')->willReturn(['simple']);
     $attribute->expects($this->any())->method('getDefaultValue')->willReturn('default_value');
     $attribute->expects($this->any())->method('usesSource')->willReturn(true);
     $entityAttributes = [['attribute_id' => 'attribute_id', 'attribute_set_name' => 'attributeSetName'], ['attribute_id' => 'boolean_attribute', 'attribute_set_name' => 'attributeSetName']];
     $attribute1 = clone $attribute;
     $attribute2 = clone $attribute;
     $attribute1->expects($this->any())->method('getId')->willReturn('1');
     $attribute1->expects($this->any())->method('getAttributeCode')->willReturn('attr_code');
     $attribute1->expects($this->any())->method('getFrontendInput')->willReturn('multiselect');
     $attribute1->expects($this->any())->method('isStatic')->willReturn(true);
     $attribute2->expects($this->any())->method('getId')->willReturn('2');
     $attribute2->expects($this->any())->method('getAttributeCode')->willReturn('boolean_attribute');
     $attribute2->expects($this->any())->method('getFrontendInput')->willReturn('boolean');
     $attribute2->expects($this->any())->method('isStatic')->willReturn(false);
     $this->entityModel->expects($this->any())->method('getEntityTypeId')->willReturn(3);
     $this->entityModel->expects($this->any())->method('getAttributeOptions')->willReturnOnConsecutiveCalls(['option1', 'option2'], ['yes' => 1, 'no' => 0]);
     $attrSetColFactory->expects($this->any())->method('create')->willReturn($attrSetCollection);
     $attrSetCollection->expects($this->any())->method('setEntityTypeFilter')->willReturn([$attributeSet]);
     $attrColFactory->expects($this->any())->method('create')->willReturn($attrCollection);
     $attrCollection->expects($this->any())->method('setAttributeSetFilter')->willReturn([$attribute1, $attribute2]);
     $attributeSet->expects($this->any())->method('getId')->willReturn(1);
     $attributeSet->expects($this->any())->method('getAttributeSetName')->willReturn('attribute_set_name');
     $attrCollection->expects($this->any())->method('addFieldToFilter')->with('main_table.attribute_id', ['in' => ['attribute_id', 'boolean_attribute']])->willReturn([$attribute1, $attribute2]);
     $this->connection = $this->getMock('Magento\\Framework\\DB\\Adapter\\Pdo\\Mysql', ['select', 'fetchAll', 'fetchPairs', 'joinLeft', 'insertOnDuplicate', 'delete', 'quoteInto'], [], '', false);
     $this->select = $this->getMock('Magento\\Framework\\DB\\Select', ['from', 'where', 'joinLeft', 'getConnection'], [], '', false);
     $this->select->expects($this->any())->method('from')->will($this->returnSelf());
     $this->select->expects($this->any())->method('where')->will($this->returnSelf());
     $this->select->expects($this->any())->method('joinLeft')->will($this->returnSelf());
     $this->connection->expects($this->any())->method('select')->will($this->returnValue($this->select));
     $connection = $this->getMock('Magento\\Framework\\DB\\Adapter\\Pdo\\Mysql', [], [], '', false);
     $connection->expects($this->any())->method('quoteInto')->will($this->returnValue('query'));
     $this->select->expects($this->any())->method('getConnection')->willReturn($connection);
     $this->connection->expects($this->any())->method('insertOnDuplicate')->willReturnSelf();
     $this->connection->expects($this->any())->method('delete')->willReturnSelf();
     $this->connection->expects($this->any())->method('quoteInto')->willReturn('');
     $this->connection->expects($this->any())->method('fetchAll')->will($this->returnValue($entityAttributes));
     $this->resource = $this->getMock('\\Magento\\Framework\\App\\ResourceConnection', ['getConnection', 'getTableName'], [], '', false);
     $this->resource->expects($this->any())->method('getConnection')->will($this->returnValue($this->connection));
     $this->resource->expects($this->any())->method('getTableName')->will($this->returnValue('tableName'));
     $this->objectManagerHelper = new ObjectManagerHelper($this);
     $this->simpleType = $this->objectManagerHelper->getObject('Magento\\CatalogImportExport\\Model\\Import\\Product\\Type\\Simple', ['attrSetColFac' => $attrSetColFactory, 'prodAttrColFac' => $attrColFactory, 'params' => [$this->entityModel, 'simple'], 'resource' => $this->resource]);
     $this->abstractType = $this->getMockBuilder('\\Magento\\CatalogImportExport\\Model\\Import\\Product\\Type\\AbstractType')->disableOriginalConstructor()->getMockForAbstractClass();
 }

 public function testRender()
 {
     $sql = 'SELECT';
     $expectedResult = $sql . ' ' . Select::SQL_GROUP_BY . ' group1' . ",\n\t" . 'group2';
     $mapValues = [[Select::FROM, true], [Select::GROUP, ['group1', 'group2']]];
     $this->selectMock->expects($this->exactly(3))->method('getPart')->willReturnMap($mapValues);
     $this->quoteMock->expects($this->exactly(2))->method('quoteIdentifier')->willReturnArgument(0);
     $this->assertEquals($expectedResult, $this->model->render($this->selectMock, $sql));
 }