How to complete the search for goods on sku opencart?

Question

opencart

There is a store on opencart 2.3.0.2 (rs.4).
The product card displays a table of product variations. Each variation has an article and a price, but this is implemented not by the product options functionality, but by creating a separate oc_product_prices table in the database with the product_id and data columns (a serialized array of the form a:2:{s:5:"names";a:4 :{i:0;s:14:"Article";i:1;s:8:"Price";i:2;s:12:"Discount";i:3;s:20:"Diameter, mm ";}s:6:"values";a:9:{i:0;a:4:{i:0;s:8:"SLTFM120";i:1;s:4:"2.61";i :2;s:1:"1";i:3;s:2:"20";}i:1;a:4:{i:0;s:8:"SLTFM125";i:1;s :4:"3.86";i:2;s:1:"1";i:3;s:2:"25";}}} ).
That is, now in the table in which the search is in progress - oc_product, the sku column is empty. How can I properly modernize the opencarta search by article? I found the product.php file which is responsible for generating a query to the database, but I don't quite understand everything there.
Can anyone suggest a solution to this problem?
Or at least explain this line of code:
SELECT price FROM " . DB_PREFIX . "product_special ps WHERE ps.product_id = p.product_id - what is this ps line?
Here is the function that is responsible for the search:

public function getProducts($data = array()) {
    $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";

    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) . ")";
      }
    }

    if (!empty($data['filter_name']) || !empty($data['filter_tag'])) {
      $sql .= " AND (";

      if (!empty($data['filter_name'])) {
        $implode = array();

        $words = explode(' ', trim(preg_replace('/\s+/', ' ', $data['filter_name'])));

        foreach ($words as $word) {
          $implode[] = "pd.name LIKE '%" . $this->db->escape($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'
    );

    if (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;
  }

I will be grateful for any help. I didn’t make the site, so don’t ask why the price and article were put in the hotel table)

Answer 1

First you need to convert the data from serialized to normal so that you can join tables and make a selection with a simple LIKE '%value%'
Well, you can use a serialized string with LIKE '%value%', but it seems to me that this is not entirely correct