Using Sphinx search in PrestaShop

PrestaShop have a nice search tool, but sometimes it doesn’t fit all your needs. Also it is not really fast and on large shops search and indexation could be very slow.

If you want something better, you can use a great replacement – Sphinx.


From Wikipedia:

Sphinx is a free software/open source Fulltext search engine designed to provide full-text search functionality to client applications.

Sphinx website – http://sphinxsearch.com/

Sphinx installation

I’ve used Debian 8.1. In other Linux distributions (and especially in Windows) installation may differ slightly.
If Sphinx is available in repos, just install it:

1
2
3
su
apt-get update
apt-get install sphinxsearch

It Sphinx is not available in repos, you can download an installer from official website and install dependencies:

1
2
3
apt-get install mysql-client unixodbc libpq5
wget http://sphinxsearch.com/files/sphinxsearch_2.2.9-release-1~wheezy_i386.deb
dpkg -i sphinxsearch_2.2.9-release-1~wheezy_i386.deb

You have to replace name and url of installer with current relevant versions. They all are available on official website.

Sphinx configuration

Open Sphinx config and edit it:

1
nano /etc/sphinxsearch/sphinx.conf

You need to edit existing default block “source src1” (or add a new block). Result should look like this:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
source PrestaSite
{
type = mysql
sql_host = localhost
# PrestaShop database username
sql_user = DBUSER
# PrestaShop database password
sql_pass = DBPASSWORD
# PrestaShop database name
sql_db = DBNAME
sql_port = 3306 # optional, default is 3306
sql_query_pre = SET NAMES utf8

# MySQL query with list of fields for indexation
sql_query =
SELECT id_product, name, description, description_short
FROM ps_product_lang
}

First you can see the settings for connecting to the database (don’t forget to change them to relevant). Param sql_query contains a query with fields for indexation.

This is all settings from my config. Basically, the config file is well documented and all options are described there, so you could easily customize your configuration.

Next, in the “index definition” block you need to configure index:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
index PrestaSite
{
# data source for indexation
source = PrestaSite

# location for index data
path = /var/lib/sphinxsearch/data/prestasite

# morphology preprocessors
morphology = stem_en

# minimal word length for indexation
min_word_len = 1
}

Next you can see “indexer settings” and “searchd settings” blocks. Usually they are ok, you can just skip them.

So, we’ve configured data source (source PrestaSite) and index (index PrestaSite).

Indexation

Launch indexer in order to index your database:

1
indexer --all

Sphinx launch

Don’t forget to start/restart searchd:

1
searchd

And add indexer to crontab:

1
15 * * * * root indexer --all

Indexation will be launched every hour.

PrestaShop configuration

Below you can see the code for PrestaShop 1.6. For other PrestaShop versions it may differ slightly.

