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]);
}
}