0

The end user needs to be able to search for products using a new field and with data coming from a new table.

I added a new string ID field to table catalog_product_entity and created a new table catalog_product_oems using db_schema.xml. A product can have multiple oem entities.

I've tried creating a new plugin for CatalogSearch IndexBuilder where I get the product ids for products where the search query matches oems and the new product ID field:

...

$select = $proceed($request);
$select->where("search_index.entity_id IN ('" . join("','", $additionalProductIds) . "')");
return $select;

However this did not work, since the select query is later expanded:

SELECT `main_select`.`entity_id`, SUM(score) AS `relevance` FROM (
    SELECT DISTINCT  `eav_index`.`entity_id`, (((0)) * 1 + LEAST((MATCH (data_index) AGAINST ('WIX*' IN BOOLEAN MODE)), 1000000) * POW(2, search_weight)) AS `score` FROM `catalog_product_index_eav` AS `eav_index`
    INNER JOIN `catalogsearch_fulltext_scope1` AS `search_index` ON eav_index.entity_id = search_index.entity_id
    INNER JOIN `catalog_eav_attribute` AS `cea` ON search_index.attribute_id = cea.attribute_id
    INNER JOIN `cataloginventory_stock_status` AS `stock_index` ON stock_index.product_id = eav_index.entity_id AND `stock_index`.`website_id` = 0 AND `stock_index`.`stock_status` = 1 AND `stock_index`.`stock_id` = 1 

    WHERE (eav_index.store_id = '1') 
    AND (`eav_index`.`attribute_id` = 99 
    AND `eav_index`.`value` in ('3', '4') 
    AND `eav_index`.`store_id` = '1') 

    /* This is what I'm bringing in */
    AND (search_index.entity_id IN ('1','2','3')) 

    /* AND should be OR for this to work... */
    AND (MATCH (data_index) AGAINST ('WIX*' IN BOOLEAN MODE))
) AS `main_select` GROUP BY `entity_id` ORDER BY `relevance` DESC, `entity_id` DESC
LIMIT 10000

Am I approaching this from a wrong angle?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.