Create (or edit) Search class override:
/override/classes/Search.php

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
<?php
class Search extends SearchCore {
    public static function find($id_lang, $expr, $page_number = 1, $page_size = 1, $order_by = 'position',
        $order_way = 'desc', $ajax = false, $use_cookie = true, Context $context = null)
    {
        if (!$context) {
            $context = Context::getContext();
        }
        $db = Db::getInstance(_PS_USE_SQL_SLAVE_);

        // TODO : smart page management
        if ($page_number < 1) $page_number = 1;
        if ($page_size < 1) $page_size = 1;

        if (!Validate::isOrderBy($order_by) || !Validate::isOrderWay($order_way))
            return false;

        if (!Validate::isOrderBy($order_by) || !Validate::isOrderWay($order_way)) {
            return false;
        }

        $start = ($page_number - 1) * $page_size;

        // Sphinx search, get ids of found products
        $sphinx_results = self::getSphinxResults($expr, $start, $page_size);

        $result = null;
        $total = 0;
        // get products by id if something found
        if (is_array($sphinx_results) AND sizeof($sphinx_results) AND isset($sphinx_results['total']) AND $sphinx_results['total'] > 0) {
            $sql = 'SELECT p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity,
                    pl.`description_short`, pl.`available_now`, pl.`available_later`, pl.`link_rewrite`, pl.`name`,
                    MAX(image_shop.`id_image`) id_image, il.`legend`, m.`name` manufacturer_name, MAX(product_attribute_shop.`id_product_attribute`) id_product_attribute,
                    DATEDIFF(
                        p.`date_add`,
                        DATE_SUB(
                            NOW(),
                            INTERVAL '.(Validate::isUnsignedInt(Configuration::get('PS_NB_DAYS_NEW_PRODUCT')) ? Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20).' DAY
                        )
                    ) > 0 new
                    FROM '._DB_PREFIX_.'product p
                    '.Shop::addSqlAssociation('product', 'p').'
                    INNER JOIN `'._DB_PREFIX_.'product_lang` pl ON (
                        p.`id_product` = pl.`id_product`
                        AND pl.`id_lang` = '.(int)$id_lang.Shop::addSqlRestrictionOnLang('pl').'
                    )
                    LEFT JOIN `'._DB_PREFIX_.'product_attribute` pa ON (p.`id_product` = pa.`id_product`)
                    '.Shop::addSqlAssociation('product_attribute', 'pa', false, 'product_attribute_shop.`default_on` = 1').'
                    '.Product::sqlStock('p', 'product_attribute_shop', false, $context->shop).'
                    LEFT JOIN `'._DB_PREFIX_.'manufacturer` m ON m.`id_manufacturer` = p.`id_manufacturer`
                    LEFT JOIN `'._DB_PREFIX_.'image` i ON (i.`id_product` = p.`id_product`)'.
                Shop::addSqlAssociation('image', 'i', false, 'image_shop.cover=1').'
                    LEFT JOIN `'._DB_PREFIX_.'image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = '.(int)$id_lang.')
                    WHERE p.`id_product` IN('.implode(',', $sphinx_results['results']).')
                    GROUP BY product_shop.id_product';
            $result = $db->executeS($sql);

            // results count
            $total = $sphinx_results['total'];
        }

        if (!$result) {
            $result_properties = array();
        } else {
            $result_properties = Product::getProductsProperties((int)$id_lang, $result);
        }

        return array('total' => $total, 'result' => $result_properties);
    }

    protected static function getSphinxResults($search_query, $page_number, $page_size)
    {
        $results = array();
        $total = 0;

        if(!$search_query)
            return null;

        // connect to Sphinx database
        $link = mysqli_connect('127.0.0.1', '', '', '', '9306');
        if($link)
        {
            $query = 'SELECT * FROM `PrestaSite` WHERE MATCH(\''.pSQL($search_query).'\') limit '.(int)$page_number.', '.(int)$page_size.';';
            if ($result = $link->query($query))
            {
                while($query_results = $result->fetch_array())
                {
                    $results = array_merge($results, $query_results);
                }

                /* clear result */
                $result->close();
            }

            // get count of results
            $query_total = 'SELECT count(*) FROM `PrestaSite` WHERE MATCH(\''.pSQL($search_query).'\');';
            if ($result = $link->query($query_total))
            {
                $total = (int)$result->fetch_array()[0];
                if($total > 1000)
                    $total = 1000;
            }

            mysqli_close($link);
        }

        return array('results' => $results, 'total' => $total);
    }
}

Basically, the code is pretty simple. We override function “find”, in which we replace PrestaShop search by Sphinx search. Sphinx returns product ids and by using those ids we query products data. The second function (getSphinxResults) directly performs the search.

At the end, you need to clear the cache and remove class cache (/cache/class_index.php). All done!

16 thoughts on “Using Sphinx search in PrestaShop”

  1. I work with Sphinx in Prestashop 1.5.6.3, works fine but plan to upgrade to 1.7.

    Anyone has experience with Sphinx in Prestashop1.7. Will the 1.6 code work, or not?

    Reply

    1. This part of code in PrestaShop 1.7 is almost the same as in PS 1.6 , so 1.6 code should work fine.

      Reply


  2. source ab1
    {
    type = mysql
    sql_host = localhost
    sql_user = abc
    sql_pass =123
    sql_db = abc_d
    sql_port = 3306 # optional, default is 3306
    sql_query = SELECT id_product, name, description, description_short FROM ps_product_lang
    sql_query_pre = SET NAMES utf8
    }
    index lang1
    {
    source = ab1
    path = /var/lib/sphinx/lang1
    # morphology preprocessors
    morphology = stem_en
    # minimal word length for indexation
    min_word_len = 1
    }
    index b1rt
    {
    type = rt
    rt_mem_limit = 512M
    path = /var/lib/sphinx/b1rt
    rt_field = title
    rt_field = content
    rt_attr_uint = gid
    }
    indexer
    {
    mem_limit = 512M
    }
    searchd
    {
    listen = 9312
    listen = 9306:mysql41
    log = /var/log/sphinx/searchd.log
    query_log = /var/log/sphinx/query.log
    read_timeout = 5
    max_children = 30
    pid_file = /var/run/sphinx/searchd.pid
    seamless_rotate = 1
    preopen_indexes = 1
    unlink_old = 1
    workers = threads # for RT to work
    binlog_path = /var/lib/sphinx/
    }

    Reply

  3. i checked almost everyday,=^_^=,it worked,cool,but have some bugs like:
    1).Can’t search “A. B” for “A B”;
    2).Just serach by ‘@name’ not include ‘description_short’ and ‘description’
    3).Named “A B 3” can’t be serched whit “A B 3” but can be searched by “A B”
    Thanks a million and happy everyday.=^_^=

    Reply

    1. Well, are you sure that you’re using default Sphinx settings? 🙂
      Yes, I know about @name, but I just thought it can help for preventing from searching wrong products. You can try adding @description_short and @description, maybe it will work better.
      I not sure what else can be changed… Please send me your sphinx.conf, maybe I’ll find something.

      Reply

  4. Hi,
    Would you write “Search.php” for 1.4.2.5,MANY MANY MANY MANY MANY MANY THANKS FOR YOUR TIME.
    Regards,

    Reply

    1. Hi,
      Try this code, it should work fine:

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20
      21
      22
      23
      24
      25
      26
      27
      28
      29
      30
      31
      32
      33
      34
      35
      36
      37
      38
      39
      40
      41
      42
      43
      44
      45
      46
      47
      48
      49
      50
      51
      52
      53
      54
      55
      56
      57
      58
      59
      60
      61
      62
      63
      64
      65
      66
      67
      68
      69
      70
      71
      72
      73
      74
      75
      76
      77
      78
      79
      80
      81
      82
      83
      84
      85
      86
      87
      88
      89
      90
      91
      92
      93
      94
      95
      96
      97
      98
      99
      100
      101
      102
      103
      104
      105
      106
      107
      108
      109
      110
      111
      112
      113
      114
      115
      116
      117
      118
      119
      120
      121
      122
      123
      124
      125
      126
      127
      128
      129
      130
      131
      132
      133
      134
      135
      136
      137
      138
      139
      <?php
      class Search extends SearchCore
      {
          public static function find(
              $id_lang,
              $expr,
              $pageNumber = 1,
              $pageSize = 1,
              $orderBy = 'position',
              $orderWay = 'desc',
              $ajax = false,
              $useCookie = true
          ) {
              global $cookie;
              $db = Db::getInstance(_PS_USE_SQL_SLAVE_);

              // Only use cookie if id_customer is not present
              if ($useCookie) {
                  $id_customer = $cookie->id_customer;
              } else {
                  $id_customer = 0;
              }

              // TODO : smart page management
              if ($pageNumber < 1) {
                  $pageNumber = 1;
              }
              if ($pageSize < 1) {
                  $pageSize = 1;
              }

              if (!Validate::isOrderBy($orderBy) OR !Validate::isOrderWay($orderWay)) {
                  return false;
              }

              // Sphinx search, get ids of found products
              $sphinx_results = self::getSphinxResults($expr, $pageNumber, $pageSize);

              $eligibleProducts = $sphinx_results['results'];

              $score = '';
              $productPool = '';
              foreach ($eligibleProducts AS $id_product) {
                  if ($id_product) {
                      $productPool .= (int)$id_product . ',';
                  }
              }
              if (empty($productPool)) {
                  return ($ajax ? array() : array('total' => 0, 'result' => array()));
              }
              $productPool = ((strpos($productPool,
                      ',') === false) ? (' = ' . (int)$productPool . ' ') : (' IN (' . rtrim($productPool, ',') . ') '));

              if ($ajax) {
                  return $db->ExecuteS('
                  SELECT DISTINCT p.id_product, pl.name pname, cl.name cname,
                      cl.link_rewrite crewrite, pl.link_rewrite prewrite '       . $score . '
                  FROM '       . _DB_PREFIX_ . 'product p
                  INNER JOIN `'       . _DB_PREFIX_ . 'product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = ' . (int)$id_lang . ')
                  INNER JOIN `'       . _DB_PREFIX_ . 'category_lang` cl ON (p.`id_category_default` = cl.`id_category` AND cl.`id_lang` = ' . (int)$id_lang . ')
                  WHERE p.`id_product` '       . $productPool . '
                  ORDER BY position DESC LIMIT 10'      );
              }

