/**
* {@inheritdoc}
*/
public function getKeywordsOfTerm($term)
{
// Set terms to search on, limit to maxKeywords
$keywords = array_slice($this->termHelper->splitTerm($term), 0, $this->maxKeywords);
// If any term in search request
if (empty($keywords)) {
return [];
}
$matches = [];
foreach ($keywords as $searchTerm) {
$matches = array_merge($matches, $this->searchMatchingKeywords($searchTerm));
}
return $matches;
}
/**
* checks if the given result set matches all search terms
*
* @param QueryBuilder $query
* @param string $term
* @param Keyword[] $keywords
*/
private function addAndSearchLogic($query, $term, $keywords)
{
$searchTerms = $this->termHelper->splitTerm($term);
$searchTermMatchQueries = $this->createSearchTermMatchQueries($keywords, $searchTerms);
$totalSearchTermMatchesQuery = $this->connection->createQueryBuilder();
$totalSearchTermMatchesQuery->select('sum(matches)')->from('(' . $searchTermMatchQueries . ')', 'termMatches')->where('termMatches.elementID = product_id');
$query->addSelect('(' . $totalSearchTermMatchesQuery->getSQL() . ') AS searchTermMatches');
$query->having('searchTermMatches >= ' . count($searchTerms));
}
/**
* Rebuilds the search index for the shopware default search query builder.
*/
public function build()
{
@ini_set("memory_limit", "512M");
@set_time_limit(0);
$this->setNextUpdateTimestamp();
// Truncate search index table
$this->connection->executeUpdate('TRUNCATE TABLE `s_search_index`');
// Get a list of all tables and columns in this tables that should be processed by search
/**
* Example return:
* tableID | table | where | referenz_table | fieldIDs | fields | foreign_key
* 1 | s_articles | NULL | NULL | 3,4 | name, keywords | NULL
* 2 | s_categories | NULL | s_articles_categories | 1,2 | metakeywords, description | categoryID
*/
$tables = $this->getSearchTables();
if (!empty($tables)) {
foreach ($tables as $table) {
// Set primary key
$table['elementID'] = empty($table['foreign_key']) && $table['table'] != 's_articles' ? 'articleID' : 'id';
// Build sql query to fetch values from this table
$sql = 'SELECT ' . $table['elementID'] . ' as id, ' . $table['fields'] . ' FROM ' . $table['table'];
// If any where condition is set, add to query
if (!empty($table['where'])) {
$sql .= ' WHERE ' . $table['where'];
}
// Get all fields & values from current table
$getTableKeywords = $this->connection->fetchAll($sql);
// If no result, return
if (empty($getTableKeywords)) {
continue;
}
// Build array from columns fieldIDs and fields
$fields = array_combine(explode(', ', $table["fieldIDs"]), explode(', ', $table["fields"]));
$keywords = [];
$sqlIndex = [];
// Go through every row of result
foreach ($getTableKeywords as $currentRow => $row) {
// Go through every column of result
foreach ($fields as $fieldID => $field) {
// Split string from column into keywords
$field_keywords = $this->termHelper->splitTerm($row[$field]);
if (empty($field_keywords)) {
continue;
}
foreach ($field_keywords as &$keyword) {
$keyword = $this->connection->quote($keyword);
$keywords[] = $keyword;
}
// SQL-queries to fill s_search_index
$sqlIndex[] = 'SELECT sk.id as keywordID, ' . $row['id'] . ' as elementID, ' . $fieldID . ' as fieldID ' . 'FROM s_search_keywords sk ' . 'WHERE sk.keyword IN (' . implode(', ', $field_keywords) . ')';
}
// If no new keywords were found, proceed with next table
if (empty($keywords)) {
continue;
}
// If last row or more then 5000 keywords fetched, write results to index
if ($currentRow == count($getTableKeywords) - 1 || count($keywords) > 5000) {
$keywords = array_unique($keywords);
// Remove duplicates
$sql_keywords = 'INSERT IGNORE INTO `s_search_keywords` (`keyword`) VALUES';
$sql_keywords .= ' (' . implode('), (', $keywords) . ')';
// Insert Keywords
$this->connection->executeUpdate($sql_keywords);
$keywords = [];
// Update index
$sqlIndex = implode("\n\nUNION ALL\n\n", $sqlIndex);
$sqlIndex = "INSERT IGNORE INTO s_search_index (keywordID, elementID, fieldID)\n\n" . $sqlIndex;
$this->connection->executeUpdate($sqlIndex);
$sqlIndex = [];
}
}
}
}
$this->cleanupIndex();
$this->cleanupKeywords();
}
