PHP Select::joinLeft方法代码示例

 /**
  * 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;
 }

 /**
  * Add attribute to filter
  *
  * @param int $storeId
  * @param string $attributeCode
  * @param mixed $value
  * @param string $type
  * @return \Magento\Framework\DB\Select|bool
  */
 protected function _addFilter($storeId, $attributeCode, $value, $type = '=')
 {
     if (!$this->_select instanceof \Magento\Framework\DB\Select) {
         return false;
     }
     if (!isset($this->_attributesCache[$attributeCode])) {
         $attribute = $this->_categoryResource->getAttribute($attributeCode);
         $this->_attributesCache[$attributeCode] = ['entity_type_id' => $attribute->getEntityTypeId(), 'attribute_id' => $attribute->getId(), 'table' => $attribute->getBackend()->getTable(), 'is_global' => $attribute->getIsGlobal(), 'backend_type' => $attribute->getBackendType()];
     }
     $attribute = $this->_attributesCache[$attributeCode];
     switch ($type) {
         case '=':
             $conditionRule = '=?';
             break;
         case 'in':
             $conditionRule = ' IN(?)';
             break;
         default:
             return false;
             break;
     }
     if ($attribute['backend_type'] == 'static') {
         $this->_select->where('e.' . $attributeCode . $conditionRule, $value);
     } else {
         $this->_select->join(['t1_' . $attributeCode => $attribute['table']], 'e.entity_id = t1_' . $attributeCode . '.entity_id AND t1_' . $attributeCode . '.store_id = 0', [])->where('t1_' . $attributeCode . '.attribute_id=?', $attribute['attribute_id']);
         if ($attribute['is_global']) {
             $this->_select->where('t1_' . $attributeCode . '.value' . $conditionRule, $value);
         } else {
             $ifCase = $this->getConnection()->getCheckSql('t2_' . $attributeCode . '.value_id > 0', 't2_' . $attributeCode . '.value', 't1_' . $attributeCode . '.value');
             $this->_select->joinLeft(['t2_' . $attributeCode => $attribute['table']], $this->getConnection()->quoteInto('t1_' . $attributeCode . '.entity_id = t2_' . $attributeCode . '.entity_id AND t1_' . $attributeCode . '.attribute_id = t2_' . $attributeCode . '.attribute_id AND t2_' . $attributeCode . '.store_id=?', $storeId), [])->where('(' . $ifCase . ')' . $conditionRule, $value);
         }
     }
     return $this->_select;
 }

 /**
  * Join attribute by code
  *
  * @param int $storeId
  * @param string $attributeCode
  * @return void
  */
 protected function _joinAttribute($storeId, $attributeCode)
 {
     $connection = $this->getConnection();
     $attribute = $this->_getAttribute($attributeCode);
     $this->_select->joinLeft(['t1_' . $attributeCode => $attribute['table']], 'e.entity_id = t1_' . $attributeCode . '.entity_id AND ' . $connection->quoteInto(' t1_' . $attributeCode . '.store_id = ?', \Magento\Store\Model\Store::DEFAULT_STORE_ID) . $connection->quoteInto(' AND t1_' . $attributeCode . '.attribute_id = ?', $attribute['attribute_id']), []);
     if (!$attribute['is_global']) {
         $this->_select->joinLeft(['t2_' . $attributeCode => $attribute['table']], $this->getConnection()->quoteInto('t1_' . $attributeCode . '.entity_id = t2_' . $attributeCode . '.entity_id AND t1_' . $attributeCode . '.attribute_id = t2_' . $attributeCode . '.attribute_id AND t2_' . $attributeCode . '.store_id = ?', $storeId), []);
     }
 }

 /**
  * Join attribute by code
  *
  * @param int $storeId
  * @param string $attributeCode
  * @return void
  */
 protected function _joinAttribute($storeId, $attributeCode)
 {
     $connection = $this->getConnection();
     $attribute = $this->_getAttribute($attributeCode);
     $linkField = $this->_productResource->getLinkField();
     $attrTableAlias = 't1_' . $attributeCode;
     $this->_select->joinLeft([$attrTableAlias => $attribute['table']], "e.{$linkField} = {$attrTableAlias}.{$linkField}" . ' AND ' . $connection->quoteInto($attrTableAlias . '.store_id = ?', Store::DEFAULT_STORE_ID) . ' AND ' . $connection->quoteInto($attrTableAlias . '.attribute_id = ?', $attribute['attribute_id']), []);
     if (!$attribute['is_global']) {
         $attrTableAlias2 = 't2_' . $attributeCode;
         $this->_select->joinLeft(['t2_' . $attributeCode => $attribute['table']], "{$attrTableAlias}.{$linkField} = {$attrTableAlias2}.{$linkField}" . ' AND ' . $attrTableAlias . '.attribute_id = ' . $attrTableAlias2 . '.attribute_id' . ' AND ' . $connection->quoteInto($attrTableAlias2 . '.store_id = ?', $storeId), []);
     }
 }

 /**
  * @param Select $select
  * @param RequestInterface $request
  * @return Select
  */
 public function addTables(Select $select, RequestInterface $request)
 {
     $mappedTables = [];
     $filters = $this->getFilters($request->getQuery());
     foreach ($filters as $filter) {
         list($alias, $table, $mapOn, $mappedFields) = $this->getMappingData($filter);
         if (!array_key_exists($alias, $mappedTables)) {
             $select->joinLeft([$alias => $table], $mapOn, $mappedFields);
             $mappedTables[$alias] = $table;
         }
     }
     return $select;
 }

 private function _populateSelect(\Magento\Framework\DB\Select $select)
 {
     /* aliases and tables */
     $asAcc = AggRepo::AS_ACCOUNT;
     $asDwnl = AggRepo::AS_DOWNLINE;
     //
     $tblDwnl = [$asDwnl => $this->_resource->getTableName(ECustomer::ENTITY_NAME)];
     /* LEFT JOIN prxgt_dwnl_customer */
     $cond = $asDwnl . '.' . ECustomer::ATTR_CUSTOMER_ID . '=' . $asAcc . '.' . EAccount::ATTR_CUST_ID;
     $cols = [AggEntity::AS_REF => ECustomer::ATTR_HUMAN_REF];
     $select->joinLeft($tblDwnl, $cond, $cols);
     return $select;
 }

 /**
  * Add attribute join condition to select and return \Zend_Db_Expr
  * attribute value definition
  * If $condition is not empty apply limitation for select
  *
  * @param \Magento\Framework\DB\Select $select
  * @param string $attrCode              the attribute code
  * @param string|\Zend_Db_Expr $entity   the entity field or expression for condition
  * @param string|\Zend_Db_Expr $store    the store field or expression for condition
  * @param \Zend_Db_Expr $condition       the limitation condition
  * @param bool $required                if required or has condition used INNER join, else - LEFT
  * @return \Zend_Db_Expr                 the attribute value expression
  */
 protected function _addAttributeToSelect($select, $attrCode, $entity, $store, $condition = null, $required = false)
 {
     $attribute = $this->_getAttribute($attrCode);
     $attributeId = $attribute->getAttributeId();
     $attributeTable = $attribute->getBackend()->getTable();
     $connection = $this->getConnection();
     $joinType = $condition !== null || $required ? 'join' : 'joinLeft';
     if ($attribute->isScopeGlobal()) {
         $alias = 'ta_' . $attrCode;
         $select->{$joinType}([$alias => $attributeTable], "{$alias}.entity_id = {$entity} AND {$alias}.attribute_id = {$attributeId}" . " AND {$alias}.store_id = 0", []);
         $expression = new \Zend_Db_Expr("{$alias}.value");
     } else {
         $dAlias = 'tad_' . $attrCode;
         $sAlias = 'tas_' . $attrCode;
         $select->{$joinType}([$dAlias => $attributeTable], "{$dAlias}.entity_id = {$entity} AND {$dAlias}.attribute_id = {$attributeId}" . " AND {$dAlias}.store_id = 0", []);
         $select->joinLeft([$sAlias => $attributeTable], "{$sAlias}.entity_id = {$entity} AND {$sAlias}.attribute_id = {$attributeId}" . " AND {$sAlias}.store_id = {$store}", []);
         $expression = $connection->getCheckSql($connection->getIfNullSql("{$sAlias}.value_id", -1) . ' > 0', "{$sAlias}.value", "{$dAlias}.value");
     }
     if ($condition !== null) {
         $select->where("{$expression}{$condition}");
     }
     return $expression;
 }

 /**
  * Add stock status to prepare index select
  *
  * @param \Magento\Framework\DB\Select $select
  * @param \Magento\Store\Model\Website $website
  * @return Status
  */
 public function addStockStatusToSelect(\Magento\Framework\DB\Select $select, \Magento\Store\Model\Website $website)
 {
     $websiteId = $website->getId();
     $select->joinLeft(['stock_status' => $this->getMainTable()], 'e.entity_id = stock_status.product_id AND stock_status.website_id=' . $websiteId, ['salable' => 'stock_status.stock_status']);
     return $this;
 }

 /**
  * Add variable store and default value to select
  *
  * @param \Magento\Framework\DB\Select $select
  * @param integer $storeId
  * @return \Magento\Variable\Model\ResourceModel\Variable
  */
 protected function _addValueToSelect(\Magento\Framework\DB\Select $select, $storeId = \Magento\Store\Model\Store::DEFAULT_STORE_ID)
 {
     $connection = $this->getConnection();
     $ifNullPlainValue = $connection->getCheckSql('store.plain_value IS NULL', 'def.plain_value', 'store.plain_value');
     $ifNullHtmlValue = $connection->getCheckSql('store.html_value IS NULL', 'def.html_value', 'store.html_value');
     $select->joinLeft(['def' => $this->getTable('variable_value')], 'def.variable_id = ' . $this->getMainTable() . '.variable_id AND def.store_id = 0', [])->joinLeft(['store' => $this->getTable('variable_value')], 'store.variable_id = def.variable_id AND store.store_id = ' . $connection->quote($storeId), [])->columns(['plain_value' => $ifNullPlainValue, 'html_value' => $ifNullHtmlValue, 'store_plain_value' => 'store.plain_value', 'store_html_value' => 'store.html_value']);
     return $this;
 }

 /**
  * Joins attribute value to a select
  *
  * @param Select $select
  * @param object $attribute
  * @param string $tableAlias
  * @return $this
  */
 public function joinAttribute(Select $select, $attribute, $tableAlias = 'main')
 {
     $defaultAlias = sprintf('attribute_%s_default', $attribute->code);
     $scopeAlias = sprintf('attribute_%s_scope', $attribute->code);
     $attributeTable = $this->getTable(['entity', $attribute->type]);
     if (1 - $attribute->required > 0.01) {
         $select->joinLeft([$defaultAlias => $attributeTable], $this->andCondition([sprintf('%s.entity_id = %s.entity_id', $defaultAlias, $tableAlias), sprintf('%s.attribute_id = ?', $defaultAlias) => $attribute->id, sprintf('%s.scope_id = ?', $defaultAlias) => 0]), []);
     } else {
         $select->join([$defaultAlias => $attributeTable], $this->andCondition([sprintf('%s.entity_id = %s.entity_id', $defaultAlias, $tableAlias), sprintf('%s.attribute_id = ?', $defaultAlias) => $attribute->id, sprintf('%s.scope_id = ?', $defaultAlias) => 0]), []);
     }
     if ($attribute->scope && $this->scopeCode) {
         $select->joinLeft([$scopeAlias => $attributeTable], $this->andCondition([sprintf('%s.entity_id = %s.entity_id', $defaultAlias, $scopeAlias), sprintf('%s.attribute_id = %s.attribute_id', $defaultAlias, $scopeAlias), sprintf('%s.scope_id = ?', $scopeAlias) => $this->scope->getId($this->scopeCode)]), []);
         $valueExpression = sprintf('IF(%2$s.value_id IS NOT NULL, %2$s.value, %1$s.value)', $defaultAlias, $scopeAlias);
     } else {
         $valueExpression = sprintf('%s.value', $defaultAlias);
     }
     $select->columns([$attribute->code => $valueExpression]);
     return $valueExpression;
 }