              $queryResults = '
              SELECT p.*, pl.`description_short`, pl.`available_now`, pl.`available_later`, pl.`link_rewrite`, pl.`name`,
                  tax.`rate`, i.`id_image`, il.`legend`, m.`name` manufacturer_name '       . $score . ', DATEDIFF(p.`date_add`, DATE_SUB(NOW(), INTERVAL ' . (Validate::isUnsignedInt(Configuration::get('PS_NB_DAYS_NEW_PRODUCT')) ? Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20) . ' DAY)) > 0 new
              FROM '       . _DB_PREFIX_ . 'product p
              INNER JOIN `'       . _DB_PREFIX_ . 'product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = ' . (int)$id_lang . ')
              LEFT JOIN `'       . _DB_PREFIX_ . 'tax_rule` tr ON (p.`id_tax_rules_group` = tr.`id_tax_rules_group`
                                                         AND tr.`id_country` = '       . (int)Country::getDefaultCountryId() . '
                                                         AND tr.`id_state` = 0)
              LEFT JOIN `'       . _DB_PREFIX_ . 'tax` tax ON (tax.`id_tax` = tr.`id_tax`)
              LEFT JOIN `'       . _DB_PREFIX_ . 'manufacturer` m ON m.`id_manufacturer` = p.`id_manufacturer`
              LEFT JOIN `'       . _DB_PREFIX_ . 'image` i ON (i.`id_product` = p.`id_product` AND i.`cover` = 1)
              LEFT JOIN `'       . _DB_PREFIX_ . 'image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = ' . (int)$id_lang . ')
              WHERE p.`id_product` '       . $productPool . '
              '       . ($orderBy ? 'ORDER BY  ' . $orderBy : '') . ($orderWay ? ' ' . $orderWay : '') . '
              LIMIT '       . (int)(($pageNumber - 1) * $pageSize) . ',' . (int)$pageSize;

              $result = $db->ExecuteS($queryResults);
              $total = $db->getValue('SELECT COUNT(*)
              FROM '       . _DB_PREFIX_ . 'product p
              INNER JOIN `'       . _DB_PREFIX_ . 'product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = ' . (int)$id_lang . ')
              LEFT JOIN `'       . _DB_PREFIX_ . 'tax_rule` tr ON (p.`id_tax_rules_group` = tr.`id_tax_rules_group`
                                                         AND tr.`id_country` = '       . (int)Country::getDefaultCountryId() . '
                                                         AND tr.`id_state` = 0)
              LEFT JOIN `'       . _DB_PREFIX_ . 'tax` tax ON (tax.`id_tax` = tr.`id_tax`)
              LEFT JOIN `'       . _DB_PREFIX_ . 'manufacturer` m ON m.`id_manufacturer` = p.`id_manufacturer`
              LEFT JOIN `'       . _DB_PREFIX_ . 'image` i ON (i.`id_product` = p.`id_product` AND i.`cover` = 1)
              LEFT JOIN `'       . _DB_PREFIX_ . 'image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = ' . (int)$id_lang . ')
              WHERE p.`id_product` '       . $productPool);

              if (!$result) {
                  $resultProperties = false;
              } else {
                  $resultProperties = Product::getProductsProperties($id_lang, $result);
              }

              return array('total' => $total, 'result' => $resultProperties);
          }

          protected static function getSphinxResults($search_query, $page_number, $page_size)
          {
              $results = array();
              $total = 0;

              if (!$search_query) {
                  return null;
              }

              // connect to Sphinx database
              $link = mysqli_connect('127.0.0.1', '', '', '', '9306');
              if ($link) {
                  $query = 'SELECT * FROM `PrestaSite` WHERE MATCH(\'' . pSQL($search_query) . '\') LIMIT ' . pSQL($page_number) . ', ' . pSQL($page_size) . ';';
                  if ($result = $link->query($query)) {
                      while ($query_results = $result->fetch_array()) {
                          $results = array_merge($results, $query_results);
                      }

                      /* clear result */
                      $result->close();
                  }

                  // get count of results
                  $query_total = 'SELECT count(*) FROM `PrestaSite` WHERE MATCH(\'' . pSQL($search_query) . '\');';
                  if ($result = $link->query($query_total)) {
                      $total = (int)$result->fetch_array()[0];
                      if ($total > 1000) {
                          $total = 1000;
                      }
                  }

                  mysqli_close($link);
              }

              return array('results' => $results, 'total' => $total);
          }
      }
      Reply

      1. OMG,Thanks a million for your kind help and your time.
        1.Deleted “[0]” at line 128 and worked;
        2.Search “iPod shuffle” but get “iPod Nano”,search “iPod shuff” get “No results…”,tried item’s “name, description, description_short” but result not correct;
        3.I’m using a module called “jbx_menu” and need to disable the “Quick Search block”,could your write it for “jbx_menu”,i will send the “”jbx_menu”” to your email.
        THANKS A MILLION MILLION MILLION MILLION FOR YOUR TIME,YOU’RE THE SUPERMAN!!!

        Reply

        1. 1) Well, actually it shouldn’t work without [0] 🙂 Try to var_dump the $result->fetch_array() at that line and see what you should use.
          2) I not sure what could cause such a problem. Maybe it’s because of some Sphinx settings. Try to re-index your database again.
          3) You mean you want to disable your default search block? You can disable blocksearch module in Back Office or just add this to your global.css:
          #search_block_top {
          display: none;
          }

          Reply

          1. Thanks a million.
            1.Try:
            $total = (int)var_dump($result->fetch_array());
            echo like:
            array(2) { [0]=> string(1) “1” [“count(*)”]=> string(1) “1” }
            array(2) { [0]=> string(1) “0” [“count(*)”]=> string(1) “0” }
            array(2) { [0]=> string(1) “5” [“count(*)”]=> string(1) “5” }
            2.Try:
            $total = (int)$result->fetch_array()[0];
            echo:
            Parse error: syntax error, unexpected ‘[‘ in ~/override/classes/Search.php on line 128
            3.For 3),i mean that the “jbx_menu” module include search function,the same like on your site when i type “cur” and then the search bar echo:
            Modules>Multi Currency PRO
            Modules>Multi Currency
            And i wanna Sphinx enable for “jbx_menu”.=^_^=

            Reply

            1. Ok, try to replace that line with the following code:

              1
              2
              3
              4
              5
              $total = $result->fetch_array();
              if (is_array($total) && isset($total[0]))
                  $total = (int)$total[0];
              else
                  $total = 0;

              Well, as I see jbx_menu uses default PrestaShop search engine, so it should use Sphynx search automatically since you override Search class. Does it work correctly without overriding Search class?

              Reply

              1. Many thanks,now it’s get no error,but:
                1.The search result is still not correct,i have checked the Sphinx query_log like below:
                [Tue Nov 1 21:11:05.683 2016] 0.000 sec 0.000 sec [ext2/0/ext 1 (0,20)] [0t01] ipod
                [Tue Nov 1 21:11:07.742 2016] 0.000 sec 0.000 sec [ext2/0/ext 1 (1,10)] [0t01] AB
                So I wanna try to do like below:
                setMatchMode(SPH_MATCH_PHRASE);
                but don’t know how to do…
                2.The “jbx_menu” can be searched but you need to type the whole word like your site “Multi Currency”,but actually you just need type “cur” and then the search bar will echo suggest:
                Modules>Multi Currency PRO
                Modules>Multi Currency
                I tried to modify the jbx_menu.php at line 208 and 224 (“ENGINE=InnoBD”) and replaced “InnoBD” on “MyISAM”,but no change.
                So sorry for my skill and SOOOOO Sorry to disturb you again.Thanks for your time.
                Regards,

                Reply

                1. Ok, I see…
                  Well, I’ll try to find something that can help.
                  No need to edit jbx_menu.php, it won’t change search settings.
                  Sorry for the delay and don’t worry about disturbing me, no problem 🙂

                  Reply

                    1. Hi Alice,
                      Hope you’re still interested…
                      I’ve made some changes to the previous code, please try it.

                      1
                      2
                      3
                      4
                      5
                      6
                      7
                      8
                      9
                      10
                      11
                      12
                      13
                      14
                      15
                      16
                      17
                      18
                      19
                      20
                      21
                      22
                      23
                      24
                      25
                      26
                      27
                      28
                      29
                      30
                      31
                      32
                      33
                      34
                      35
                      36
                      37
                      38
                      39
                      40
                      41
                      42
                      43
                      44
                      45
                      46
                      47
                      48
                      49
                      50
                      51
                      52
                      53
                      54
                      55
                      56
                      57
                      58
                      59
                      60
                      61
                      62
                      63
                      64
                      65
                      66
                      67
                      68
                      69
                      70
                      71
                      72
                      73
                      74
                      75
                      76
                      77
                      78
                      79
                      80
                      81
                      82
                      83
                      84
                      85
                      86
                      87
                      88
                      89
                      90
                      91
                      92
                      93
                      94
                      95
                      96
                      97
                      98
                      99
                      100
                      101
                      102
                      103
                      104
                      105
                      106
                      107
                      108
                      109
                      110
                      111
                      112
                      113
                      114
                      115
                      116
                      117
                      118
                      119
                      120
                      121
                      122
                      123
                      124
                      125
                      126
                      127
                      128
                      129
                      130
                      131
                      132
                      133
                      134
                      135
                      136
                      137
                      138
                      139
                      <?php
                      class Search extends SearchCore
                      {
                          public static function find(
                              $id_lang,
                              $expr,
                              $pageNumber = 1,
                              $pageSize = 1,
                              $orderBy = 'position',
                              $orderWay = 'desc',
                              $ajax = false,
                              $useCookie = true
                          ) {
                              global $cookie;
                              $db = Db::getInstance(_PS_USE_SQL_SLAVE_);

                              // Only use cookie if id_customer is not present
                              if ($useCookie) {
                                  $id_customer = $cookie->id_customer;
                              } else {
                                  $id_customer = 0;
                              }

                              // TODO : smart page management
                              if ($pageNumber < 1) {
                                  $pageNumber = 1;
                              }
                              if ($pageSize < 1) {
                                  $pageSize = 1;
                              }

                              if (!Validate::isOrderBy($orderBy) OR !Validate::isOrderWay($orderWay)) {
                                  return false;
                              }

                              // Sphinx search, get ids of found products
                              $sphinx_results = self::getSphinxResults($expr, $pageNumber, $pageSize);

                              $eligibleProducts = $sphinx_results['results'];

                              $score = '';
                              $productPool = '';
                              foreach ($eligibleProducts AS $id_product) {
                                  if ($id_product) {
                                      $productPool .= (int)$id_product . ',';
                                  }
                              }
                              if (empty($productPool)) {
                                  return ($ajax ? array() : array('total' => 0, 'result' => array()));
                              }
                              $productPool = ((strpos($productPool,
                                      ',') === false) ? (' = ' . (int)$productPool . ' ') : (' IN (' . rtrim($productPool, ',') . ') '));

                              if ($ajax) {
                                  return $db->ExecuteS('
                                  SELECT DISTINCT p.id_product, pl.name pname, cl.name cname,
                                      cl.link_rewrite crewrite, pl.link_rewrite prewrite '                       . $score . '
                                  FROM '                       . _DB_PREFIX_ . 'product p
                                  INNER JOIN `'                       . _DB_PREFIX_ . 'product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = ' . (int)$id_lang . ')
                                  INNER JOIN `'                       . _DB_PREFIX_ . 'category_lang` cl ON (p.`id_category_default` = cl.`id_category` AND cl.`id_lang` = ' . (int)$id_lang . ')
                                  WHERE p.`id_product` '                       . $productPool . '
                                  ORDER BY p.`id_product` DESC LIMIT 10'                      );
                              }

                              $queryResults = '
                              SELECT p.*, pl.`description_short`, pl.`available_now`, pl.`available_later`, pl.`link_rewrite`, pl.`name`,
                                  tax.`rate`, i.`id_image`, il.`legend`, m.`name` manufacturer_name '                       . $score . ', DATEDIFF(p.`date_add`, DATE_SUB(NOW(), INTERVAL ' . (Validate::isUnsignedInt(Configuration::get('PS_NB_DAYS_NEW_PRODUCT')) ? Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20) . ' DAY)) > 0 new
                              FROM '                       . _DB_PREFIX_ . 'product p
                              INNER JOIN `'                       . _DB_PREFIX_ . 'product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = ' . (int)$id_lang . ')
                              LEFT JOIN `'                       . _DB_PREFIX_ . 'tax_rule` tr ON (p.`id_tax_rules_group` = tr.`id_tax_rules_group`
                                                                         AND tr.`id_country` = '                       . (int)Country::getDefaultCountryId() . '
                                                                         AND tr.`id_state` = 0)
                              LEFT JOIN `'                       . _DB_PREFIX_ . 'tax` tax ON (tax.`id_tax` = tr.`id_tax`)
                              LEFT JOIN `'                       . _DB_PREFIX_ . 'manufacturer` m ON m.`id_manufacturer` = p.`id_manufacturer`
                              LEFT JOIN `'                       . _DB_PREFIX_ . 'image` i ON (i.`id_product` = p.`id_product` AND i.`cover` = 1)
                              LEFT JOIN `'                       . _DB_PREFIX_ . 'image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = ' . (int)$id_lang . ')
                              WHERE p.`id_product` '                       . $productPool . '
                              '                       . ($orderBy ? 'ORDER BY  ' . $orderBy : '') . ($orderWay ? ' ' . $orderWay : '') . '
                              LIMIT '                       . (int)(($pageNumber - 1) * $pageSize) . ',' . (int)$pageSize;

                              $result = $db->ExecuteS($queryResults);
                              $total = $db->getValue('SELECT COUNT(*)
                              FROM '                       . _DB_PREFIX_ . 'product p
                              INNER JOIN `'                       . _DB_PREFIX_ . 'product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = ' . (int)$id_lang . ')
                              LEFT JOIN `'                       . _DB_PREFIX_ . 'tax_rule` tr ON (p.`id_tax_rules_group` = tr.`id_tax_rules_group`
                                                                         AND tr.`id_country` = '                       . (int)Country::getDefaultCountryId() . '
                                                                         AND tr.`id_state` = 0)
                              LEFT JOIN `'                       . _DB_PREFIX_ . 'tax` tax ON (tax.`id_tax` = tr.`id_tax`)
                              LEFT JOIN `'                       . _DB_PREFIX_ . 'manufacturer` m ON m.`id_manufacturer` = p.`id_manufacturer`
                              LEFT JOIN `'                       . _DB_PREFIX_ . 'image` i ON (i.`id_product` = p.`id_product` AND i.`cover` = 1)
                              LEFT JOIN `'                       . _DB_PREFIX_ . 'image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = ' . (int)$id_lang . ')
                              WHERE p.`id_product` '                       . $productPool);

                              if (!$result) {
                                  $resultProperties = false;
                              } else {
                                  $resultProperties = Product::getProductsProperties($id_lang, $result);
                              }

                              return array('total' => $total, 'result' => $resultProperties);
                          }

                          protected static function getSphinxResults($search_query, $page_number, $page_size)
                          {
                              $results = array();
                              $total = 0;

                              if (!$search_query) {
                                  return null;
                              }

                              // connect to Sphinx database
                              $link = mysqli_connect('127.0.0.1', '', '', '', '9306');
                              if ($link) {
                                  $query = 'SELECT * FROM `PrestaSite` WHERE MATCH(\'@name ' . pSQL($search_query) . '\') LIMIT ' . pSQL($page_number) . ', ' . pSQL($page_size) . ';';
                                  if ($result = $link->query($query)) {
                                      while ($query_results = $result->fetch_array()) {
                                          $results = array_merge($results, $query_results);
                                      }

                                      /* clear result */
                                      $result->close();
                                  }

                                  // get count of results
                                  $query_total = 'SELECT count(*) FROM `PrestaSite` WHERE MATCH(\'@name ' . pSQL($search_query) . '\');';
                                  if ($result = $link->query($query_total)) {
                                      $total = (int)$result->fetch_array()[0];
                                      if ($total > 1000) {
                                          $total = 1000;
                                      }
                                  }

                                  mysqli_close($link);
                              }

                              return array('results' => $results, 'total' => $total);
                          }
                      }

Leave a Reply

Your email address will not be published. Required fields are marked *