public function sphinxTest($keyword, $cateid)
{
$keyword = $this->getSplitWd($keyword);
$sphinxConfig = array("host" => "172.17.17.105", "port" => 9093);
$conn = new \Foolz\SphinxQL\Connection();
$conn->setParams(array('host' => $sphinxConfig["host"], 'port' => $sphinxConfig["port"]));
$query = \Foolz\SphinxQL\SphinxQL::create($conn);
$query->select(array("id", "cate3"))->from("product_distri_29");
$query->where("cate3", "=", intval($cateid));
$query->limit(0, 1000);
$query->option("max_matches", 4000);
try {
$facet = Facet::create($conn)->facet('cate3')->limit(10)->orderby("count(*)", "desc");
$query->facet($facet);
echo $query->compileSelect()->getCompiled(), "\n";
$sphinxRs = $query->executeBatch();
var_dump($sphinxRs[1]);
echo "\n";
$sphinxql = new SphinxQL($conn);
$total = $sphinxql->query('show meta');
$total = $this->formatSphQLArray($total->execute());
var_dump($total);
exit;
} catch (Exception $e) {
return array();
}
}
protected function search(SphinxQL $query, $limit = null)
{
if (null !== $limit) {
$query->limit($limit);
}
$results = $this->index->search($query);
return $results;
}
public function __construct()
{
$conn = TestUtil::getConnectionDriver();
$conn->setParam('port', 9307);
$this->conn = $conn;
SphinxQL::create($this->conn)->query('TRUNCATE RTINDEX rt')->execute();
}
public function getPro($cateArr, $type = 1)
{
$hottype = 'tradenum';
$max_matches = 50;
$sphinxConfig = $this->di["config"]["gcdprolistsphinx"];
$host = $sphinxConfig->host;
$port = intval($sphinxConfig->port);
$table = $sphinxConfig->table;
$conn = new Connection();
$conn->setParams(array('host' => $host, 'port' => $port));
$query = SphinxQL::create($conn)->select('id', 'tradenum', 'visitnum', 'cid')->from($table);
$query->option('max_matches', $max_matches);
// $query->where('is_op', '=', 1);
$query->where('cate' . $cateArr['level'], '=', intval($cateArr['cateid']));
$query->limit(0, 50);
$query->orderBy($hottype, 'DESC');
$sphinxRes = $query->execute();
$totalArr = array_unique(array_column($sphinxRes, 'id'));
if (empty($totalArr)) {
return array();
}
$tmpArr = array_rand($totalArr, 10);
foreach ($tmpArr as $key => $value) {
$pidArr[$value] = $totalArr[$value];
}
$rs = $this->getProinfo($pidArr, $type);
return $rs;
}
public function __construct()
{
$conn = new Connection();
$conn->setConnectionParams('127.0.0.1', 9307);
$this->conn = $conn;
SphinxQL::create($this->conn)->query('TRUNCATE RTINDEX rt')->execute();
}
/**
* Execute raw query
*
* @param string $query
* @return Collection
*/
public function raw($query)
{
if (empty($query)) {
return new Collection([]);
}
$result = $this->sphinxQL->query($query)->execute();
return new Collection($result);
}
public function refill()
{
SphinxQL::create(self::$conn)->getConnection()->query('TRUNCATE RTINDEX rt');
$sq = SphinxQL::create(self::$conn)->insert()->into('rt')->columns('id', 'gid', 'title', 'content');
foreach (static::$data as $row) {
$sq->values($row['id'], $row['gid'], $row['title'], $row['content']);
}
$sq->execute();
}
public function getMunicipiosByProvincias($provincia, $begin = 1, $end = 11)
{
$query = SphinxQL::create($this->getConnection())->select('municipio')->from('municipios')->match('provincia_normalizada', $provincia)->limit($begin, $end);
$results = $query->execute();
$results = self::check($results);
if (!empty($results)) {
return $results;
} else {
throw new \Exception("No hay municipios");
}
}
public function executeUserSearch($q, $limit)
{
if (empty($q)) {
return array();
}
$query = SphinxQL::create($this->client->getConnection())->select('*')->from('user')->match(array('name'), $q . '*')->limit($limit);
$results = $this->client->query($query);
foreach ($results as &$result) {
$result['id'] = floor($result['id'] / count($this->manager->getIndexes()));
}
return $results;
}
public static function suggest($keyword)
{
$conn = self::getSphinxConnection();
$query = SphinxQL::create($conn)->select('*')->from('mangaindex_suggested')->limit(0, 100);
$keyword = rtrim($keyword, '*') . '*';
$query->match('*', $query->halfEscapeMatch($keyword));
$result = $query->execute();
$suggestions = array();
foreach ($result as $row) {
$suggestions[] = array('value' => $row['keyword']);
}
return $suggestions;
}
public function testMatch()
{
$match = Match::create(self::$sphinxql)->match('test');
$this->assertEquals('test', $match->compile()->getCompiled());
$match = Match::create(self::$sphinxql)->match('test case');
$this->assertEquals('(test case)', $match->compile()->getCompiled());
$match = Match::create(self::$sphinxql)->match(function ($m) {
$m->match('a')->orMatch('b');
});
$this->assertEquals('(a | b)', $match->compile()->getCompiled());
$sub = new Match(self::$sphinxql);
$sub->match('a')->orMatch('b');
$match = Match::create(self::$sphinxql)->match($sub);
$this->assertEquals('(a | b)', $match->compile()->getCompiled());
$match = Match::create(self::$sphinxql)->match('test|case');
$this->assertEquals('test\\|case', $match->compile()->getCompiled());
$match = Match::create(self::$sphinxql)->match(SphinxQL::expr('test|case'));
$this->assertEquals('test|case', $match->compile()->getCompiled());
}
/**
* @param $options (match_fields, paginate)
* @return \Cake\ORM\Query|bool
*/
public function search(array $options)
{
$index = isset($options['index']) ? $options['index'] : $this->config('defaultIndex');
$sphinx = SphinxQL::create($this->conn)->select('id')->from($index)->match(empty($options['match_fields']) ? "*" : $options['match_fields'], $options['term'])->limit(empty($options['limit']) ? 1000 : $options['limit']);
$result = $sphinx->execute()->fetchAllAssoc();
if (!empty($result)) {
$ids = Hash::extract($result, '{n}.id');
$query = $this->_table->find();
if (!empty($options['paginate']['fields'])) {
$query->select($options['paginate']['fields']);
}
if (!empty($options['paginate']['contain'])) {
$query->contain($options['paginate']['contain']);
}
$query->where([$this->_table->alias() . '.' . $this->_table->primaryKey() . ' IN' => $ids]);
return $query;
}
return false;
}
function testAction($cateid)
{
$sphinxConfig = array("host" => "172.17.17.105", "port" => 9020);
$conn = new \Foolz\SphinxQL\Connection();
$conn->setParams(array('host' => $sphinxConfig["host"], 'port' => $sphinxConfig["port"]));
$query = \Foolz\SphinxQL\SphinxQL::create($conn);
$query->select(array("id", "cate3"))->from("product_distri_29");
$query->where("cate3", "=", intval($cateid));
$query->limit(0, 1000);
$query->option("max_matches", 4000);
try {
echo $query->compileSelect()->getCompiled();
echo "\n";
exit;
$sphinxRs = $query->execute();
var_dump($sphinxRs);
exit;
} catch (Exception $e) {
return array();
}
}
public function getBrand($cate1Arr)
{
$max_matches = 50;
$sphinxConfig = $this->di["config"]["gcdprolistsphinx"];
$host = $sphinxConfig->host;
$port = intval($sphinxConfig->port);
$table = $sphinxConfig->table;
$conn = new Connection();
$conn->setParams(array('host' => $host, 'port' => $port));
$query = SphinxQL::create($conn)->select('id', 'tradenum', 'visitnum', 'cid')->from($table);
$query->option('max_matches', $max_matches);
$query->limit(0, 1);
$query->where('is_op', '=', 1);
$query->where('cate1', 'IN', $cate1Arr);
$facet = Facet::create($conn);
$facet->facet('brand', 'cate3');
$facet->limit(0, 50);
$query->facet($facet);
$query = $query->enqueue();
$sphinxRes = $query->executeBatch();
$brandArr = $sphinxRes[1];
$tmpArr = $retArr = array();
foreach ($brandArr as $key => $value) {
if (!isset($tmpArr[$value['brand']]) || isset($tmpArr[$value['brand']]) && $tmpArr[$value['brand']]['count'] < $value['count(*)']) {
$tmpArr[$value['brand']] = array('cate3' => $value['cate3'], 'count' => $value['count(*)'], 'brand' => $value['brand']);
$fieldArr[$value['brand']] = $value['count(*)'];
}
}
array_multisort($fieldArr, SORT_DESC, $tmpArr, SORT_DESC);
if (empty($tmpArr)) {
return array();
}
$retArr = array_slice($tmpArr, 0, 15);
foreach ($retArr as $key => $value) {
$return[$value['brand']] = $value['cate3'];
}
$rs = $this->getBrandInfo($return);
return $rs;
}
/**
* {@inheritdoc }
*/
public function createQueryBuilder()
{
return SphinxQL::create($this);
}
/**
* Gets the search results
*
* @return \Foolz\Foolfuuka\Model\Search The current object
* @throws SearchEmptyResultException If there's no results to display
* @throws SearchRequiresSphinxException If the search submitted requires Sphinx to run
* @throws SearchSphinxOfflineException If the Sphinx server is unreachable
* @throws SearchInvalidException If the values of the search weren't compatible with the domain
*/
protected function p_getSearchComments()
{
$this->profiler->log('Board::getSearchComments Start');
extract($this->options);
// set all empty fields to null
$search_fields = ['boards', 'subject', 'text', 'username', 'tripcode', 'email', 'capcode', 'uid', 'poster_ip', 'filename', 'image', 'deleted', 'ghost', 'filter', 'type', 'start', 'end', 'results', 'order'];
foreach ($search_fields as $field) {
if (!isset($args[$field])) {
$args[$field] = null;
}
}
// populate an array containing all boards that would be searched
$boards = [];
if ($args['boards'] !== null) {
foreach ($args['boards'] as $board) {
$b = $this->radix_coll->getByShortname($board);
if ($b) {
$boards[] = $b;
}
}
}
// search all boards if none selected
if (count($boards) == 0) {
$boards = $this->radix_coll->getAll();
}
// if image is set, get either the media_hash or media_id
if ($args['image'] !== null) {
if (substr($args['image'], -2) !== '==') {
$args['image'] .= '==';
}
// if board is set, retrieve media_id
if ($this->radix !== null) {
try {
$media = $this->media_factory->getByMediaHash($this->radix, $args['image']);
} catch (MediaNotFoundException $e) {
$this->comments_unsorted = [];
$this->comments = [];
$this->profiler->log('Board::getSearchComments Ended Prematurely');
throw new SearchEmptyResultException(_i('No results found.'));
}
$args['image'] = $media->media_id;
}
}
if ($this->radix === null && !$this->preferences->get('foolfuuka.sphinx.global')) {
// global search requires sphinx
throw new SearchRequiresSphinxException(_i('Sorry, this action requires the Sphinx to be installed and running.'));
} elseif ($this->radix === null && $this->preferences->get('foolfuuka.sphinx.global') || $this->radix !== null && $this->radix->sphinx) {
// configure sphinx connection params
$sphinx = explode(':', $this->preferences->get('foolfuuka.sphinx.listen'));
$conn = new SphinxConnnection();
$conn->setParams(['host' => $sphinx[0], 'port' => $sphinx[1], 'options' => [MYSQLI_OPT_CONNECT_TIMEOUT => 5]]);
$conn->silenceConnectionWarning(true);
// establish connection
try {
SphinxQL::forge($conn);
} catch (\Foolz\SphinxQL\ConnectionException $e) {
throw new SearchSphinxOfflineException(_i('The search backend is currently unavailable.'));
}
// determine if all boards will be used for search or not
if ($this->radix == null) {
$indexes = [];
foreach ($boards as $radix) {
if (!$radix->sphinx) {
continue;
}
$indexes[] = $radix->shortname . '_ancient';
$indexes[] = $radix->shortname . '_main';
$indexes[] = $radix->shortname . '_delta';
}
} else {
$indexes = [$this->radix->shortname . '_ancient', $this->radix->shortname . '_main', $this->radix->shortname . '_delta'];
}
// start search query
$query = SphinxQL::forge()->select('id', 'board')->from($indexes);
// parse search params
if ($args['subject'] !== null) {
$query->match('title', $args['subject']);
}
if ($args['text'] !== null) {
if (mb_strlen($args['text'], 'utf-8') < 1) {
return [];
}
$query->match('comment', $args['text'], true);
}
if ($args['username'] !== null) {
$query->match('name', $args['username']);
}
if ($args['tripcode'] !== null) {
$query->match('trip', '"' . $args['tripcode'] . '"');
}
if ($args['email'] !== null) {
$query->match('email', $args['email']);
}
if ($args['capcode'] !== null) {
if ($args['capcode'] === 'user') {
$query->where('cap', ord('N'));
} elseif ($args['capcode'] === 'mod') {
$query->where('cap', ord('M'));
} elseif ($args['capcode'] === 'admin') {
$query->where('cap', ord('A'));
} elseif ($args['capcode'] === 'dev') {
$query->where('cap', ord('D'));
}
}
if ($args['uid'] !== null) {
$query->match('pid', $args['uid']);
}
if ($this->getAuth()->hasAccess('comment.see_ip') && $args['poster_ip'] !== null) {
$query->where('pip', (int) Inet::ptod($args['poster_ip']));
}
if ($args['filename'] !== null) {
$query->match('media_filename', $args['filename']);
}
if ($args['image'] !== null) {
if ($this->radix !== null) {
$query->where('mid', (int) $args['image']);
} else {
$query->match('media_hash', '"' . $args['image'] . '"');
}
}
if ($args['deleted'] !== null) {
if ($args['deleted'] == 'deleted') {
$query->where('is_deleted', 1);
}
if ($args['deleted'] == 'not-deleted') {
$query->where('is_deleted', 0);
}
}
if ($args['ghost'] !== null) {
if ($args['ghost'] == 'only') {
$query->where('is_internal', 1);
}
if ($args['ghost'] == 'none') {
$query->where('is_internal', 0);
}
}
if ($args['filter'] !== null) {
if ($args['filter'] == 'image') {
$query->where('has_image', 0);
}
if ($args['filter'] == 'text') {
$query->where('has_image', 1);
}
}
if ($args['type'] !== null) {
if ($args['type'] == 'sticky') {
$query->where('is_sticky', 1);
}
if ($args['type'] == 'op') {
$query->where('is_op', 1);
}
if ($args['type'] == 'posts') {
$query->where('is_op', 0);
}
}
if ($args['start'] !== null) {
$query->where('timestamp', '>=', intval(strtotime($args['start'])));
}
if ($args['end'] !== null) {
$query->where('timestamp', '<=', intval(strtotime($args['end'])));
}
if ($args['results'] !== null) {
if ($args['results'] == 'op') {
$query->groupBy('thread_num');
$query->withinGroupOrderBy('is_op', 'desc');
}
if ($args['results'] == 'posts') {
$query->where('is_op', 0);
}
}
if ($args['order'] !== null && $args['order'] == 'asc') {
$query->orderBy('timestamp', 'ASC');
} else {
$query->orderBy('timestamp', 'DESC');
}
$max_matches = $this->preferences->get('foolfuuka.sphinx.max_matches', 5000);
// set sphinx options
$query->limit($limit)->offset($page * $limit - $limit >= $max_matches ? $max_matches - 1 : $page * $limit - $limit)->option('max_matches', (int) $max_matches)->option('reverse_scan', $args['order'] === 'asc' ? 0 : 1);
// submit query
try {
$search = $query->execute();
} catch (\Foolz\SphinxQL\DatabaseException $e) {
$this->logger->error('Search Error: ' . $e->getMessage());
throw new SearchInvalidException(_i('The search backend returned an error.'));
}
// no results found
if (!count($search)) {
$this->comments_unsorted = [];
$this->comments = [];
throw new SearchEmptyResultException(_i('No results found.'));
}
$sphinx_meta = Helper::pairsToAssoc(Helper::create($conn)->showMeta()->execute());
$this->total_count = $sphinx_meta['total'];
$this->total_found = $sphinx_meta['total_found'];
// populate sql array for full records
$sql = [];
foreach ($search as $doc => $result) {
$board = $this->radix_coll->getById($result['board']);
$sql[] = $this->dc->qb()->select('*, ' . $result['board'] . ' AS board_id')->from($board->getTable(), 'r')->leftJoin('r', $board->getTable('_images'), 'mg', 'mg.media_id = r.media_id')->where('doc_id = ' . $this->dc->getConnection()->quote($result['id']))->getSQL();
}
$result = $this->dc->getConnection()->executeQuery(implode(' UNION ', $sql))->fetchAll();
} else {
// this is not implemented yet, would require some sort of MySQL search
throw new SearchRequiresSphinxException(_i('Sorry, this board does not have search enabled.'));
}
// no results found IN DATABASE, but we might still get a search count from Sphinx
if (!count($result)) {
$this->comments_unsorted = [];
$this->comments = [];
} else {
// process results
foreach ($result as $key => $row) {
$board = $this->radix !== null ? $this->radix : $this->radix_coll->getById($row['board_id']);
$bulk = new CommentBulk();
$bulk->import($row, $board);
$this->comments_unsorted[] = $bulk;
unset($result[$key]);
}
}
$this->comments[0]['posts'] = $this->comments_unsorted;
return $this;
}
/**
* @covers \Foolz\SphinxQL\SphinxQL::facet
* @covers \Foolz\SphinxQL\SphinxQL::compileSelect
*/
public function testFacet()
{
$this->refill();
// test both setting and not setting the connection
foreach (array(self::$conn, null) as $conn) {
$result = SphinxQL::create(self::$conn)->select()->from('rt')->facet(Facet::create($conn)->facetFunction('INTERVAL', array('gid', 300, 600))->orderByFunction('FACET', '', 'ASC'))->executeBatch()->getStored();
$this->assertArrayHasKey('id', $result[0][0]);
$this->assertArrayHasKey('interval(gid,300,600)', $result[1][0]);
$this->assertArrayHasKey('count(*)', $result[1][0]);
$this->assertEquals('2', $result[1][0]['count(*)']);
$this->assertEquals('5', $result[1][1]['count(*)']);
$this->assertEquals('1', $result[1][2]['count(*)']);
$result = SphinxQL::create(self::$conn)->select()->from('rt')->facet(Facet::create($conn)->facet(array('gid'))->orderBy('gid', 'ASC'))->executeBatch()->getStored();
$this->assertArrayHasKey('id', $result[0][0]);
$this->assertArrayHasKey('gid', $result[1][0]);
$this->assertArrayHasKey('count(*)', $result[1][0]);
$this->assertEquals('1', $result[1][0]['count(*)']);
$this->assertEquals('200', $result[1][0]['gid']);
$this->assertEquals('3', $result[1][2]['count(*)']);
$this->assertEquals('2', $result[1][3]['count(*)']);
}
}
/**
* sphinx搜索实现
*
* @Author tianyunzi
* @DateTime 2015-09-23T14:26:57+0800
* @param [type] $keyword [description]
* @return [type] [description]
*/
public function sphinxHelp($keyword, $page = 1, $limit = 20)
{
$stringObj = new StringHelper();
$keyword = $stringObj->formatKeyword($keyword);
$splitKeyword = $this->splitKeyword($keyword);
$sphinxConfig = $this->di['config']['helpsphinx'];
$conn = new Connection();
$conn->setParams(array('host' => $sphinxConfig->host, 'port' => $sphinxConfig->port));
if (count($splitKeyword) > 1) {
} else {
$sql = "select id from m_article where match('(@articletitle " . $splitKeyword[0] . "),(@content " . $splitKeyword[0] . ")') order by id desc limit " . $limit;
}
$query = SphinxQL::create($conn)->query($sql)->enqueue(Helper::create($conn)->showMeta());
$result = array('data' => array(), 'total_found' => 0, 'time' => 0);
try {
$sphinxRs = $query->executeBatch();
if (is_array($sphinxRs) && count($sphinxRs) > 0) {
$sphinxData = $sphinxRs[0];
$variableData = $sphinxRs[1];
}
if (isset($sphinxData) && is_array($sphinxData)) {
foreach ($sphinxData as $value) {
$result['data'][] = $value['id'];
}
}
if (isset($variableData) && is_array($variableData)) {
foreach ($variableData as $value) {
if ($value['Variable_name'] == 'total_found') {
$result['total_found'] = $value['Value'];
}
if ($value['Variable_name'] == 'time') {
$result['time'] = $value['Value'];
}
}
}
} catch (Exception $e) {
}
$result['data'] = implode(",", array_filter($result['data']));
return $result;
}
/**
* Prepare results for query
*
* @param Mage_CatalogSearch_Model_Fulltext $object
* @param string $queryText
* @param Mage_CatalogSearch_Model_Query $query
* @return Mage_CatalogSearch_Model_Resource_Fulltext
*/
public function prepareResult($object, $queryText, $query)
{
$adapter = $this->_getWriteAdapter();
if (!$query->getIsProcessed()) {
$searchType = $object->getSearchType($query->getStoreId());
$preparedTerms = Mage::getResourceHelper('catalogsearch')->prepareTerms($queryText, $query->getMaxQueryWords());
$bind = array();
$like = array();
$likeCond = '';
if ($searchType == Mage_CatalogSearch_Model_Fulltext::SEARCH_TYPE_LIKE || $searchType == Mage_CatalogSearch_Model_Fulltext::SEARCH_TYPE_COMBINE) {
$helper = Mage::getResourceHelper('core');
$words = Mage::helper('core/string')->splitWords($queryText, true, $query->getMaxQueryWords());
foreach ($words as $word) {
$like[] = $helper->getCILike('s.data_index', $word, array('position' => 'any'));
}
if ($like) {
$likeCond = '(' . join(' OR ', $like) . ')';
}
}
$mainTableAlias = 's';
$fields = array('query_id' => new Zend_Db_Expr($query->getId()), 'product_id');
$select = $adapter->select()->from(array($mainTableAlias => $this->getMainTable()), $fields)->joinInner(array('e' => $this->getTable('catalog/product')), 'e.entity_id = s.product_id', array())->where($mainTableAlias . '.store_id = ?', (int) $query->getStoreId());
if ($searchType == Mage_CatalogSearch_Model_Fulltext::SEARCH_TYPE_FULLTEXT || $searchType == Mage_CatalogSearch_Model_Fulltext::SEARCH_TYPE_COMBINE) {
$bind[':query'] = implode(' ', $preparedTerms[0]);
$where = Mage::getResourceHelper('catalogsearch')->chooseFulltext($this->getMainTable(), $mainTableAlias, $select);
}
if ($likeCond != '' && $searchType == Mage_CatalogSearch_Model_Fulltext::SEARCH_TYPE_COMBINE) {
$where .= ($where ? ' OR ' : '') . $likeCond;
} elseif ($likeCond != '' && $searchType == Mage_CatalogSearch_Model_Fulltext::SEARCH_TYPE_LIKE) {
$select->columns(array('relevance' => new Zend_Db_Expr(0)));
$where = $likeCond;
}
if ($where != '') {
$select->where($where);
}
/*
$sql = $adapter->insertFromSelect($select,
$this->getTable('catalogsearch/result'),
array(),
Varien_Db_Adapter_Interface::INSERT_ON_DUPLICATE);
$adapter->query($sql, $bind);
*/
$conn = new Connection();
$conn->setParams(array('host' => '127.0.0.1', 'port' => 9306));
/** @var SphinxQL $sphinxQuery */
$sphinxQuery = SphinxQL::create($conn)->select(array('*', SphinxQL::expr('WEIGHT()')))->from('fulltext')->option('ranker', 'bm25')->option('field_weights', SphinxQL::expr('(name=7, attributes=3, data_index=1)'))->option('cutoff', 5000)->option('max_matches', 1000)->limit(0, 100)->match('*', $queryText);
$results = $sphinxQuery->execute();
foreach ($results as $result) {
// Ensure we log query results into the Magento table.
$sql = sprintf("INSERT INTO {$this->getTable('catalogsearch/result')} " . " (query_id, product_id, relevance) VALUES " . " (%d, %d, %f) " . " ON DUPLICATE KEY UPDATE relevance = %f", $query->getId(), $result['id'], $result['weight()'] / 1000, $result['weight()'] / 1000);
$this->_getWriteAdapter()->query($sql, $bind);
}
$conn->close();
$query->setIsProcessed(1);
}
return $this;
}
/**
* 功能描述 搜索提示时，根据用户输入关键词，获取企业关键词
* @author 吕小虎
* @datetime ${DATE} ${TIME}
* @version
* @param
* @return
*/
public function getComWordList($keyword, $offset = 0, $limit = 5)
{
//分词
$this->data['split'] = $keyword;
$sphinxConfig = $this->di["config"]["comsearchSphinxKeyword"];
$conn = new Connection();
$indexTable = $sphinxConfig->table;
$conn->setParams(array('host' => $sphinxConfig->host, 'port' => $sphinxConfig->port));
$query = SphinxQL::create($conn);
$query->select(array('id', 'keyword', 'pinyin', 'keycomnum'));
$query->from($indexTable);
$query->match(array('keyword', 'pinyin'), $this->data['split']);
$query->limit($offset, $limit);
// echo $query->compileSelect()->getCompiled();
$result = $query->execute();
return $result;
}
/**
* 功能描述 搜索时，根据用户输入关键词，获取产品关键词列表
* @author 吕小虎
* @datetime ${DATE} ${TIME}
* @version
* @param
* @return
*/
public function getProWordList($keyword, $offset = 0, $limit = 5)
{
//分词
$this->data['split'] = $keyword;
$sphinxConfig = $this->di["config"]["proSphinxKeyword"];
$conn = new Connection();
$indexTable = $sphinxConfig->table;
$conn->setParams(array('host' => $sphinxConfig->host, 'port' => $sphinxConfig->port));
$query = SphinxQL::create($conn);
$query->select(array('id', 'keyword', 'pinyin', 'keypronum'));
$query->from($indexTable);
$query->match(array('keyword', 'pinyin'), $this->data['split']);
$query->option('ranker', 'sph04');
$query->limit($offset, $limit);
$result = $query->execute();
return $result;
}
/**
* Gets the search results
*
* @return \Foolz\FoolFuuka\Model\Search The current object
* @throws SearchEmptyResultException If there's no results to display
* @throws SearchRequiresSphinxException If the search submitted requires Sphinx to run
* @throws SearchSphinxOfflineException If the Sphinx server is unreachable
* @throws SearchInvalidException If the values of the search weren't compatible with the domain
*/
protected function p_getResults()
{
$this->profiler->log('Search::getResults Start');
extract($this->options);
$boards = [];
$input = $this->getUserInput();
if ($this->radix !== null) {
$boards[] = $this->radix;
} elseif ($input['boards'] !== null) {
foreach ($input['boards'] as $board) {
$b = $this->radix_coll->getByShortname($board);
if ($b) {
$boards[] = $b;
}
}
}
// search all boards if none selected
if (count($boards) == 0) {
$boards = $this->radix_coll->getAll();
}
// if image is set, get either the media_hash or media_id
if ($input['image'] !== null && substr($input['image'], -2) !== '==') {
$input['image'] .= '==';
}
if ($this->radix === null && !$this->preferences->get('foolfuuka.sphinx.global')) {
throw new SearchRequiresSphinxException(_i('Sorry, the global search function has not been enabled.'));
}
if ($this->radix !== null && !$this->radix->sphinx) {
throw new SearchRequiresSphinxException(_i('Sorry, this board does not have search enabled.'));
}
$sphinx = explode(':', $this->preferences->get('foolfuuka.sphinx.listen'));
$conn = new SphinxConnnection();
$conn->setParams(['host' => $sphinx[0], 'port' => $sphinx[1], 'options' => [MYSQLI_OPT_CONNECT_TIMEOUT => 5]]);
$indices = [];
foreach ($boards as $radix) {
if (!$radix->sphinx) {
continue;
}
$indices[] = $radix->shortname . '_ancient';
$indices[] = $radix->shortname . '_main';
$indices[] = $radix->shortname . '_delta';
}
// establish connection
try {
$query = SphinxQL::create($conn)->select('id', 'board', 'tnum')->from($indices)->setFullEscapeChars(['\\', '(', ')', '|', '-', '!', '@', '%', '~', '"', '&', '/', '^', '$', '='])->setHalfEscapeChars(['\\', '(', ')', '!', '@', '%', '~', '&', '/', '^', '$', '=']);
} catch (\Foolz\SphinxQL\Exception\ConnectionException $e) {
throw new SearchSphinxOfflineException($this->preferences->get('foolfuuka.sphinx.custom_message', _i('The search backend is currently unavailable.')));
}
// process user input
if ($input['subject'] !== null) {
$query->match('title', $input['subject']);
}
if ($input['text'] !== null) {
if (mb_strlen($input['text'], 'utf-8') < 1) {
return [];
}
$query->match('comment', $input['text'], true);
}
if ($input['username'] !== null) {
$query->match('name', $input['username']);
}
if ($input['tripcode'] !== null) {
$query->match('trip', '"' . $input['tripcode'] . '"');
}
if ($input['email'] !== null) {
$query->match('email', $input['email']);
}
if ($input['capcode'] !== null) {
switch ($input['capcode']) {
case 'user':
$query->where('cap', ord('N'));
break;
case 'mod':
$query->where('cap', ord('M'));
break;
case 'dev':
$query->where('cap', ord('D'));
break;
case 'admin':
$query->where('cap', ord('A'));
break;
}
}
if ($input['uid'] !== null) {
$query->match('pid', $input['uid']);
}
if ($input['country'] !== null) {
$query->match('country', $input['country'], true);
}
if ($this->getAuth()->hasAccess('comment.see_ip') && $input['poster_ip'] !== null) {
$query->where('pip', (int) Inet::ptod($input['poster_ip']));
}
if ($input['filename'] !== null) {
$query->match('media_filename', $input['filename']);
}
if ($input['image'] !== null) {
$query->match('media_hash', '"' . $input['image'] . '"');
}
if ($input['deleted'] !== null) {
switch ($input['deleted']) {
case 'deleted':
$query->where('is_deleted', 1);
break;
case 'not-deleted':
$query->where('is_deleted', 0);
break;
}
}
if ($input['ghost'] !== null) {
switch ($input['ghost']) {
case 'only':
$query->where('is_internal', 1);
break;
case 'none':
$query->where('is_internal', 0);
break;
}
}
if ($input['filter'] !== null) {
switch ($input['filter']) {
case 'image':
$query->where('has_image', 0);
break;
case 'text':
$query->where('has_image', 1);
break;
}
}
if ($input['type'] !== null) {
switch ($input['type']) {
case 'sticky':
$query->where('is_sticky', 1);
break;
case 'op':
$query->where('is_op', 1);
break;
case 'posts':
$query->where('is_op', 0);
break;
}
}
if ($input['start'] !== null) {
$query->where('timestamp', '>=', intval(strtotime($input['start'])));
}
if ($input['end'] !== null) {
$query->where('timestamp', '<=', intval(strtotime($input['end'])));
}
if ($input['results'] !== null && $input['results'] == 'thread') {
$query->groupBy('tnum');
$query->withinGroupOrderBy('is_op', 'desc');
}
if ($input['order'] !== null && $input['order'] == 'asc') {
$query->orderBy('timestamp', 'ASC');
} else {
$query->orderBy('timestamp', 'DESC');
}
$max_matches = $this->preferences->get('foolfuuka.sphinx.max_matches', 5000);
// set sphinx options
$query->limit($limit)->offset($page * $limit - $limit >= $max_matches ? $max_matches - 1 : $page * $limit - $limit)->option('max_matches', (int) $max_matches)->option('reverse_scan', $input['order'] === 'asc' ? 0 : 1);
// submit query
try {
$this->profiler->log('Start: SphinxQL: ' . $query->compile()->getCompiled());
$search = $query->execute();
$this->profiler->log('Stop: SphinxQL');
} catch (\Foolz\SphinxQL\Exception\DatabaseException $e) {
$this->logger->error('Search Error: ' . $e->getMessage());
throw new SearchInvalidException(_i('The search backend returned an error.'));
}
// no results found
if (!count($search)) {
$this->comments_unsorted = [];
$this->comments = [];
throw new SearchEmptyResultException(_i('No results found.'));
}
$sphinx_meta = Helper::pairsToAssoc(Helper::create($conn)->showMeta()->execute());
$this->total_count = $sphinx_meta['total'];
$this->total_found = $sphinx_meta['total_found'];
// populate sql array for full records
$sql = [];
foreach ($search as $doc => $result) {
$board = $this->radix_coll->getById($result['board']);
if ($input['results'] !== null && $input['results'] == 'thread') {
$post = 'num = ' . $this->dc->getConnection()->quote($result['tnum']) . ' AND subnum = 0';
} else {
$post = 'doc_id = ' . $this->dc->getConnection()->quote($result['id']);
}
$sql[] = $this->dc->qb()->select('*, ' . $result['board'] . ' AS board_id')->from($board->getTable(), 'r')->leftJoin('r', $board->getTable('_images'), 'mg', 'mg.media_id = r.media_id')->where($post)->getSQL();
}
$result = $this->dc->getConnection()->executeQuery(implode(' UNION ', $sql))->fetchAll();
// no results found IN DATABASE, but we might still get a search count from Sphinx
if (!count($result)) {
$this->comments_unsorted = [];
$this->comments = [];
} else {
// process results
foreach ($result as $key => $row) {
$board = $this->radix !== null ? $this->radix : $this->radix_coll->getById($row['board_id']);
$bulk = new CommentBulk();
$bulk->import($row, $board);
$this->comments_unsorted[] = $bulk;
unset($result[$key]);
}
}
$this->comments[0]['posts'] = $this->comments_unsorted;
return $this;
}
/**
* Returns a new SphinxQL instance
*
* @return SphinxQL
*/
protected function getSphinxQL()
{
return SphinxQL::create($this->getConnection());
}
/**
* 功能说明：品牌详情页的右侧推荐
*
* @Author yxg
* @DateTime 2015-10-21T11:26:50+0800
* @param [type] $brandid [description]
* @return [type] [description]
*/
public function getBrandRec($brandid)
{
$sphinxConfig = $this->di["config"]["gcdprolistsphinx"];
$host = $sphinxConfig->host;
$port = intval($sphinxConfig->port);
$table = $sphinxConfig->table;
$conn = new Connection();
$conn->setParams(array('host' => $host, 'port' => $port));
$query = SphinxQL::create($conn)->select('id', 'tradenum', 'visitnum', 'cid')->from($table);
$query->option('max_matches', 50);
$query->where('is_op', '=', 1);
if (!empty($brandid)) {
$query->where('brand', '=', $brandid);
}
$query->orderBy('tradenum', 'DESC');
$query = $query->enqueue();
$query->option('max_matches', 50);
$query->select('id', 'tradenum', 'visitnum', 'cid')->from($table);
$query->where('is_op', '=', 1);
if (!empty($brandid)) {
$query->where('brand', '=', $brandid);
}
$query->orderBy('visitnum', 'DESC');
$sphinxRes = $query->executeBatch();
return $sphinxRes;
}
/**
* Create and connect to Sphinx
*/
public function getSphinx()
{
$conn = new Connection();
$conn->setConnectionParams($this->Config->Sphinx->Host, $this->Config->Sphinx->Port);
return SphinxQL::create($conn);
}
/**
* Create and connect to SphinxQL instance
* @since Version 3.9.0
* @return \Foolz\SphinxQL\SphinxQL
*/
public static function getSphinx()
{
$Config = self::getConfig();
$conn = new Connection();
$conn->setParams(array("host" => $Config->Sphinx->Host, "port" => $Config->Sphinx->Port));
return SphinxQL::create($conn);
}
/**
* @covers \Foolz\SphinxQL\SphinxQL::executeBatch
* @covers \Foolz\SphinxQL\SphinxQL::enqueue
* @covers \Foolz\SphinxQL\SphinxQL::getQueue
* @covers \Foolz\SphinxQL\SphinxQL::getQueuePrev
* @covers \Foolz\SphinxQL\SphinxQL::setQueuePrev
*/
public function testQueue()
{
$this->refill();
$result = SphinxQL::create($this->conn)->select()->from('rt')->where('gid', 9003)->enqueue(Helper::create($this->conn)->showMeta())->enqueue()->select()->from('rt')->where('gid', 201)->executeBatch();
$this->assertEquals('10', $result[0][0]['id']);
$this->assertEquals('1', $result[1][0]['Value']);
$this->assertEquals('11', $result[2][0]['id']);
}
public function deleteById($index, $id)
{
return SphinxQL::forge($this->getConnection())->delete()->from('`' . $index . '`')->where('id', $id)->execute();
}
public function search(SphinxQL $query)
{
$query->from($this->getId());
return $this->getClient()->query($query);
}
