/**
* Get category collection array
*
* @param null|string|bool|int|\Magento\Store\Model\Store $storeId
* @return array|bool
*/
public function getCollection($storeId)
{
$products = [];
/* @var $store \Magento\Store\Model\Store */
$store = $this->_storeManager->getStore($storeId);
if (!$store) {
return false;
}
$connection = $this->getConnection();
$this->_select = $connection->select()->from(['e' => $this->getMainTable()], [$this->getIdFieldName(), 'updated_at'])->joinInner(['w' => $this->getTable('catalog_product_website')], 'e.entity_id = w.product_id', [])->joinLeft(['url_rewrite' => $this->getTable('url_rewrite')], 'e.entity_id = url_rewrite.entity_id AND url_rewrite.is_autogenerated = 1' . $connection->quoteInto(' AND url_rewrite.store_id = ?', $store->getId()) . $connection->quoteInto(' AND url_rewrite.entity_type = ?', ProductUrlRewriteGenerator::ENTITY_TYPE), ['url' => 'request_path'])->where('w.website_id = ?', $store->getWebsiteId());
$this->_addFilter($store->getId(), 'visibility', $this->_productVisibility->getVisibleInSiteIds(), 'in');
$this->_addFilter($store->getId(), 'status', $this->_productStatus->getVisibleStatusIds(), 'in');
// Join product images required attributes
$imageIncludePolicy = $this->_sitemapData->getProductImageIncludePolicy($store->getId());
if (\Magento\Sitemap\Model\Source\Product\Image\IncludeImage::INCLUDE_NONE != $imageIncludePolicy) {
$this->_joinAttribute($store->getId(), 'name');
$this->_select->columns(['name' => $this->getConnection()->getIfNullSql('t2_name.value', 't1_name.value')]);
if (\Magento\Sitemap\Model\Source\Product\Image\IncludeImage::INCLUDE_ALL == $imageIncludePolicy) {
$this->_joinAttribute($store->getId(), 'thumbnail');
$this->_select->columns(['thumbnail' => $this->getConnection()->getIfNullSql('t2_thumbnail.value', 't1_thumbnail.value')]);
} elseif (\Magento\Sitemap\Model\Source\Product\Image\IncludeImage::INCLUDE_BASE == $imageIncludePolicy) {
$this->_joinAttribute($store->getId(), 'image');
$this->_select->columns(['image' => $this->getConnection()->getIfNullSql('t2_image.value', 't1_image.value')]);
}
}
$query = $connection->query($this->_select);
while ($row = $query->fetch()) {
$product = $this->_prepareProduct($row, $store->getId());
$products[$product->getId()] = $product;
}
return $products;
}
/**
* Apply price rule price to price index table
*
* @param \Magento\Framework\DB\Select $select
* @param array|string $indexTable
* @param string $entityId
* @param string $customerGroupId
* @param string $websiteId
* @param array $updateFields the array of fields for compare with rule price and update
* @param string $websiteDate
* @return \Magento\CatalogRule\Model\ResourceModel\Rule\Product\Price
*/
public function applyPriceRuleToIndexTable(\Magento\Framework\DB\Select $select, $indexTable, $entityId, $customerGroupId, $websiteId, $updateFields, $websiteDate)
{
if (empty($updateFields)) {
return $this;
}
if (is_array($indexTable)) {
foreach ($indexTable as $k => $v) {
if (is_string($k)) {
$indexAlias = $k;
} else {
$indexAlias = $v;
}
break;
}
} else {
$indexAlias = $indexTable;
}
$select->join(['rp' => $this->getMainTable()], "rp.rule_date = {$websiteDate}", [])->where("rp.product_id = {$entityId} AND rp.website_id = {$websiteId} AND rp.customer_group_id = {$customerGroupId}");
foreach ($updateFields as $priceField) {
$priceCond = $this->getConnection()->quoteIdentifier([$indexAlias, $priceField]);
$priceExpr = $this->getConnection()->getCheckSql("rp.rule_price < {$priceCond}", 'rp.rule_price', $priceCond);
$select->columns([$priceField => $priceExpr]);
}
$query = $select->crossUpdateFromSelect($indexTable);
$this->getConnection()->query($query);
return $this;
}
/**
* @param Select $select
* @param AggregationRange[] $ranges
* @return Select
*/
private function generateCase(Select $select, array $ranges)
{
$casesResults = [];
$field = RequestBucketInterface::FIELD_VALUE;
foreach ($ranges as $range) {
$from = $range->getFrom();
$to = $range->getTo();
if ($from && $to) {
$casesResults = array_merge($casesResults, ["`{$field}` BETWEEN {$from} AND {$to}" => "'{$from}_{$to}'"]);
} elseif ($from) {
$casesResults = array_merge($casesResults, ["`{$field}` >= {$from}" => "'{$from}_*'"]);
} elseif ($to) {
$casesResults = array_merge($casesResults, ["`{$field}` < {$to}" => "'*_{$to}'"]);
}
}
$cases = $this->connection->getCaseSql('', $casesResults);
$select->columns([RequestBucketInterface::FIELD_VALUE => $cases]);
return $select;
}
/**
* @param \Magento\Framework\DB\Select $select
* @param string $table
* @param string $type
* @return \Magento\Framework\DB\Select
*/
protected function _addLoadAttributesSelectValues($select, $table, $type)
{
$storeId = $this->getStoreId();
if ($storeId) {
$connection = $this->getConnection();
$valueExpr = $connection->getCheckSql('t_s.value_id IS NULL', 't_d.value', 't_s.value');
$select->columns(['default_value' => 't_d.value', 'store_value' => 't_s.value', 'value' => $valueExpr]);
} else {
$select = parent::_addLoadAttributesSelectValues($select, $table, $type);
}
return $select;
}
/**
* Add select values
*
* @param \Magento\Framework\DB\Select $select
* @param string $table
* @param string $type
* @return Select
* @SuppressWarnings(PHPMD.UnusedFormalParameter)
*/
protected function _addLoadAttributesSelectValues($select, $table, $type)
{
$select->columns(['value' => $table . '.value']);
return $select;
}
/**
* Adds order by random to select object
* Possible using integer field for optimization
*
* @param Select $select
* @param string $field
* @return $this
*/
public function orderRand(Select $select, $field = null)
{
if ($field !== null) {
$expression = new \Zend_Db_Expr(sprintf('RAND() * %s', $this->quoteIdentifier($field)));
$select->columns(array('mage_rand' => $expression));
$spec = new \Zend_Db_Expr('mage_rand');
} else {
$spec = new \Zend_Db_Expr('RAND()');
}
$select->order($spec);
return $this;
}
/**
* @param RequestInterface $request
* @param MatchContainer[] $matchQueries
* @param ScoreBuilder $scoreBuilder
* @param Select $select
* @param IndexBuilderInterface $indexBuilder
* @return Select
* @internal param QueryContainer $queryContainer
*/
private function addMatchQueries(
RequestInterface $request,
array $matchQueries,
ScoreBuilder $scoreBuilder,
Select $select,
IndexBuilderInterface $indexBuilder
) {
if (!$matchQueries) {
$select->columns($scoreBuilder->build());
$select = $this->createAroundSelect($select, $scoreBuilder);
} elseif (count($matchQueries) === 1) {
$matchContainer = reset($matchQueries);
$this->matchBuilder->build(
$scoreBuilder,
$select,
$matchContainer->getRequest(),
$matchContainer->getConditionType()
);
$select->columns($scoreBuilder->build());
$select = $this->createAroundSelect($select, $scoreBuilder);
} elseif (count($matchQueries) > 1) {
$select->columns($scoreBuilder->build());
$select = $this->createAroundSelect($select, $scoreBuilder);
$subSelect = $select;
$select = $this->resource->getConnection(Resource::DEFAULT_READ_RESOURCE)->select();
$tables = array_merge(array_keys($matchQueries), ['main_select.relevance']);
$relevance = implode('.relevance + ', $tables);
$select
->from(
['main_select' => $subSelect],
[
$this->entityMetadata->getEntityId() => 'entity_id',
'relevance' => sprintf('(%s)', $relevance),
]
);
foreach ($matchQueries as $matchName => $matchContainer) {
$matchSelect = $indexBuilder->build($request);
$matchScoreBuilder = $this->scoreBuilderFactory->create();
$matchSelect = $this->matchBuilder->build(
$matchScoreBuilder,
$matchSelect,
$matchContainer->getRequest(),
$matchContainer->getConditionType()
);
$matchSelect->columns($matchScoreBuilder->build());
$select->join(
[$matchName => $this->createAroundSelect($matchSelect, $scoreBuilder)],
$matchName . '.entity_id = main_select.entity_id',
[]
);
}
}
return $select;
}
/**
* @param Select $query
* @param Table $previousResultTable
* @param ScoreBuilder $scoreBuilder
* @return Select
* @throws \Zend_Db_Exception
*/
private function joinPreviousResultToSelect(Select $query, Table $previousResultTable, ScoreBuilder $scoreBuilder)
{
$query->joinInner(['previous_results' => $previousResultTable->getName()], 'previous_results.entity_id = search_index.entity_id', []);
$scoreBuilder->addCondition('previous_results.score', false);
$query->columns($scoreBuilder->build());
$query = $this->createAroundSelect($query, $scoreBuilder);
return $query;
}
/**
* Add prepared column group_concat expression
*
* @param \Magento\Framework\DB\Select $select
* @param string $fieldAlias Field alias which will be added with column group_concat expression
* @param string $fields
* @param string $groupConcatDelimiter
* @param string $fieldsDelimiter
* @param string $additionalWhere
* @return \Magento\Framework\DB\Select
*/
public function addGroupConcatColumn($select, $fieldAlias, $fields, $groupConcatDelimiter = ',', $fieldsDelimiter = '', $additionalWhere = '')
{
if (is_array($fields)) {
$fieldExpr = $this->_getReadAdapter()->getConcatSql($fields, $fieldsDelimiter);
} else {
$fieldExpr = $fields;
}
if ($additionalWhere) {
$fieldExpr = $this->_getReadAdapter()->getCheckSql($additionalWhere, $fieldExpr, "''");
}
$separator = '';
if ($groupConcatDelimiter) {
$separator = sprintf(" SEPARATOR '%s'", $groupConcatDelimiter);
}
$select->columns([$fieldAlias => new \Zend_Db_Expr(sprintf('GROUP_CONCAT(%s%s)', $fieldExpr, $separator))]);
return $select;
}
/**
* 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;
}
/**
* Join information for usin full text search
*
* @param string $table
* @param string $alias
* @param \Magento\Framework\DB\Select $select
* @return \Zend_Db_Expr
*/
public function chooseFulltext($table, $alias, $select)
{
$field = new \Zend_Db_Expr('MATCH (' . $alias . '.data_index) AGAINST (:query IN BOOLEAN MODE)');
$select->columns(array('relevance' => $field));
return $field;
}
