Yii2 Directory search?

Question

Yii

Hello. There is something like a directory. 3 tables - catalog(catalogue), section(sections), product(products). Section is associated with catalog and product is associated with section. Through gii generated everything.
The site needs a search. Please tell me the search algorithm. After all, users can enter Laptops(catalog) or Laptops Acer(catalog + section) or Acer(section) or Monitor Acer XXX(catalog+section+product) or XXX(product).
If, for example, an Acer Notebook was entered, then it would receive id_catalog via catalog_name LIKE '%Ноутбук%'. Then would get section.id_section matches when

catalog_id = '$catalog' AND section_name LIKE '%Acer%'

. Well, knowing id_section to find product is a matter of one request.
But the user can enter anything. Therefore, I can not come up with an algorithm. I will be glad to any advice.
Thanks in advance.

Answer 1

In general, for good, you need a search engine, such as sphinx or elasticsearch.
Well, if you need a simple crutch, then:
create a myisam table search_table with the fields name, type, id. In name, put the name (of directories, sections, products), in type, the actual type of the entity - (catalog, section, product), in id - the ID of the entity in its native table. Next, add a full-text index to name, and already search by it, and already process the search result (if a catalog or section, then display all products from them, if a product, then show it). And of course, keep the search_table table up-to-date when any entity is created/edited/deleted.

Answer 2

class ProductSearch extends Product
{
..........
public function search($params)
{
$query = Product::find();
$dataProvider = new ActiveDataProvider([
'query' => $query,
]);
$query->joinWith(['section' => function($query) { $query->from(['section' => 'section']); }]);
$query->joinWith(['catalog' => function($query) { $query->from(['catalog' => 'catalog']); }]);
..........
$search_array = explode(' ', $this->search_string);
$query->andFilterWhere(['OR LIKE', 'catalog.catalog_name', $search_array])
$query->andFilterWhere(['OR LIKE', 'section.section_name', $search_array])
...........
}
}