Query provides a set of methods to facilitate the specification of different clauses
in a SELECT statement. These methods can be chained together.
By calling Query::createCommand, we can get a Command instance which can be further
used to perform/execute the Sphinx query.
For example,
php
$query = new Query();
$query->select('id, group_id')
->from('idx_item')
->limit(10);
build and execute the query
$command = $query->createCommand();
$command->sql returns the actual SQL
$rows = $command->queryAll();
Since Sphinx does not store the original indexed text, the snippets for the rows in query result
should be build separately via another query. You can simplify this workflow using [[snippetCallback]].
Warning: even if you do not set any query limit, implicit LIMIT 0,20 is present by default!
public function search($q) { $query = new Query(); $rows = $query->from(array_values($this->indexs))->match($q)->showMeta(true)->search(); $models = []; /** @var \XSDocument $item */ foreach ($rows["hits"] as $item) { $models[] = $item["entity"]::findOne($item["id"]); } return $models; }
public function actionSearch($title) { $query = new Query(); $result = $query->from('apps_zh')->match($title)->all(); var_dump($result); exit; $query = DictZhXs::find(); $result = $query->where($title)->limit(10)->all(); Yii::info($result); return $this->render('search', ['query' => $title, 'models' => $result]); }
public function testQuery() { $query = new Query(); $query->from('yii2_test_article_index'); $provider = new ActiveDataProvider(['query' => $query, 'db' => $this->getConnection()]); $models = $provider->getModels(); $this->assertEquals(2, count($models)); $provider = new ActiveDataProvider(['query' => $query, 'db' => $this->getConnection(), 'pagination' => ['pageSize' => 1]]); $models = $provider->getModels(); $this->assertEquals(1, count($models)); }
/** * Возвращает запрос к сфинксу * @return \yii\sphinx\Query * @throws \yii\base\InvalidConfigException */ public function getSphinxQuery() { if ($this->_sphinxQuery === null) { $this->_sphinxQuery = \Yii::createObject(\yii\sphinx\Query::className()); $this->_sphinxQuery->from($this->index)->options(array_merge(["ranker" => "wordcount"], $this->options)); } return $this->_sphinxQuery; }
/** * Generates a SELECT SQL statement from a [[Query]] object. * @param Query $query the [[Query]] object from which the SQL statement will be generated * @param array $params the parameters to be bound to the generated SQL statement. These parameters will * be included in the result with the additional parameters generated during the query building process. * @return array the generated SQL statement (the first array element) and the corresponding * parameters to be bound to the SQL statement (the second array element). The parameters returned * include those provided in `$params`. */ public function build($query, $params = []) { $params = empty($params) ? $query->params : array_merge($params, $query->params); if ($query->match !== null) { if ($query->match instanceof Expression) { $query->andWhere('MATCH(' . $query->match->expression . ')'); $params = array_merge($params, $query->match->params); } else { $phName = self::PARAM_PREFIX . count($params); $params[$phName] = $this->db->escapeMatchValue($query->match); $query->andWhere('MATCH(' . $phName . ')'); } } $from = $query->from; if ($from === null && $query instanceof ActiveQuery) { /** @var ActiveRecord $modelClass */ $modelClass = $query->modelClass; $from = [$modelClass::indexName()]; } $clauses = [$this->buildSelect($query->select, $params, $query->distinct, $query->selectOption), $this->buildFrom($from, $params), $this->buildWhere($query->from, $query->where, $params), $this->buildGroupBy($query->groupBy), $this->buildWithin($query->within), $this->buildOrderBy($query->orderBy), $this->buildLimit($query->limit, $query->offset), $this->buildOption($query->options, $params)]; return [implode($this->separator, array_filter($clauses)), $params]; }
/** * @depends testRun * * @see https://github.com/yiisoft/yii2/issues/4375 */ public function testRunOnDistributedIndex() { $connection = $this->getConnection(); $query = new Query(); $rows = $query->from('yii2_test_distributed')->match('about')->options(['cutoff' => 50, 'field_weights' => ['title' => 10, 'content' => 3]])->all($connection); $this->assertNotEmpty($rows); }
/** * Initializes the object. * This method is called at the end of the constructor. The default implementation will trigger * an [[EVENT_INIT]] event. If you override this method, make sure you call the parent implementation at the end * to ensure triggering of the event. */ public function init() { parent::init(); $this->trigger(self::EVENT_INIT); }
/** * Executes query and returns a single row of result. * @param Connection $db the DB connection used to create the DB command. * If null, the DB connection returned by [[modelClass]] will be used. * @return ActiveRecord|array|null a single row of query result. Depending on the setting of [[asArray]], * the query result may be either an array or an ActiveRecord object. Null will be returned * if the query results in nothing. */ public function one($db = null) { $row = parent::one($db); if ($row !== false) { $models = $this->populate([$row]); return reset($models) ?: null; } else { return null; } }
/** * @return int */ public function actionIle() { $query = new Query(); $rows = $query->from('lproduct')->match(new Expression(':match', ['match' => '@(description)' . \Yii::$app->sphinx->escapeMatchValue('база макияж под')]))->all(); print_r($rows); print_r(IledebeauteProduct::findOne(['id' => $rows[0]['id']])->toArray()); die; return $rows; }
/** * @inheritdoc */ protected function prepareTotalCount() { if (!$this->query instanceof Query) { throw new InvalidConfigException('The "query" property must be an instance "' . Query::className() . '" or its subclasses.'); } if (!empty($this->query->showMeta)) { $meta = $this->getMeta(); if (isset($meta['total_found'])) { return (int) $meta['total_found']; } if (isset($meta['total'])) { return (int) $meta['total']; } } $query = clone $this->query; return (int) $query->limit(-1)->offset(-1)->orderBy([])->facets([])->showMeta(false)->count('*', $this->db); }
/** * Constructor. * @param array $modelClass the model class associated with this query * @param array $config configurations to be applied to the newly created query object */ public function __construct($modelClass, $config = []) { $this->modelClass = $modelClass; parent::__construct($config); }
/** * @depends testMatchSpecialCharValue */ public function testMatchComplex() { $connection = $this->getConnection(); $query = new Query(); $rows = $query->from('yii2_test_article_index')->match(new Expression(':match', ['match' => '@(content) ' . $connection->escapeMatchValue('about\\"')]))->all($connection); $this->assertNotEmpty($rows); }
public function testCount() { $connection = $this->getConnection(); $query = new Query(); $count = $query->from('yii2_test_article_index')->match('about')->count('*', $connection); $this->assertEquals(2, $count); }
public static function getLastTorrentsDataProvider($count = 5, $forceRefresh = false) { $cacheKey = 'browse_torrents'; $torrentsByTags = Yii::$app->cache->get($cacheKey); if (!$torrentsByTags) { $torrentsByTags = []; $torrentsIds = []; $tags = Category::$categoriesTags; foreach ($tags as $tag) { $query = new Query(); $rows = $query->from(static::indexName())->match(new Expression(':match', [':match' => '@tags ' . Yii::$app->sphinx->escapeMatchValue($tag)]))->where(['deleted' => 0])->orderBy(['weight()' => SORT_DESC, 'id' => SORT_DESC])->limit($count)->addOptions(['ranker' => 'SPH04', 'max_matches' => 5]); $torrents = array_map(function ($item) { return $item['id']; }, $rows->all()); $torrentsIds = array_merge($torrentsIds, $torrents); } if (empty($torrentsIds)) { Yii::log('Empty last torrents ids', CLogger::LEVEL_WARNING); } else { $query = (new Torrent())->find()->with('scrapes')->where(['id' => $torrentsIds]); foreach ($query->all() as $torrent) { $torrentsByTags[Category::getTorrentCategoryTag($torrent)][] = $torrent; } } Yii::$app->cache->set($cacheKey, $torrentsByTags, 600); } $result = array_map(function ($data) { return new ArrayDataProvider(['key' => 'id', 'allModels' => $data]); }, $torrentsByTags); return $result; }
/** * Выборка для случайного концерта * @return string */ function actionConcert() { if ($user = Yii::$app->getRequest()->getQueryParam('user')) { if (!$user) { return 'Доступ запрещн!'; } // $user = Yii::$app->getRequest()->getQueryParam('user'); if (Items::find()->where(['is_next' => 1])->one()) { $item = Items::find()->where(['is_next' => 1])->one(); $song_id = $item->id; } else { $song_id = 100; } $songs = []; //return $song_id; $songs1 = Items::find()->where("(source_id = 6 or source_id = 40) and id < {$song_id} and published = 1")->limit(5)->orderBy('id DESC')->all(); $songs2 = Items::find()->where("(source_id = 19 or source_id = 41) and id < {$song_id} and published = 1")->limit(5)->orderBy('id DESC')->all(); $songs3 = Items::find()->where("source_id = 38 and id < {$song_id} and published = 1")->limit(5)->orderBy('id DESC')->all(); $songs4 = Items::find()->where("(source_id = 29 or source_id = 326 or source_id = 526) and id < {$song_id} and published = 1")->limit(1)->orderBy('id DESC')->all(); $songs = array_merge($songs1, $songs2, $songs3, $songs4); shuffle($songs); //return var_dump($songs); //$choosen_songs = []; foreach ($songs as $song) { try { $items_records = []; $tags = explode(',', $song->tags); $rand_tag = $tags[mt_rand(0, count($tags) - 1)]; $this->rand_tag[] = $rand_tag; $query = new Query(); // $search_result = $query_search->from('siteSearch')->match($q)->all(); // поиск осуществляется по средством метода match с переданной поисковой фразой. $query_items_ids = $query->from('items')->match($rand_tag)->all(); //return var_dump($query_items_ids); foreach ($query_items_ids as $arr_item_rec) { foreach ($arr_item_rec as $id) { $item = Items::findOne((int) $id); if ($item->cat_id == 93 || $item->cat_id == 104 || $item->cat_id == 105 || $item->cat_id == 143 || $item->cat_id == 136) { $items_records[] = Items::findOne((int) $id); } } } //return var_dump($items_records[mt_rand(0,count($items_records)-1)]->text); if (empty($items_records)) { continue; } $rand_index = mt_rand(0, count($items_records) - 1); $song->phrase = $items_records[$rand_index]->text; if (isset($items_records[$rand_index + 1]) && $song->phrase != $song->phrase2) { $song->phrase2 = $items_records[$rand_index + 1]->text; } else { $song->phrase2 = $items_records[0]->text; } //return $song->phrase.'<br>'.$song->phrase2; //return var_dump($song); $this->choosen_songs[] = $song; } catch (\ErrorException $e) { return $e->getMessage(); } } //exit; //return var_dump($this->choosen_songs); return $this->renderPartial('concert', ['songs' => $this->choosen_songs, 'tags' => $this->rand_tag]); } }