Own search by hand rutracker.org - implementation on Yii2

CREATE TABLE `categories` ( `id` int(11) NOT NULL AUTO_INCREMENT, `category_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `file_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=37 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; 

 CREATE TABLE `subcategory` ( `id` int(11) NOT NULL AUTO_INCREMENT, `forum_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1239 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; 

 CREATE TABLE `torrents` ( `id` int(11) NOT NULL AUTO_INCREMENT, `forum_id` int(11) DEFAULT NULL, `forum_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `topic_id` int(11) DEFAULT NULL, `hash` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL, `topic_name` text COLLATE utf8_unicode_ci, `size` bigint(20) DEFAULT NULL, `datetime` int(11) DEFAULT NULL, `category_id` int(11) NOT NULL, `forum_name_id` int(11) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `topic_id` (`topic_id`), UNIQUE KEY `hash` (`hash`), KEY `category_torrent_fk` (`category_id`), KEY `torrent_subcat_id` (`forum_name_id`), CONSTRAINT `category_torrent_fk` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `torrent_subcat_id` FOREIGN KEY (`forum_name_id`) REFERENCES `subcategory` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=1635590 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; 

 namespace common\models; use Yii; use yii\helpers\ArrayHelper; use yii\sphinx\ActiveDataProvider; //   use yii\sphinx\ActiveRecord; //   yii2-sphinx /** * This is the model class for index "torrentz". * * @property integer $id * @property string $size * @property string $datetime * @property integer $id_attr * @property integer $size_attr * @property integer $datetime_attr * @property string $topic_name * @property string $topic_id * @property integer $topic_id_attr * @property integer $category_attr * @property string $category_id * @property string $name_attr * @property integer $forum_name_id_attr */ class TorrentSearch extends ActiveRecord { /** * @inheritdoc */ public static function indexName() { return '{{%torrentz}}'; } /** * @inheritdoc */ public function rules() { return [ [['id'], 'required'], [['id'], 'unique'], [['id'], 'integer'], [['id_attr'], 'integer'], [['topic_name', 'topic_id', 'category_id'], 'string'], [['name_attr'], 'string'], [['id', 'size_attr', 'datetime_attr', 'id_attr', 'topic_id_attr', 'category_attr', 'forum_name_id_attr'], 'integer'], [['size', 'datetime', 'topic_name', 'name_attr'], 'string'] ]; } /** * @inheritdoc */ public function attributeLabels() { return [ 'id_attr' => Yii::t('app', 'ID'), 'name_attr' => Yii::t('app', 'Topic Name'), 'id' => Yii::t('app', 'ID'), 'size' => Yii::t('app', 'Size'), 'datetime' => Yii::t('app', 'Datetime'), 'topic_name' => Yii::t('app', 'Topic Name'), 'size_attr' => Yii::t('app', 'Size'), 'datetime_attr' => Yii::t('app', 'Torrent Registered Date'), 'category_attr' => Yii::t('app', 'Category Name'), 'forum_name_id_attr' => Yii::t('app', 'Forum Name'), ]; } /** *    * * @param $params * @return ActiveDataProvider */ public function search($params) { $query = self::find(); $dataProvider = new ActiveDataProvider([ 'query' => $query, ]); $this->load($params); $query->match($this->name_attr); $query->filterWhere(['category_attr' => $this->category_attr]); $query->andFilterWhere(['forum_name_id_attr' => $this->forum_name_id_attr]); $dataProvider->sort = [ 'defaultOrder' => ['category_attr' => SORT_ASC, 'datetime_attr' => SORT_DESC], ]; return $dataProvider; } /** *    (forum_name)    * * @param null|integer $id * @return array */ public static function subsForCat($id = null) { $query = Subcategory::find(); if ($id != null && ($cat = Categories::findOne($id)) !== null) { $subcatsArr = array_keys(self::find() ->where(['category_attr' => $id]) ->groupBy('forum_name_id_attr') ->indexBy('forum_name_id_attr') ->limit(10000) ->asArray() ->all()); $query->andWhere(['id' => $subcatsArr]); } return ArrayHelper::map($query->asArray()->all(), 'id', 'forum_name'); } /** *     ,    * * @param null|integer $id * @return array */ public static function catForSubs($id = null) { $query = Categories::find(); if($id != null && ($subCat = Subcategory::findOne($id)) !== null) { /** @var TorrentSearch $category */ $category = self::find()->where(['forum_name_id_attr' => $id])->one(); $query->andWhere(['id' => $category->category_attr]); } return ArrayHelper::map($query->asArray()->all(), 'id', 'category_name'); } } 

 namespace console\controllers; use common\models\Categories; use common\models\Subcategory; use common\models\Torrents; use Yii; use yii\console\Controller; use yii\helpers\Console; use yii\helpers\VarDumper; /** *      csv- * * Class ImportController * @package console\controllers */ class ImportController extends Controller { public $color = true; /** *  * @return int */ public function actionIndex() { $this->stdout("Default: import/import [file_path]. \nDefault file path is frontend/runtime/csv\n\n"); return Controller::EXIT_CODE_NORMAL; } /** *    * * @param string $path * @return int */ public function actionImport($path = 'frontend/runtime/csv') { $fullPath = Yii::getAlias('@' . $path); if(!is_dir($fullPath)) { $this->stderr("Path '{$fullPath}' not found\n", Console::FG_RED); return Controller::EXIT_CODE_ERROR; } if(is_file($fullPath . DIRECTORY_SEPARATOR . 'category_info.csv')) $categories = $this->importCategories($fullPath); else { $this->stderr("File 'category_info.csv' not found\n", Console::FG_RED); return Controller::EXIT_CODE_ERROR; } if($categories === false) { $this->stderr("Categories is NOT imported", Console::FG_RED); return Controller::EXIT_CODE_ERROR; } /** @var Categories $cat */ foreach ($categories as $cat) { if(!is_file($fullPath . DIRECTORY_SEPARATOR . $cat->file_name)) continue; $this->importTorrents($cat, $path); } return Controller::EXIT_CODE_NORMAL; } /** *   * * @param \common\models\Categories $cat * @param $path */ private function importTorrents(Categories $cat, $path) { $filePath = Yii::getAlias('@' . $path . DIRECTORY_SEPARATOR . $cat->file_name); $row = 0; if (($handle = fopen($filePath, "r")) !== FALSE) { while (($data = fgetcsv($handle, 0, ";")) !== FALSE) { $row++; $model = Torrents::findOne(['forum_id' => $data[0], 'topic_id' => $data[2]]); if($model !== null) continue; // Subcategory $subcat = $this->importSubcategory($data[1]); if(!($subcat instanceof Subcategory)) { $this->stderr("Error! Unable to import subcategory!"); $this->stdout("\n"); continue; } $this->stdout("Row {$row} of category \"{$cat->category_name}\" "); $this->stdout("and subcategory \"{$subcat->forum_name}\": \n"); if($model === null) { if(isset($data[4])) $data[4] = str_replace('\\', '/', $data[4]); //   ,   ,       //          , //    ,    if(!isset($data[0]) || !isset($data[1]) || !isset($data[2]) || !isset($data[3]) || !isset($data[4]) || !isset($data[5]) || !isset($data[6])) { $this->stderr("Error! Undefined Field!\n", Console::FG_RED); \yii\helpers\VarDumper::dump($data); $this->stdout("\n"); continue; } $model = new Torrents([ 'forum_id' => $data[0], 'forum_name' => $data[1], 'topic_id' => $data[2], 'hash' => $data[3], 'topic_name' => $data[4], 'size' => $data[5], 'datetime' => strtotime($data[6]), 'category_id' => $cat->id, ]); } $model->forum_name_id = $subcat->id; if($model->save()) { $this->stdout("Torrent \t"); $this->stdout($model->topic_name, Console::FG_YELLOW); $this->stdout(" added\n"); } $this->stdout("\n"); } } } /** *   (forum_name) * * @param string $subcat_name * @return bool|Subcategory */ private function importSubcategory($subcat_name) { $model = Subcategory::findOne(['forum_name' => $subcat_name]); if($model === null) $model = new Subcategory(['forum_name' => $subcat_name]); if($model->save()) return $model; else { VarDumper::dump($model->errors); } return false; } /** *   * * @param $path * @return array|\yii\db\ActiveRecord[] */ private function importCategories($path) { $file = $path . DIRECTORY_SEPARATOR . 'category_info.csv'; $row = 1; if (($handle = fopen($file, "r")) !== FALSE) { while (($data = fgetcsv($handle, 0, ";")) !== FALSE) { $row++; $this->stdout("Row " . $row . ":\n"); $model = Categories::findOne($data[0]); if($model === null) { $model = new Categories([ 'id' => $data[0], 'category_name' => $data[1], 'file_name' => $data[2] ]); } if($model->save()) $this->stdout("Category {$model->id} with name '{$model->category_name}' imported\n"); $this->stdout("\n"); } } else return false; return Categories::find()->all(); } } 

 source torrentz { type = mysql sql_host = localhost sql_user = webmaster #   MySQL sql_pass = webmaster #   MySQL sql_db = rutracker #      sql_port = 3306 sql_query_pre = SET NAMES utf8 sql_query_pre = SET CHARACTER SET utf8 sql_query = SELECT id, id AS id_attr, \ size, size AS size_attr, \ datetime, datetime as datetime_attr, \ topic_name, topic_name AS name_attr, \ topic_id, topic_id AS topic_id_attr, \ category_id, category_id AS category_attr, \ forum_name_id, forum_name_id AS forum_name_id_attr \ FROM torrents sql_attr_string = name_attr sql_attr_uint = id_attr sql_attr_uint = size_attr sql_attr_uint = datetime_attr sql_attr_uint = topic_id_attr sql_attr_uint = category_attr sql_attr_uint = forum_name_id_attr } index torrentz { source = torrentz path = /var/lib/sphinxsearch/data/ docinfo = extern morphology = stem_enru min_word_len = 2 charset_table = 0..9, A..Z->a..z, _, a..z, U+410..U+42C->U+430..U+44C, U+42E..U+42F->U+44E..U+44F, U+430..U+44C, U+44E..U+44F, U+0401->U+0435, U+0451->U+0435, U+042D->U+0435, U+044D->U+0435 min_infix_len = 2 } indexer { mem_limit = 512M } searchd { listen = 0.0.0.0:9306:mysql41 log = /var/log/sphinxsearch/searchd.log query_log = /var/log/sphinxsearch/query.log read_timeout = 5 max_children = 30 pid_file = /var/run/sphinxsearch/searchd.pid }