PHP Collection::getSelect方法代码示例

 /**
  * Premare block data
  * @return $this
  */
 protected function _prepareCollection()
 {
     $post = $this->getPost();
     $this->_itemCollection = $post->getRelatedProducts()->addAttributeToSelect('required_options');
     if ($this->_moduleManager->isEnabled('Magento_Checkout')) {
         $this->_addProductAttributesAndPrices($this->_itemCollection);
     }
     $this->_itemCollection->setVisibility($this->_catalogProductVisibility->getVisibleInCatalogIds());
     $this->_itemCollection->setPageSize((int) $this->_scopeConfig->getValue('mfblog/post_view/related_products/number_of_products', \Magento\Store\Model\ScopeInterface::SCOPE_STORE));
     $this->_itemCollection->getSelect()->order('rl.position', 'ASC');
     $this->_itemCollection->load();
     foreach ($this->_itemCollection as $product) {
         $product->setDoNotUseCategoryId(true);
     }
     return $this;
 }

 /**
  * Make collection not to load products that are in specified quote
  *
  * @param \Magento\Catalog\Model\ResourceModel\Product\Collection $collection
  * @param int $quoteId
  * @return $this
  */
 public function addExcludeProductFilter($collection, $quoteId)
 {
     $connection = $this->getConnection();
     $exclusionSelect = $connection->select()->from($this->getTable('quote_item'), ['product_id'])->where('quote_id = ?', $quoteId);
     $condition = $connection->prepareSqlCondition('e.entity_id', ['nin' => $exclusionSelect]);
     $collection->getSelect()->where($condition);
     return $this;
 }

 /**
  * Collect validated attributes for Product Collection
  *
  * @param \Magento\Catalog\Model\ResourceModel\Product\Collection $productCollection
  * @return $this
  */
 public function collectValidatedAttributes($productCollection)
 {
     $alias = array_keys($productCollection->getSelect()->getPart('from'))[0];
     foreach ($this->getConditions() as $condition) {
         $condition->setData('attribute', $alias . '.' . $condition->getData('attribute'));
         $condition->addToCollection($productCollection);
     }
     return $this;
 }

 /**
  * Separate query for product and order data
  *
  * @param array $productIds
  * @return array
  * @throws \Magento\Framework\Exception\LocalizedException
  */
 protected function getProductData(array $productIds)
 {
     $productConnection = $this->productResource->getConnection();
     $productAttrName = $this->productResource->getAttribute('name');
     $productAttrNameId = (int) $productAttrName->getAttributeId();
     $productAttrPrice = $this->productResource->getAttribute('price');
     $productAttrPriceId = (int) $productAttrPrice->getAttributeId();
     $select = clone $this->productResource->getSelect();
     $select->reset();
     $select->from(['main_table' => $this->getTable('catalog_product_entity')])->useStraightJoin(true)->joinInner(['product_name' => $productAttrName->getBackend()->getTable()], 'product_name.entity_id = main_table.entity_id' . ' AND product_name.attribute_id = ' . $productAttrNameId . ' AND product_name.store_id = ' . \Magento\Store\Model\Store::DEFAULT_STORE_ID, ['name' => 'product_name.value'])->joinInner(['product_price' => $productAttrPrice->getBackend()->getTable()], "product_price.entity_id = main_table.entity_id AND product_price.attribute_id = {$productAttrPriceId}", ['price' => new \Zend_Db_Expr('product_price.value')])->where('main_table.entity_id IN (?)', $productIds);
     $productData = $productConnection->fetchAssoc($select);
     return $productData;
 }

 /**
  * Replace default stock id in the where clause by stock id corresponded with store id.
  *
  * @param \Magento\CatalogInventory\Model\ResourceModel\Stock\Status $subject
  * @param \Magento\Catalog\Model\ResourceModel\Product\Collection $result
  * @return \Magento\Catalog\Model\ResourceModel\Product\Collection
  */
 public function afterAddStockDataToCollection(\Magento\CatalogInventory\Model\ResourceModel\Stock\Status $subject, \Magento\Catalog\Model\ResourceModel\Product\Collection $result)
 {
     /** @var \Magento\Framework\Db\Select $select */
     $select = $result->getSelect();
     $from = $select->getPart('from');
     $join = $from['stock_status_index'];
     $cond = $join['joinCondition'];
     $stockId = $this->_manStock->getCurrentStockId();
     $fixed = str_replace('.stock_id = 1', '.stock_id = ' . $stockId, $cond);
     $join['joinCondition'] = $fixed;
     $from['stock_status_index'] = $join;
     $select->setPart('from', $from);
     return $result;
 }

 /**
  * Replace WHERE-filtering by HAVING-filtering.
  *
  * @param \Magento\CatalogInventory\Ui\DataProvider\Product\AddQuantityFilterToCollection $subject
  * @param \Closure $proceed
  * @param \Magento\Catalog\Model\ResourceModel\Product\Collection $collection
  * @param $field
  * @param null $condition
  */
 public function aroundAddFilter(\Magento\CatalogInventory\Ui\DataProvider\Product\AddQuantityFilterToCollection $subject, \Closure $proceed, \Magento\Catalog\Model\ResourceModel\Product\Collection $collection, $field, $condition = null)
 {
     /* skip identical conditions () */
     $regKey = print_r($condition, true);
     if (!isset($this->_regCond[$regKey])) {
         $conn = $collection->getConnection();
         $select = $collection->getSelect();
         $equation = $this->_repoModifierProductGFrid->getEquationQty();
         $prepared = $conn->prepareSqlCondition($equation, $condition);
         $select->having($prepared);
         $this->_regCond[$regKey] = true;
     }
     return;
 }

 /**
  * Add only is in stock products filter to product collection
  *
  * @param \Magento\Catalog\Model\ResourceModel\Product\Collection $collection
  * @return $this
  */
 public function addIsInStockFilterToCollection($collection)
 {
     $websiteId = $this->_storeManager->getStore($collection->getStoreId())->getWebsiteId();
     $joinCondition = $this->getConnection()->quoteInto('e.entity_id = stock_status_index.product_id' . ' AND stock_status_index.website_id = ?', $websiteId);
     $joinCondition .= $this->getConnection()->quoteInto(' AND stock_status_index.stock_id = ?', Stock::DEFAULT_STOCK_ID);
     $collection->getSelect()->join(['stock_status_index' => $this->getMainTable()], $joinCondition, [])->where('stock_status_index.stock_status=?', Stock\Status::STATUS_IN_STOCK);
     return $this;
 }

 /**
  * @param \Magento\Catalog\Model\ResourceModel\Eav\Attribute $attribute
  * @param \Magento\Catalog\Model\ResourceModel\Product\Collection $collection
  * @return $this
  */
 protected function addGlobalAttribute(\Magento\Catalog\Model\ResourceModel\Eav\Attribute $attribute, \Magento\Catalog\Model\ResourceModel\Product\Collection $collection)
 {
     $storeId = $this->storeManager->getStore()->getId();
     switch ($attribute->getBackendType()) {
         case 'decimal':
         case 'datetime':
         case 'int':
             $alias = 'at_' . $attribute->getAttributeCode();
             $collection->addAttributeToSelect($attribute->getAttributeCode(), 'inner');
             break;
         default:
             $alias = 'at_' . md5($this->getId()) . $attribute->getAttributeCode();
             $collection->getSelect()->join([$alias => $collection->getTable('catalog_product_index_eav')], "({$alias}.entity_id = e.entity_id) AND ({$alias}.store_id = {$storeId})" . " AND ({$alias}.attribute_id = {$attribute->getId()})", []);
     }
     $this->joinedAttributes[$attribute->getAttributeCode()] = $alias . '.value';
     return $this;
 }

 /**
  * @param Subject $subject
  * @param \Closure $proceed
  * @param \Magento\Catalog\Model\ResourceModel\Product\Collection $collection
  * @param $field
  * @param null $alias
  * @throws \Magento\Framework\Exception\LocalizedException
  */
 public function aroundAddField(Subject $subject, \Closure $proceed, \Magento\Catalog\Model\ResourceModel\Product\Collection $collection)
 {
     $select = $collection->getSelect();
     $this->_queryModGrid->modifySelect($select);
     return;
 }

 /**
  * Add low stock filter to product collection
  *
  * @param \Magento\Catalog\Model\ResourceModel\Product\Collection $collection
  * @param array $fields
  * @return $this
  */
 public function addLowStockFilter(\Magento\Catalog\Model\ResourceModel\Product\Collection $collection, $fields)
 {
     $this->_initConfig();
     $connection = $collection->getSelect()->getConnection();
     $qtyIf = $connection->getCheckSql('invtr.use_config_notify_stock_qty > 0', $this->_configNotifyStockQty, 'invtr.notify_stock_qty');
     $conditions = [[$connection->prepareSqlCondition('invtr.use_config_manage_stock', 1), $connection->prepareSqlCondition($this->_isConfigManageStock, 1), $connection->prepareSqlCondition('invtr.qty', ['lt' => $qtyIf])], [$connection->prepareSqlCondition('invtr.use_config_manage_stock', 0), $connection->prepareSqlCondition('invtr.manage_stock', 1)]];
     $where = [];
     foreach ($conditions as $k => $part) {
         $where[$k] = join(' ' . \Magento\Framework\DB\Select::SQL_AND . ' ', $part);
     }
     $where = $connection->prepareSqlCondition('invtr.low_stock_date', ['notnull' => true]) . ' ' . \Magento\Framework\DB\Select::SQL_AND . ' ((' . join(') ' . \Magento\Framework\DB\Select::SQL_OR . ' (', $where) . '))';
     $collection->joinTable(['invtr' => 'cataloginventory_stock_item'], 'product_id = entity_id', $fields, $where);
     return $this;
 }

 /**
  * @param ProductCollection $productCollection
  * @param integer $parentId
  * @return void
  */
 protected function addFilterByParent(ProductCollection $productCollection, $parentId)
 {
     $tableProductRelation = $productCollection->getTable('catalog_product_relation');
     $productCollection->getSelect()->join(['pr' => $tableProductRelation], 'e.entity_id = pr.child_id')->where('pr.parent_id = ?', $parentId);
 }