/** * Render sql select conditions * * @return $this */ protected function _renderFilters() { if ($this->_isFiltersRendered) { return $this; } $this->_renderFiltersBefore(); foreach ($this->_filters as $filter) { switch ($filter['type']) { case 'or': $condition = $this->_conn->quoteInto($filter['field'] . '=?', $filter['value']); $this->_select->orWhere($condition); break; case 'string': $this->_select->where($filter['value']); break; case 'public': $field = $this->_getMappedField($filter['field']); $condition = $filter['value']; $this->_select->where($this->_getConditionSql($field, $condition), null, Select::TYPE_CONDITION); break; default: $condition = $this->_conn->quoteInto($filter['field'] . '=?', $filter['value']); $this->_select->where($condition); } } $this->_isFiltersRendered = true; return $this; }
/** * Update relation products * * @param int $storeId * @param int|array $productIds Update child product(s) only * @return \Magento\Catalog\Model\Indexer\Product\Flat\AbstractAction * @SuppressWarnings(PHPMD.CyclomaticComplexity) */ protected function _updateRelationProducts($storeId, $productIds = null) { if (!$this->_productIndexerHelper->isAddChildData() || !$this->_isFlatTableExists($storeId)) { 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()) { $columns = $this->_productIndexerHelper->getFlatColumns(); $fieldList = array_keys($columns); unset($columns['entity_id']); unset($columns['child_id']); unset($columns['is_child']); /** @var $select \Magento\Framework\DB\Select */ $select = $this->_connection->select()->from(['t' => $this->_productIndexerHelper->getTable($relation->getTable())], [$relation->getParentFieldName(), $relation->getChildFieldName(), new \Zend_Db_Expr('1')])->join(['e' => $this->_productIndexerHelper->getFlatTableName($storeId)], "e.entity_id = t.{$relation->getChildFieldName()}", array_keys($columns)); if ($relation->getWhere() !== null) { $select->where($relation->getWhere()); } if ($productIds !== null) { $cond = [$this->_connection->quoteInto("{$relation->getChildFieldName()} IN(?)", $productIds), $this->_connection->quoteInto("{$relation->getParentFieldName()} IN(?)", $productIds)]; $select->where(implode(' OR ', $cond)); } $sql = $select->insertFromSelect($this->_productIndexerHelper->getFlatTableName($storeId), $fieldList); $this->_connection->query($sql); } } return $this; }
/** * 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); } } } }
/** * Load ensured nodes * * @param object $category * @param Node $rootNode * @return void */ public function loadEnsuredNodes($category, $rootNode) { $pathIds = $category->getPathIds(); $rootNodeId = $rootNode->getId(); $rootNodePath = $rootNode->getData($this->_pathField); $select = clone $this->_select; $select->order($this->_table . '.' . $this->_orderField . ' ASC'); if ($pathIds) { $condition = $this->_conn->quoteInto("{$this->_table}.{$this->_idField} in (?)", $pathIds); $select->where($condition); } $arrNodes = $this->_conn->fetchAll($select); if ($arrNodes) { $childrenItems = []; foreach ($arrNodes as $nodeInfo) { $nodeId = $nodeInfo[$this->_idField]; if ($nodeId <= $rootNodeId) { continue; } $pathToParent = explode('/', $nodeInfo[$this->_pathField]); array_pop($pathToParent); $pathToParent = implode('/', $pathToParent); $childrenItems[$pathToParent][] = $nodeInfo; } $this->_addChildNodes($childrenItems, $rootNodePath, $rootNode, true); } }
/** * {@inheritdoc} */ public function getAggregation(BucketInterface $bucket, array $dimensions, $range, \Magento\Framework\Search\Dynamic\EntityStorage $entityStorage) { $select = $this->dataProvider->getDataSet($bucket, $dimensions, $entityStorage->getSource()); $column = $select->getPart(Select::COLUMNS)[0]; $select->reset(Select::COLUMNS); $rangeExpr = new \Zend_Db_Expr($this->connection->quoteInto('(FLOOR(' . $column[1] . ' / ? ) + 1)', $range)); $select->columns(['range' => $rangeExpr])->columns(['metrix' => 'COUNT(*)'])->group('range')->order('range'); $result = $this->connection->fetchPairs($select); return $result; }
/** * Delete options and selections. * * @param array $productIds * * @return \Magento\CatalogImportExport\Model\Import\Product\Type\AbstractType */ protected function deleteOptionsAndSelections($productIds) { $optionTable = $this->_resource->getTableName('catalog_product_bundle_option'); $optionValueTable = $this->_resource->getTableName('catalog_product_bundle_option_value'); $valuesIds = $this->connection->fetchAssoc($this->connection->select()->from(['bov' => $optionValueTable], ['value_id'])->joinLeft(['bo' => $optionTable], 'bo.option_id = bov.option_id', ['option_id'])->where('parent_id IN (?)', $productIds)); $this->connection->delete($optionTable, $this->connection->quoteInto('value_id IN (?)', array_keys($valuesIds))); $productIdsInWhere = $this->connection->quoteInto('parent_id IN (?)', $productIds); $this->connection->delete($optionTable, $this->connection->quoteInto('parent_id IN (?)', $productIdsInWhere)); $this->connection->delete($optionTable, $this->connection->quoteInto('parent_product_id IN (?)', $productIdsInWhere)); return $this; }
/** * @param AbstractCondition $condition * @param string $value * @return string * @throws \Magento\Framework\Exception */ protected function _getMappedSqlCondition(AbstractCondition $condition, $value = '') { $argument = $condition->getMappedSqlField(); if ($argument) { $conditionOperator = $condition->getOperatorForValidate(); if (!isset($this->_conditionOperatorMap[$conditionOperator])) { throw new \Magento\Framework\Exception('Unknown condition operator'); } $sql = str_replace(':field', $this->_connection->getIfNullSql($this->_connection->quoteIdentifier($argument), 0), $this->_conditionOperatorMap[$conditionOperator]); return $this->_expressionFactory->create(['expression' => $value . $this->_connection->quoteInto($sql, $condition->getBindArgumentValue())]); } return ''; }
/** * @param Rule $rule * @return $this * @SuppressWarnings(PHPMD.CyclomaticComplexity) * @SuppressWarnings(PHPMD.NPathComplexity) */ protected function updateRuleProductData(Rule $rule) { $ruleId = $rule->getId(); if ($rule->getProductsFilter()) { $this->connection->delete($this->getTable('catalogrule_product'), ['rule_id=?' => $ruleId, 'product_id IN (?)' => $rule->getProductsFilter()]); } else { $this->connection->delete($this->getTable('catalogrule_product'), $this->connection->quoteInto('rule_id=?', $ruleId)); } if (!$rule->getIsActive()) { return $this; } $websiteIds = $rule->getWebsiteIds(); if (!is_array($websiteIds)) { $websiteIds = explode(',', $websiteIds); } if (empty($websiteIds)) { return $this; } \Magento\Framework\Profiler::start('__MATCH_PRODUCTS__'); $productIds = $rule->getMatchingProductIds(); \Magento\Framework\Profiler::stop('__MATCH_PRODUCTS__'); $customerGroupIds = $rule->getCustomerGroupIds(); $fromTime = strtotime($rule->getFromDate()); $toTime = strtotime($rule->getToDate()); $toTime = $toTime ? $toTime + self::SECONDS_IN_DAY - 1 : 0; $sortOrder = (int) $rule->getSortOrder(); $actionOperator = $rule->getSimpleAction(); $actionAmount = $rule->getDiscountAmount(); $subActionOperator = $rule->getSubIsEnable() ? $rule->getSubSimpleAction() : ''; $subActionAmount = $rule->getSubDiscountAmount(); $actionStop = $rule->getStopRulesProcessing(); $rows = []; foreach ($productIds as $productId => $validationByWebsite) { foreach ($websiteIds as $websiteId) { if (empty($validationByWebsite[$websiteId])) { continue; } foreach ($customerGroupIds as $customerGroupId) { $rows[] = ['rule_id' => $ruleId, 'from_time' => $fromTime, 'to_time' => $toTime, 'website_id' => $websiteId, 'customer_group_id' => $customerGroupId, 'product_id' => $productId, 'action_operator' => $actionOperator, 'action_amount' => $actionAmount, 'action_stop' => $actionStop, 'sort_order' => $sortOrder, 'sub_simple_action' => $subActionOperator, 'sub_discount_amount' => $subActionAmount]; if (count($rows) == $this->batchCount) { $this->connection->insertMultiple($this->getTable('catalogrule_product'), $rows); $rows = []; } } } } if (!empty($rows)) { $this->connection->insertMultiple($this->getTable('catalogrule_product'), $rows); } return $this; }
/** * Fill temporary table by data from products EAV attributes by type * * @param string $tableName * @param array $tableColumns * @param array $changedIds * @param string $valueFieldSuffix * @param int $storeId * @return void */ protected function _fillTemporaryTable($tableName, array $tableColumns, array $changedIds, $valueFieldSuffix, $storeId) { $metadata = $this->metadataPool->getMetadata(\Magento\Catalog\Api\Data\ProductInterface::class); if (!empty($tableColumns)) { $columnsChunks = array_chunk($tableColumns, Action\Indexer::ATTRIBUTES_CHUNK_SIZE, true); foreach ($columnsChunks as $columnsList) { $select = $this->_connection->select(); $selectValue = $this->_connection->select(); $entityTableName = $this->_getTemporaryTableName($this->_productIndexerHelper->getTable('catalog_product_entity')); $temporaryTableName = $this->_getTemporaryTableName($tableName); $temporaryValueTableName = $temporaryTableName . $valueFieldSuffix; $keyColumn = ['entity_id']; $columns = array_merge($keyColumn, array_keys($columnsList)); $valueColumns = $keyColumn; $flatColumns = $this->_productIndexerHelper->getFlatColumns(); $iterationNum = 1; $select->from(['et' => $entityTableName], $keyColumn)->join(['e' => $this->resource->getTableName('catalog_product_entity')], 'e.entity_id = et.entity_id', []); $selectValue->from(['e' => $temporaryTableName], $keyColumn); /** @var $attribute \Magento\Catalog\Model\ResourceModel\Eav\Attribute */ foreach ($columnsList as $columnName => $attribute) { $countTableName = 't' . $iterationNum++; $joinCondition = sprintf('e.%3$s = %1$s.%3$s AND %1$s.attribute_id = %2$d AND %1$s.store_id = 0', $countTableName, $attribute->getId(), $metadata->getLinkField()); $select->joinLeft([$countTableName => $tableName], $joinCondition, [$columnName => 'value']); if ($attribute->getFlatUpdateSelect($storeId) instanceof \Magento\Framework\DB\Select) { $attributeCode = $attribute->getAttributeCode(); $columnValueName = $attributeCode . $valueFieldSuffix; if (isset($flatColumns[$columnValueName])) { $valueJoinCondition = sprintf('e.%1$s = %2$s.option_id AND %2$s.store_id = 0', $attributeCode, $countTableName); $selectValue->joinLeft([$countTableName => $this->_productIndexerHelper->getTable('eav_attribute_option_value')], $valueJoinCondition, [$columnValueName => $countTableName . '.value']); $valueColumns[] = $columnValueName; } } } if (!empty($changedIds)) { $select->where($this->_connection->quoteInto('e.entity_id IN (?)', $changedIds)); } $sql = $select->insertFromSelect($temporaryTableName, $columns, true); $this->_connection->query($sql); if (count($valueColumns) > 1) { if (!empty($changedIds)) { $selectValue->where($this->_connection->quoteInto('e.entity_id IN (?)', $changedIds)); } $sql = $selectValue->insertFromSelect($temporaryValueTableName, $valueColumns, true); $this->_connection->query($sql); } } } }
/** * @param Node $node * @return $this * @throws \Exception */ public function removeNode($node) { // For reorder old node branch $dataReorderOld = [$this->_orderField => new \Zend_Db_Expr($this->_conn->quoteIdentifier($this->_orderField) . '-1')]; $conditionReorderOld = $this->_conn->quoteIdentifier($this->_parentField) . '=' . $node->getData($this->_parentField) . ' AND ' . $this->_conn->quoteIdentifier($this->_orderField) . '>' . $node->getData($this->_orderField); $this->_conn->beginTransaction(); try { $condition = $this->_conn->quoteInto("{$this->_idField}=?", $node->getId()); $this->_conn->delete($this->_table, $condition); // Update old node branch $this->_conn->update($this->_table, $dataReorderOld, $conditionReorderOld); $this->_conn->commit(); } catch (\Exception $e) { $this->_conn->rollBack(); throw new \Exception('Can\'t remove tree node'); } parent::removeNode($node); return $this; }
/** * Load product(s) attributes * * @param int $storeId * @param array $productIds * @param array $attributeTypes * @return array */ public function getProductAttributes($storeId, array $productIds, array $attributeTypes) { $result = []; $selects = []; $ifStoreValue = $this->connection->getCheckSql('t_store.value_id > 0', 't_store.value', 't_default.value'); foreach ($attributeTypes as $backendType => $attributeIds) { if ($attributeIds) { $tableName = $this->getTable('catalog_product_entity_' . $backendType); $selects[] = $this->connection->select()->from(['t_default' => $tableName], ['entity_id', 'attribute_id'])->joinLeft(['t_store' => $tableName], $this->connection->quoteInto('t_default.entity_id=t_store.entity_id' . ' AND t_default.attribute_id=t_store.attribute_id' . ' AND t_store.store_id = ?', $storeId), ['value' => $this->unifyField($ifStoreValue, $backendType)])->where('t_default.store_id = ?', 0)->where('t_default.attribute_id IN (?)', $attributeIds)->where('t_default.entity_id IN (?)', $productIds); } } if ($selects) { $select = $this->connection->select()->union($selects, \Magento\Framework\DB\Select::SQL_UNION_ALL); $query = $this->connection->query($select); while ($row = $query->fetch()) { $result[$row['entity_id']][$row['attribute_id']] = $row['value']; } } return $result; }
/** * Delete unnecessary links. * * @return $this */ protected function _deleteData() { $linkTable = $this->_resource->getTableName('catalog_product_super_link'); $relationTable = $this->_resource->getTableName('catalog_product_relation'); if ($this->_entityModel->getBehavior() == \Magento\ImportExport\Model\Import::BEHAVIOR_APPEND && !empty($this->_productSuperData['product_id']) && !empty($this->_simpleIdsToDelete)) { $quoted = $this->_connection->quoteInto('IN (?)', [$this->_productSuperData['product_id']]); $quotedChildren = $this->_connection->quoteInto('IN (?)', $this->_simpleIdsToDelete); $this->_connection->delete($linkTable, "parent_id {$quoted} AND product_id {$quotedChildren}"); $this->_connection->delete($relationTable, "parent_id {$quoted} AND child_id {$quotedChildren}"); } return $this; }
/** * Delete custom option type values * * @param array $optionIds * @return $this */ protected function _deleteSpecificTypeValues(array $optionIds) { $this->_connection->delete($this->_tables['catalog_product_option_type_value'], $this->_connection->quoteInto('option_id IN (?)', $optionIds)); return $this; }
/** * Write single product into flat product table * * @param int $storeId * @param int $productId * @param string $valueFieldSuffix * @return \Magento\Catalog\Model\Indexer\Product\Flat */ public function write($storeId, $productId, $valueFieldSuffix = '') { $flatTable = $this->_productIndexerHelper->getFlatTableName($storeId); $attributes = $this->_productIndexerHelper->getAttributes(); $eavAttributes = $this->_productIndexerHelper->getTablesStructure($attributes); $updateData = array(); $describe = $this->_connection->describeTable($flatTable); foreach ($eavAttributes as $tableName => $tableColumns) { $columnsChunks = array_chunk($tableColumns, self::ATTRIBUTES_CHUNK_SIZE, true); foreach ($columnsChunks as $columns) { $select = $this->_connection->select(); $selectValue = $this->_connection->select(); $keyColumns = array('entity_id' => 'e.entity_id', 'attribute_id' => 't.attribute_id', 'value' => $this->_connection->getIfNullSql('`t2`.`value`', '`t`.`value`')); if ($tableName != $this->_productIndexerHelper->getTable('catalog_product_entity')) { $valueColumns = array(); $ids = array(); $select->from(array('e' => $this->_productIndexerHelper->getTable('catalog_product_entity')), $keyColumns); $selectValue->from(array('e' => $this->_productIndexerHelper->getTable('catalog_product_entity')), $keyColumns); /** @var $attribute \Magento\Catalog\Model\Resource\Eav\Attribute */ foreach ($columns as $columnName => $attribute) { if (isset($describe[$columnName])) { $ids[$attribute->getId()] = $columnName; } } $select->joinLeft(array('t' => $tableName), 'e.entity_id = t.entity_id ' . $this->_connection->quoteInto(' AND t.attribute_id IN (?)', array_keys($ids)) . ' AND t.store_id = 0', array())->joinLeft(array('t2' => $tableName), 't.entity_id = t2.entity_id ' . ' AND t.attribute_id = t2.attribute_id ' . $this->_connection->quoteInto(' AND t2.store_id = ?', $storeId), array())->where('e.entity_id = ' . $productId)->where('t.attribute_id IS NOT NULL'); $cursor = $this->_connection->query($select); while ($row = $cursor->fetch(\Zend_Db::FETCH_ASSOC)) { $updateData[$ids[$row['attribute_id']]] = $row['value']; $valueColumnName = $ids[$row['attribute_id']] . $valueFieldSuffix; if (isset($describe[$valueColumnName])) { $valueColumns[$row['value']] = $valueColumnName; } } //Update not simple attributes (eg. dropdown) if (!empty($valueColumns)) { $valueIds = array_keys($valueColumns); $select = $this->_connection->select()->from(array('t' => $this->_productIndexerHelper->getTable('eav_attribute_option_value')), array('t.option_id', 't.value'))->where($this->_connection->quoteInto('t.option_id IN (?)', $valueIds)); $cursor = $this->_connection->query($select); while ($row = $cursor->fetch(\Zend_Db::FETCH_ASSOC)) { $valueColumnName = $valueColumns[$row['option_id']]; if (isset($describe[$valueColumnName])) { $updateData[$valueColumnName] = $row['value']; } } } } else { $columnNames = array_keys($columns); $columnNames[] = 'attribute_set_id'; $columnNames[] = 'type_id'; $select->from(array('e' => $this->_productIndexerHelper->getTable('catalog_product_entity')), $columnNames)->where('e.entity_id = ' . $productId); $cursor = $this->_connection->query($select); $row = $cursor->fetch(\Zend_Db::FETCH_ASSOC); if (!empty($row)) { foreach ($row as $columnName => $value) { $updateData[$columnName] = $value; } } } } } if (!empty($updateData)) { $updateData += array('entity_id' => $productId); $updateFields = array(); foreach ($updateData as $key => $value) { $updateFields[$key] = $key; } $this->_connection->insertOnDuplicate($flatTable, $updateData, $updateFields); } return $this; }
/** * @param array $productIds * @throws \Magento\Framework\Exception\LocalizedException * @return void */ protected function deleteOldLinks($productIds) { if ($this->getBehavior() != \Magento\ImportExport\Model\Import::BEHAVIOR_APPEND) { $this->connection->delete($this->productLink->getMainTable(), $this->connection->quoteInto('product_id IN (?) AND link_type_id = ' . $this->getLinkTypeId(), $productIds)); } }