/**
* Remove products from flat that are not exist
*
* @param array $ids
* @param int $storeId
* @return void
*/
public function removeDeletedProducts(array &$ids, $storeId)
{
$select = $this->connection->select()->from($this->productIndexerHelper->getTable('catalog_product_entity'))->where('entity_id IN(?)', $ids);
$result = $this->connection->query($select);
$existentProducts = [];
foreach ($result->fetchAll() as $product) {
$existentProducts[] = $product['entity_id'];
}
$productsToDelete = array_diff($ids, $existentProducts);
$ids = $existentProducts;
$this->deleteProductsFromStore($productsToDelete, $storeId);
}
/**
* Clean unused relation products
*
* @param int $storeId
* @return \Magento\Catalog\Model\Indexer\Product\Flat\AbstractAction
*/
protected function _cleanRelationProducts($storeId)
{
if (!$this->_productIndexerHelper->isAddChildData()) {
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()) {
$select = $this->_connection->select()->distinct(true)->from($this->_productIndexerHelper->getTable($relation->getTable()), "{$relation->getParentFieldName()}");
$joinLeftCond = ["e.entity_id = t.{$relation->getParentFieldName()}", "e.child_id = t.{$relation->getChildFieldName()}"];
if ($relation->getWhere() !== null) {
$select->where($relation->getWhere());
$joinLeftCond[] = $relation->getWhere();
}
$entitySelect = new \Zend_Db_Expr($select->__toString());
/** @var $select \Magento\Framework\DB\Select */
$select = $this->_connection->select()->from(['e' => $this->_productIndexerHelper->getFlatTableName($storeId)], null)->joinLeft(['t' => $this->_productIndexerHelper->getTable($relation->getTable())], implode(' AND ', $joinLeftCond), [])->where('e.is_child = ?', 1)->where('e.entity_id IN(?)', $entitySelect)->where("t.{$relation->getChildFieldName()} IS NULL");
$sql = $select->deleteFromSelect('e');
$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);
}
}
}
}
/**
* 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);
}
}
}
}
/**
* 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;
}