OpenCart Kereső Gyorsítás & Javítás Fulltext indexelés + relevancia alapú rendezés – Journal3 kompatibilis

-- Termékleírás tábla: név, leírás, tag mezők indexelése
ALTER TABLE oc_product_description
ADD FULLTEXT ft_search_text (name, description, tag);

-- Termék tábla: azonosítók (cikkszám, SKU, EAN stb.) indexelése
ALTER TABLE oc_product
ADD FULLTEXT ft_search_codes (model, sku, upc, ean, jan, isbn, mpn);

SHOW VARIABLES LIKE 'innodb_ft_min_token_size';

# [mysqld] szekcióba kell beilleszteni
innodb_ft_min_token_size = 2

ALTER TABLE oc_product_description DROP INDEX ft_search_text;
ALTER TABLE oc_product DROP INDEX ft_search_codes;

ALTER TABLE oc_product_description
ADD FULLTEXT ft_search_text (name, description, tag);

ALTER TABLE oc_product
ADD FULLTEXT ft_search_codes (model, sku, upc, ean, jan, isbn, mpn);

catalog/model/catalog/product.php

public function getProducts($data = array()) {
    // Boolean kereső string előkészítése
    $boolean = '';
    if (!empty($data['filter_name'])) {
        $search = trim($data['filter_name']);
        $search = preg_replace('/(\d)(mm|cm|m|kg|g|db|pcs|w|a|v)/i', '$1 $2', $search);
        $words = explode(' ', preg_replace('/\s+/', ' ', $search));
        foreach ($words as $word) {
            $word = preg_replace('/[^a-zA-Z0-9áéíóöőúüűÁÉÍÓÖŐÚÜŰ]/u', '', $word);
            if (strlen($word) >= 3) {
                $boolean .= '+' . $word . '* ';
            }
        }
        $boolean = trim($boolean);
    }

    $sql = "SELECT p.product_id,
        (SELECT AVG(rating) AS total FROM " . DB_PREFIX . "review r1
            WHERE r1.product_id = p.product_id AND r1.status = '1'
            GROUP BY r1.product_id) AS rating,
        (SELECT price FROM " . DB_PREFIX . "product_discount pd2
            WHERE pd2.product_id = p.product_id
            AND pd2.customer_group_id = '" . (int)$this->config->get('config_customer_group_id') . "'
            AND pd2.quantity = '1'
            AND ((pd2.date_start = '0000-00-00' OR pd2.date_start < NOW())
            AND (pd2.date_end = '0000-00-00' OR pd2.date_end > NOW()))
            ORDER BY pd2.priority ASC, pd2.price ASC LIMIT 1) AS discount,
        (SELECT price FROM " . DB_PREFIX . "product_special ps
            WHERE ps.product_id = p.product_id
            AND ps.customer_group_id = '" . (int)$this->config->get('config_customer_group_id') . "'
            AND ((ps.date_start = '0000-00-00' OR ps.date_start < NOW())
            AND (ps.date_end = '0000-00-00' OR ps.date_end > NOW()))
            ORDER BY ps.priority ASC, ps.price ASC LIMIT 1) AS special";

    // Relevancia számítás hozzáadása, ha van keresési kifejezés
    if (!empty($boolean)) {
        $sql .= ", (
            MATCH(pd.name, pd.description, pd.tag)
                AGAINST('" . $boolean . "' IN BOOLEAN MODE) * 5
            +
            MATCH(p.model, p.sku, p.upc, p.ean, p.jan, p.isbn, p.mpn)
                AGAINST('" . $boolean . "' IN BOOLEAN MODE) * 15
        ) AS relevance";
    }

    if (!empty($data['filter_category_id'])) {
        if (!empty($data['filter_sub_category'])) {
            $sql .= " FROM " . DB_PREFIX . "category_path cp LEFT JOIN " . DB_PREFIX . "product_to_category p2c ON (cp.category_id = p2c.category_id)";
        } else {
            $sql .= " FROM " . DB_PREFIX . "product_to_category p2c";
        }
        if (!empty($data['filter_filter'])) {
            $sql .= " LEFT JOIN " . DB_PREFIX . "product_filter pf ON (p2c.product_id = pf.product_id) LEFT JOIN " . DB_PREFIX . "product p ON (pf.product_id = p.product_id)";
        } else {
            $sql .= " LEFT JOIN " . DB_PREFIX . "product p ON (p2c.product_id = p.product_id)";
        }
    } else {
        $sql .= " FROM " . DB_PREFIX . "product p";
    }

    $sql .= " LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id)"
          . " LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id)"
          . " WHERE pd.language_id = '" . (int)$this->config->get('config_language_id') . "'"
          . " AND p.status = '1' AND p.date_available <= NOW()"
          . " AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "'";

    if (!empty($data['filter_category_id'])) {
        if (!empty($data['filter_sub_category'])) {
            $sql .= " AND cp.path_id = '" . (int)$data['filter_category_id'] . "'";
        } else {
            $sql .= " AND p2c.category_id = '" . (int)$data['filter_category_id'] . "'";
        }
        if (!empty($data['filter_filter'])) {
            $implode = array();
            $filters = explode(',', $data['filter_filter']);
            foreach ($filters as $filter_id) {
                $implode[] = (int)$filter_id;
            }
            $sql .= " AND pf.filter_id IN (" . implode(',', $implode) . ")";
        }
    }

    // FULLTEXT keresés ha van elegendő hosszú szó, egyébként LIKE fallback
    if (!empty($boolean)) {
        $sql .= " AND (
            MATCH(pd.name, pd.description, pd.tag)
                AGAINST('" . $boolean . "' IN BOOLEAN MODE)
            OR
            MATCH(p.model, p.sku, p.upc, p.ean, p.jan, p.isbn, p.mpn)
                AGAINST('" . $boolean . "' IN BOOLEAN MODE)
        )";
    } elseif (!empty($data['filter_name']) || !empty($data['filter_tag'])) {
        // Fallback rövid kereséshez (1-2 karakter)
        $sql .= " AND (";
        if (!empty($data['filter_name'])) {
            $implode = array();
            $words = explode(' ', trim(preg_replace('/\s+/', ' ', $data['filter_name'])));
            foreach ($words as $word) {
                $word = $this->db->escape($word);
                $implode[] = "(pd.name LIKE '%$word%' OR pd.tag LIKE '%$word%')";
            }
            if ($implode) {
                $sql .= " " . implode(" AND ", $implode) . "";
            }
            if (!empty($data['filter_description'])) {
                $sql .= " OR pd.description LIKE '%" . $this->db->escape($data['filter_name']) . "%'";
            }
        }
        if (!empty($data['filter_name']) && !empty($data['filter_tag'])) {
            $sql .= " OR ";
        }
        if (!empty($data['filter_tag'])) {
            $implode = array();
            $words = explode(' ', trim(preg_replace('/\s+/', ' ', $data['filter_tag'])));
            foreach ($words as $word) {
                $implode[] = "pd.tag LIKE '%" . $this->db->escape($word) . "%'";
            }
            if ($implode) {
                $sql .= " " . implode(" AND ", $implode) . "";
            }
        }
        if (!empty($data['filter_name'])) {
            $sql .= " OR LCASE(p.model) = '"  . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
            $sql .= " OR LCASE(p.sku)   = '"  . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
            $sql .= " OR LCASE(p.upc)   = '"  . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
            $sql .= " OR LCASE(p.ean)   = '"  . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
            $sql .= " OR LCASE(p.jan)   = '"  . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
            $sql .= " OR LCASE(p.isbn)  = '"  . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
            $sql .= " OR LCASE(p.mpn)   = '"  . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
        }
        $sql .= ")";
    }

    if (!empty($data['filter_manufacturer_id'])) {
        $sql .= " AND p.manufacturer_id = '" . (int)$data['filter_manufacturer_id'] . "'";
    }

    $sql .= " GROUP BY p.product_id";

    $sort_data = array(
        'pd.name',
        'p.model',
        'p.quantity',
        'p.price',
        'rating',
        'p.sort_order',
        'p.date_added'
    );

    // Relevancia alapú rendezés keresés esetén, egyébként az OpenCart eredeti logikája
    if (!empty($boolean)) {
        $sql .= " ORDER BY relevance DESC";
    } elseif (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
        if ($data['sort'] == 'pd.name' || $data['sort'] == 'p.model') {
            $sql .= " ORDER BY LCASE(" . $data['sort'] . ")";
        } elseif ($data['sort'] == 'p.price') {
            $sql .= " ORDER BY (CASE WHEN special IS NOT NULL THEN special WHEN discount IS NOT NULL THEN discount ELSE p.price END)";
        } else {
            $sql .= " ORDER BY " . $data['sort'];
        }
    } else {
        $sql .= " ORDER BY p.sort_order";
    }

    if (isset($data['order']) && ($data['order'] == 'DESC')) {
        $sql .= " DESC, LCASE(pd.name) DESC";
    } else {
        $sql .= " ASC, LCASE(pd.name) ASC";
    }

    if (isset($data['start']) || isset($data['limit'])) {
        if ($data['start'] < 0) {
            $data['start'] = 0;
        }
        if ($data['limit'] < 1) {
            $data['limit'] = 20;
        }
        $sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
    }

    $product_data = array();
    $query = $this->db->query($sql);
    foreach ($query->rows as $result) {
        $product_data[$result['product_id']] = $this->getProduct($result['product_id']);
    }
    return $product_data;
}

SELECT TABLE_NAME, INDEX_NAME, INDEX_TYPE
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = DATABASE()
  AND INDEX_NAME IN ('ft_search_text', 'ft_search_codes');