# Search Own MySQL FULLTEXT index table, three relevance levels, one row per record **per language**. ## The index table [`models/SearchIndex.php`](../../models/SearchIndex.php) extends `ActiveRecord`: | Column | Meaning | |--------|---------| | `language` | 2 chars, one row per record per language in `APP_LANGUAGES` | | `modelClass` | FQCN of the indexed model | | `modelId` | its `id` | | `dataLevel1` | highest relevance (weight ×3) | | `dataLevel2` | medium (×2) | | `dataLevel3` | lowest (×1) | Indexed on `language`, `modelClass`, `modelId` and the pair `[ modelClass, modelId ]`. **The three FULLTEXT indexes are created outside the schema helper.** `initData()` (`models/SearchIndex.php:83`) runs raw `ALTER TABLE … ADD FULLTEXT INDEX` for each `dataLevelN` and then does a full build. `getIndexes()` cannot express a FULLTEXT index, so a table created without `initData()` running has no full-text indexes and every search returns nothing. ## Making a model searchable Implement [`SearchableInterface`](../../models/SearchableInterface.php): ```php public function getSearchIndexData() : array; // [ level1, level2, level3 ] public function isSearchable() : bool; // usually: published && has content public function renderSearchResult() : string; // the markup on the results page ``` Currently implemented by **five models**: `Article`, `Content`, `Location`, `Service`, `Storage`, `StorageType`. That is it — no registration list. `ActiveRecord::afterSave()` checks `instanceof SearchableInterface` and `SearchIndex::build()` discovers implementors by scanning `models/*.php`. ## Indexing Automatic, on every save ([`db/ActiveRecord.php:309`](../../db/ActiveRecord.php)): ```php if( $this instanceof SearchableInterface ) { if( $this->isSearchable() ) SearchIndex::index( $this ); else SearchIndex::remove( $this ); } ``` `SearchIndex::index()` (`:123`) loops over **every** language: clones the model, `translate()`s it to that language, then upserts the row for that `(modelClass, modelId, language)` triple. So one save writes three index rows. `SearchIndex::remove()` deletes all language rows and returns whether anything was deleted. ### Rebuilding `./yii search-index/build` → `SearchIndex::build()` (`:167`): 1. `glob( models/*.php )`, instantiate each class, skip any that throws; 2. for each `SearchableInterface`, iterate `find()->each()`; 3. index or remove depending on `isSearchable()`; 4. return a per-class `{ indexed, removed }` log, printed by `consoleLog()`. Needed after anything that changes data without going through ActiveRecord: imports, raw SQL, an AI translation run, or adding a language. ## Searching `SearchIndex::search( $keyword, $page, $perPage )` (`:230`). The keyword is normalized by [`StringHelper::relevancyKeyword()`](../../helpers/StringHelper.php): boolean-mode operators (`- + * ~ " ' ( ) < > , ; . $ # |`) are stripped, whitespace collapsed, **words shorter than 3 characters are dropped entirely**, and each surviving word gets a `*` suffix. `"a bt"` normalizes to `"*"` — nothing usable — and the search short-circuits to an empty result. Relevance is computed in SQL: ```sql ( MATCH( dataLevel1 ) AGAINST ( '' IN BOOLEAN MODE ) * 3 ) + ( MATCH( dataLevel2 ) AGAINST ( '' IN BOOLEAN MODE ) * 2 ) + ( MATCH( dataLevel3 ) AGAINST ( '' IN BOOLEAN MODE ) * 1 ) ``` used both in the `SELECT` (aliased `relevancy`) and in the `WHERE` (`> 0`), filtered to `language = Yii::$app->language`, ordered by relevancy descending, paginated. Results are `SearchIndex` rows; `getModel()` resolves the real record through `call_user_func( [ $this->modelClass, 'findOne' ], $this->modelId )`, and `renderSearchResult()` on that model produces the markup. ## Storage search is a different thing The filterable storage listing is **not** this index. It is [`services/StorageSearchService.php`](../../services/StorageSearchService.php) (478 LOC), a normal query builder over `Storage` with cascading filters and AJAX responses. See [STORAGE-AND-LOCATIONS.md](STORAGE-AND-LOCATIONS.md). The two share no code. ## Traps 1. **`$k` is interpolated into the SQL string**, not bound. It reaches the query through `relevancyKeyword()`, which strips quotes and every boolean operator — that sanitization is the only thing standing between user input and the query. Do not weaken it. 2. **Short words are unfindable.** Under 3 characters, the word is dropped before it reaches MySQL. MySQL's own `ft_min_word_len` applies on top of that. 3. **`getModel()` returns `null` for a deleted record** — index rows are only removed through `SearchIndex::remove()`, so a record deleted outside ActiveRecord leaves orphan rows. 4. **Three rows per record.** A model with a large `dataLevel1` triples its storage cost. 5. **`build()` instantiates every model class in `models/*.php`.** A constructor with side effects, or one that needs arguments, will be silently skipped by the `catch( Throwable )`.