/** * Load customer log data by customer id * * @param int $customerId * @return array */ protected function loadLogData($customerId) { /** @var \Magento\Framework\DB\Adapter\AdapterInterface $connection */ $connection = $this->resource->getConnection(); $select = $connection->select()->from(['cl' => $this->resource->getTableName('customer_log')])->joinLeft(['cv' => $this->resource->getTableName('customer_visitor')], 'cv.customer_id = cl.customer_id', ['last_visit_at'])->where('cl.customer_id = ?', $customerId)->order('cv.visitor_id DESC')->limit(1); return $connection->fetchRow($select); }
/** * {@inheritdoc} */ public function getAggregations(\Magento\Framework\Search\Dynamic\EntityStorage $entityStorage) { $aggregation = ['count' => 'count(DISTINCT main_table.entity_id)', 'max' => 'MAX(min_price)', 'min' => 'MIN(min_price)', 'std' => 'STDDEV_SAMP(min_price)']; $select = $this->getSelect(); $tableName = $this->resource->getTableName('catalog_product_index_price'); /** @var Table $table */ $table = $entityStorage->getSource(); $select->from(['main_table' => $tableName], [])->joinInner(['entities' => $table->getName()], 'main_table.entity_id = entities.entity_id', [])->columns($aggregation); $select = $this->setCustomerGroupId($select); $result = $this->connection->fetchRow($select); return $result; }
/** * Build index query * * @param RequestInterface $request * @return Select */ public function build(RequestInterface $request) { $searchIndexTable = $this->scopeResolver->resolve($request->getIndex(), $request->getDimensions()); $select = $this->resource->getConnection()->select()->from(['search_index' => $searchIndexTable], ['entity_id' => 'entity_id'])->joinLeft(['cea' => $this->resource->getTableName('catalog_eav_attribute')], 'search_index.attribute_id = cea.attribute_id', []); $select = $this->tableMapper->addTables($select, $request); $select = $this->processDimensions($request, $select); $isShowOutOfStock = $this->config->isSetFlag('cataloginventory/options/show_out_of_stock', ScopeInterface::SCOPE_STORE); if ($isShowOutOfStock === false) { $select->joinLeft(['stock_index' => $this->resource->getTableName('cataloginventory_stock_status')], 'search_index.entity_id = stock_index.product_id' . $this->resource->getConnection()->quoteInto(' AND stock_index.website_id = ?', $this->storeManager->getWebsite()->getId()), []); $select->where('stock_index.stock_status = ?', 1); } return $select; }
/** * Retrieve customer IDs for reindex * * @return array */ protected function getCustomerIdsForReindex() { $connection = $this->resource->getConnection(); $gridTableName = $this->flatScopeResolver->resolve(Customer::CUSTOMER_GRID_INDEXER_ID, []); $select = $connection->select()->from($this->resource->getTableName($gridTableName), 'last_visit_at')->order('last_visit_at DESC')->limit(1); $lastVisitAt = $connection->query($select)->fetchColumn(); $select = $connection->select()->from($this->resource->getTableName('customer_log'), 'customer_id')->where('last_login_at > ?', $lastVisitAt); $customerIds = []; foreach ($connection->query($select)->fetchAll() as $row) { $customerIds[] = $row['customer_id']; } return $customerIds; }
/** * @param string $index * @param Dimension[] $dimensions * @return string */ public function resolve($index, array $dimensions) { $tableNameParts = [$index]; foreach ($dimensions as $dimension) { switch ($dimension->getName()) { case 'scope': $tableNameParts[] = $dimension->getName() . $this->getScopeId($dimension); break; default: $tableNameParts[] = $dimension->getName() . $dimension->getValue(); } } return $this->resource->getTableName(implode('_', $tableNameParts)); }
/** * Check attribute lock state * * @param \Magento\Framework\Model\AbstractModel $object * @param null $attributeSet * @throws \Magento\Framework\Exception\LocalizedException * @return void */ public function validate(\Magento\Framework\Model\AbstractModel $object, $attributeSet = null) { $metadata = $this->metadataPool->getMetadata(ProductInterface::class); $connection = $this->resource->getConnection(); $bind = ['attribute_id' => $object->getAttributeId()]; $select = clone $connection->select(); $select->reset()->from(['main_table' => $this->resource->getTableName('catalog_product_super_attribute')], ['psa_count' => 'COUNT(product_super_attribute_id)'])->join(['entity' => $this->resource->getTableName('catalog_product_entity')], 'main_table.product_id = entity.' . $metadata->getLinkField())->where('main_table.attribute_id = :attribute_id')->group('main_table.attribute_id')->limit(1); if ($attributeSet !== null) { $bind['attribute_set_id'] = $attributeSet; $select->where('entity.attribute_set_id = :attribute_set_id'); } if ($connection->fetchOne($select, $bind)) { throw new \Magento\Framework\Exception\LocalizedException(__('This attribute is used in configurable products.')); } }
/** * Returns mapping data for field in format: [ * 'table_alias', * 'table', * 'join_condition', * ['fields'] * ] * @param FilterInterface $filter * @return array */ private function getMappingData(FilterInterface $filter) { $alias = null; $table = null; $mapOn = null; $mappedFields = null; $field = $filter->getField(); $fieldToTableMap = $this->getFieldToTableMap($field); if ($fieldToTableMap) { list($alias, $table, $mapOn, $mappedFields) = $fieldToTableMap; $table = $this->resource->getTableName($table); } elseif ($attribute = $this->getAttributeByCode($field)) { if ($filter->getType() === FilterInterface::TYPE_TERM && in_array($attribute->getFrontendInput(), ['select', 'multiselect'], true)) { $table = $this->resource->getTableName('catalog_product_index_eav'); $alias = $field . '_filter'; $mapOn = sprintf('search_index.entity_id = %1$s.entity_id AND %1$s.attribute_id = %2$d AND %1$s.store_id = %3$d', $alias, $attribute->getId(), $this->getStoreId()); $mappedFields = []; } elseif ($attribute->getBackendType() === AbstractAttribute::TYPE_STATIC) { $table = $attribute->getBackendTable(); $alias = $field . '_filter'; $mapOn = 'search_index.entity_id = ' . $alias . '.entity_id'; $mappedFields = null; } } return [$alias, $table, $mapOn, $mappedFields]; }
/** * Returns lookup SQL * * @return string */ public function __toString() { $select = $this->adapter->select()->from(['lookup' => $this->resource->getTableName($this->targetTable)], [$this->targetColumn])->limit(1); $this->processWhereCondition($select); $this->processSortOrder($select); return sprintf('(%s)', $select->assemble()); }
/** * Reindex affected products * * @param int $storeId * @param string $attrCode * @param \Zend_Db_Expr $attrConditionValue * @return void */ protected function _refreshSpecialPriceByStore($storeId, $attrCode, $attrConditionValue) { $attribute = $this->_eavConfig->getAttribute(\Magento\Catalog\Model\Product::ENTITY, $attrCode); $attributeId = $attribute->getAttributeId(); $connection = $this->_getConnection(); $select = $connection->select()->from($this->_resource->getTableName(['catalog_product_entity', 'datetime']), ['entity_id'])->where('attribute_id = ?', $attributeId)->where('store_id = ?', $storeId)->where('value = ?', $attrConditionValue); $this->_processor->getIndexer()->reindexList($connection->fetchCol($select, ['entity_id'])); }
/** * @param \Magento\CatalogSearch\Model\Adapter\Mysql\Aggregation\DataProvider $subject * @param callable|\Closure $proceed * @param BucketInterface $bucket * @param Dimension[] $dimensions * * @param Table $entityIdsTable * @return Select * @SuppressWarnings(PHPMD.UnusedFormalParameter) */ public function aroundGetDataSet(\Magento\CatalogSearch\Model\Adapter\Mysql\Aggregation\DataProvider $subject, \Closure $proceed, BucketInterface $bucket, array $dimensions, Table $entityIdsTable) { if ($bucket->getField() == 'category_ids') { $currentScopeId = $this->scopeResolver->getScope($dimensions['scope']->getValue())->getId(); $currentCategory = $this->layer->getCurrentCategory(); $derivedTable = $this->resource->getConnection()->select(); $derivedTable->from(['main_table' => $this->resource->getTableName('catalog_category_product_index')], ['value' => 'category_id'])->where('main_table.store_id = ?', $currentScopeId); $derivedTable->joinInner(['entities' => $entityIdsTable->getName()], 'main_table.product_id = entities.entity_id', []); if (!empty($currentCategory)) { $derivedTable->join(['category' => $this->resource->getTableName('catalog_category_entity')], 'main_table.category_id = category.entity_id', [])->where('`category`.`path` LIKE ?', $currentCategory->getPath() . '%')->where('`category`.`level` > ?', $currentCategory->getLevel()); } $select = $this->resource->getConnection()->select(); $select->from(['main_table' => $derivedTable]); return $select; } return $proceed($bucket, $dimensions, $entityIdsTable); }
/** * {@inheritdoc} */ public function getDataSet(BucketInterface $bucket, array $dimensions, Table $entityIdsTable) { $currentScope = $this->scopeResolver->getScope($dimensions['scope']->getValue())->getId(); $attribute = $this->eavConfig->getAttribute(Product::ENTITY, $bucket->getField()); $select = $this->getSelect(); if ($attribute->getAttributeCode() == 'price') { /** @var \Magento\Store\Model\Store $store */ $store = $this->scopeResolver->getScope($currentScope); if (!$store instanceof \Magento\Store\Model\Store) { throw new \RuntimeException('Illegal scope resolved'); } $table = $this->resource->getTableName('catalog_product_index_price'); $select->from(['main_table' => $table], null)->columns([BucketInterface::FIELD_VALUE => 'main_table.min_price'])->where('main_table.customer_group_id = ?', $this->customerSession->getCustomerGroupId())->where('main_table.website_id = ?', $store->getWebsiteId()); } else { $currentScopeId = $this->scopeResolver->getScope($currentScope)->getId(); $table = $this->resource->getTableName('catalog_product_index_eav' . ($attribute->getBackendType() == 'decimal' ? '_decimal' : '')); $select->from(['main_table' => $table], ['value'])->where('main_table.attribute_id = ?', $attribute->getAttributeId())->where('main_table.store_id = ? ', $currentScopeId); } $select->joinInner(['entities' => $entityIdsTable->getName()], 'main_table.entity_id = entities.entity_id', []); return $select; }
/** * @param FilterInterface $filter * @param string $query * @param Attribute $attribute * @return string */ private function processRangeNumeric(FilterInterface $filter, $query, $attribute) { $tableSuffix = $attribute->getBackendType() === 'decimal' ? '_decimal' : ''; $table = $this->resource->getTableName("catalog_product_index_eav{$tableSuffix}"); $select = $this->connection->select(); $currentStoreId = $this->scopeResolver->getScope()->getId(); $select->from(['main_table' => $table], 'entity_id')->columns([$filter->getField() => 'main_table.value'])->where('main_table.attribute_id = ?', $attribute->getAttributeId())->where('main_table.store_id = ?', $currentStoreId)->having($query); $resultQuery = 'search_index.entity_id IN ( select entity_id from ' . $this->conditionManager->wrapBrackets($select) . ' as filter )'; return $resultQuery; }
/** * Reindex affected products * * @param int $storeId * @param string $attrCode * @param \Zend_Db_Expr $attrConditionValue * @return void */ protected function _refreshSpecialPriceByStore($storeId, $attrCode, $attrConditionValue) { $attribute = $this->_eavConfig->getAttribute(\Magento\Catalog\Model\Product::ENTITY, $attrCode); $attributeId = $attribute->getAttributeId(); $linkField = $this->getMetadataPool()->getMetadata(CategoryInterface::class)->getLinkField(); $identifierField = $this->getMetadataPool()->getMetadata(CategoryInterface::class)->getIdentifierField(); $connection = $this->_getConnection(); $select = $connection->select()->from(['attr' => $this->_resource->getTableName(['catalog_product_entity', 'datetime'])], [$identifierField => 'cat.' . $identifierField])->joinLeft(['cat' => $this->_resource->getTableName('catalog_product_entity')], 'cat.' . $linkField . '= attr.' . $linkField, '')->where('attr.attribute_id = ?', $attributeId)->where('attr.store_id = ?', $storeId)->where('attr.value = ?', $attrConditionValue); $selectData = $connection->fetchCol($select, $identifierField); if (!empty($selectData)) { $this->_processor->getIndexer()->reindexList($selectData); } }
/** * Return all product children ids * * @param int $productId Product Entity Id * @param string $typeId Super Product Link Type * @return array|null */ public function getProductChildIds($productId, $typeId) { $typeInstance = $this->getProductTypeInstance($typeId); $relation = $typeInstance->isComposite($this->getProductEmulator($typeId)) ? $typeInstance->getRelationInfo() : false; if ($relation && $relation->getTable() && $relation->getParentFieldName() && $relation->getChildFieldName()) { $select = $this->connection->select()->from(['main' => $this->getTable($relation->getTable())], [$relation->getChildFieldName()]); $select->join(['e' => $this->resource->getTableName('catalog_product_entity')], 'e.' . $this->metadata->getLinkField() . ' = main.' . $relation->getParentFieldName())->where('e.entity_id = ?', $productId); if ($relation->getWhere() !== null) { $select->where($relation->getWhere()); } return $this->connection->fetchCol($select); } return null; }
/** * Build an "after" event for the given table and event * * @param string $event The DB level event, like "update" or "insert" * * @return string */ private function getAfterEventTriggerName($event) { return $this->resource->getTriggerName($this->resource->getTableName($this->getTableName()), Trigger::TIME_AFTER, $event); }
/** * Return validated table name * * @param string|string[] $table * @return string */ private function getTable($table) { return $this->resource->getTableName($table); }
/** * {@inheritdoc} */ public function deleteByData(array $data) { $this->connection->query($this->prepareSelect($data)->deleteFromSelect($this->resource->getTableName(self::TABLE_NAME))); }
/** * Get table name * * @param string $name * @return string */ protected function getTableName($name) { return $this->resource->getTableName($name); }
/** * Returns table name for given entity * * @param string $entityName * @return string */ protected function _getTable($entityName) { return $this->_resource->getTableName($entityName); }