{"id":182,"date":"2016-02-29T11:43:40","date_gmt":"2016-02-29T08:43:40","guid":{"rendered":"http:\/\/presta.site\/blog\/?p=182"},"modified":"2021-10-22T15:18:50","modified_gmt":"2021-10-22T12:18:50","slug":"using-sphinx-search-in-prestashop","status":"publish","type":"post","link":"https:\/\/presta.site\/blog\/en\/using-sphinx-search-in-prestashop\/","title":{"rendered":"Using Sphinx search in PrestaShop"},"content":{"rendered":"

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.<\/p>\n

If you want something better, you can use a great replacement — Sphinx.<\/p>\n


\nFrom Wikipedia:<\/p>\n

Sphinx is a free software\/open source Fulltext search engine designed to provide full-text search functionality to client applications.<\/p><\/blockquote>\n

Sphinx website — http:\/\/sphinxsearch.com\/<\/a><\/p>\n

Sphinx installation<\/h2>\n

I’ve used Debian 8.1. In other Linux distributions (and especially in Windows) installation may differ slightly.
\nIf Sphinx is available in repos, just install it:<\/p>\n

1
2
3
<\/div><\/td>
su<\/span>
\napt-get update<\/span>
\napt-get install<\/span> sphinxsearch<\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

It Sphinx is not available in repos, you can download an installer from official website and install dependencies:<\/p>\n

1
2
3
<\/div><\/td>
apt-get install<\/span> mysql-client unixodbc libpq5
\nwget<\/span> http:\/\/<\/span>sphinxsearch.com\/<\/span>files\/<\/span>sphinxsearch_2.2.9-release-1<\/span>~wheezy_i386.deb
\ndpkg<\/span> -i<\/span> sphinxsearch_2.2.9-release-1<\/span>~wheezy_i386.deb<\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

You have to replace name and url of installer with current relevant versions. They all are available on official website.<\/p>\n

Sphinx configuration<\/h2>\n

Open Sphinx config and edit it:<\/p>\n

1
<\/div><\/td>
nano<\/span> \/<\/span>etc\/<\/span>sphinxsearch\/<\/span>sphinx.conf<\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

You need to edit existing default block «source src1» (or add a new block). Result should look like this:<\/p>\n

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
<\/div><\/td>
source PrestaSite
\n{<\/span>
\ntype<\/span> =<\/span> mysql<\/span>
\nsql_host<\/span> =<\/span> localhost<\/span>
\n# PrestaShop database username
\nsql_user<\/span> =<\/span> DBUSER<\/span>
\n# PrestaShop database password
\nsql_pass<\/span> =<\/span> DBPASSWORD<\/span>
\n# PrestaShop database name
\nsql_db<\/span> =<\/span> DBNAME<\/span>
\nsql_port<\/span> =<\/span> 3306 # optional, default is 3306<\/span>
\nsql_query_pre<\/span> =<\/span> SET NAMES utf8<\/span>
\n
\n# MySQL query with list of fields for indexation
\nsql_query<\/span> =<\/span>
\nSELECT id_product, name, description, description_short
\nFROM ps_product_lang
\n}<\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

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

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.<\/p>\n

Next, in the «index definition» block you need to configure index:<\/p>\n

1
2
3
4
5
6
7
8
9
10
11
12
13
14
<\/div><\/td>
index PrestaSite
\n{<\/span>
\n# data source for indexation
\nsource<\/span> =<\/span> PrestaSite<\/span>
\n
\n# location for index data
\npath<\/span> =<\/span> \/var\/lib\/sphinxsearch\/data\/prestasite<\/span>
\n
\n# morphology preprocessors
\nmorphology<\/span> =<\/span> stem_en<\/span>
\n
\n# minimal word length for indexation
\nmin_word_len<\/span> =<\/span> 1<\/span>
\n}<\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

Next you can see «indexer settings» and «searchd settings» blocks. Usually they are ok, you can just skip them.<\/p>\n

So, we’ve configured data source (source PrestaSite) and index (index PrestaSite).<\/span><\/p>\n

Indexation<\/h2>\n

Launch indexer in order to index your database:<\/p>\n

1
<\/div><\/td>
indexer --all<\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

Sphinx launch<\/h2>\n

Don’t forget to start\/restart searchd:<\/p>\n

1
<\/div><\/td>
searchd<\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

And add indexer to crontab:<\/p>\n

1
<\/div><\/td>
15<\/span> *<\/span> *<\/span> *<\/span> *<\/span> root indexer --all<\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

Indexation will be launched every hour.<\/p>\n

PrestaShop configuration<\/h2>\n

Below you can see the code for PrestaShop 1.6. For other PrestaShop versions it may differ slightly.<\/p>\n

Create (or edit) Search class override:
\n\/override\/classes\/Search.php<\/p>\n

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
<\/div><\/td>
<?php<\/span>
\nclass<\/span> Search extends<\/span> SearchCore {<\/span>
\n    public<\/span> static function<\/span> find(<\/span>$id_lang<\/span>,<\/span> $expr<\/span>,<\/span> $page_number<\/span> =<\/span> 1<\/span>,<\/span> $page_size<\/span> =<\/span> 1<\/span>,<\/span> $order_by<\/span> =<\/span> 'position'<\/span>,<\/span>
\n        $order_way<\/span> =<\/span> 'desc'<\/span>,<\/span> $ajax<\/span> =<\/span> false<\/span>,<\/span> $use_cookie<\/span> =<\/span> true<\/span>,<\/span> Context $context<\/span> =<\/span> null<\/span>)<\/span>
\n    {<\/span>
\n        if<\/span> (<\/span>!<\/span>$context<\/span>)<\/span> {<\/span>
\n            $context<\/span> =<\/span> Context::<\/span>getContext<\/span>(<\/span>)<\/span>;<\/span>
\n        }<\/span>
\n        $db<\/span> =<\/span> Db::<\/span>getInstance<\/span>(<\/span>_PS_USE_SQL_SLAVE_)<\/span>;<\/span>
\n
\n        \/\/ TODO : smart page management<\/span>
\n        if<\/span> (<\/span>$page_number<\/span> <<\/span> 1<\/span>)<\/span> $page_number<\/span> =<\/span> 1<\/span>;<\/span>
\n        if<\/span> (<\/span>$page_size<\/span> <<\/span> 1<\/span>)<\/span> $page_size<\/span> =<\/span> 1<\/span>;<\/span>
\n
\n        if<\/span> (<\/span>!<\/span>Validate::<\/span>isOrderBy<\/span>(<\/span>$order_by<\/span>)<\/span> ||<\/span> !<\/span>Validate::<\/span>isOrderWay<\/span>(<\/span>$order_way<\/span>)<\/span>)<\/span>
\n            return<\/span> false<\/span>;<\/span>
\n
\n        if<\/span> (<\/span>!<\/span>Validate::<\/span>isOrderBy<\/span>(<\/span>$order_by<\/span>)<\/span> ||<\/span> !<\/span>Validate::<\/span>isOrderWay<\/span>(<\/span>$order_way<\/span>)<\/span>)<\/span> {<\/span>
\n            return<\/span> false<\/span>;<\/span>
\n        }<\/span>
\n
\n        $start<\/span> =<\/span> (<\/span>$page_number<\/span> -<\/span> 1<\/span>)<\/span> *<\/span> $page_size<\/span>;<\/span>
\n
\n        \/\/ Sphinx search, get ids of found products<\/span>
\n        $sphinx_results<\/span> =<\/span> self<\/span>::<\/span>getSphinxResults<\/span>(<\/span>$expr<\/span>,<\/span> $start<\/span>,<\/span> $page_size<\/span>)<\/span>;<\/span>
\n
\n        $result<\/span> =<\/span> null<\/span>;<\/span>
\n        $total<\/span> =<\/span> 0<\/span>;<\/span>
\n        \/\/ get products by id if something found<\/span>
\n        if<\/span> (<\/span>is_array<\/span><\/a>(<\/span>$sphinx_results<\/span>)<\/span> AND sizeof<\/span><\/a>(<\/span>$sphinx_results<\/span>)<\/span> AND isset<\/span><\/a>(<\/span>$sphinx_results<\/span>[<\/span>'total'<\/span>]<\/span>)<\/span> AND $sphinx_results<\/span>[<\/span>'total'<\/span>]<\/span> ><\/span> 0<\/span>)<\/span> {<\/span>
\n            $sql<\/span> =<\/span> 'SELECT p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity,
\n                    pl.`description_short`, pl.`available_now`, pl.`available_later`, pl.`link_rewrite`, pl.`name`,
\n                    MAX(image_shop.`id_image`) id_image, il.`legend`, m.`name` manufacturer_name, MAX(product_attribute_shop.`id_product_attribute`) id_product_attribute,
\n                    DATEDIFF(
\n                        p.`date_add`,
\n                        DATE_SUB(
\n                            NOW(),
\n                            INTERVAL '<\/span>.<\/span>(<\/span>Validate::<\/span>isUnsignedInt<\/span>(<\/span>Configuration::<\/span>get<\/span>(<\/span>'PS_NB_DAYS_NEW_PRODUCT'<\/span>)<\/span>)<\/span> ? Configuration::<\/span>get<\/span>(<\/span>'PS_NB_DAYS_NEW_PRODUCT'<\/span>)<\/span> :<\/span> 20<\/span>)<\/span>.<\/span>' DAY
\n                        )
\n                    ) > 0 new
\n                    FROM '<\/span>.<\/span>_DB_PREFIX_.<\/span>'product p
\n                    '<\/span>.<\/span>Shop::<\/span>addSqlAssociation<\/span>(<\/span>'product'<\/span>,<\/span> 'p'<\/span>)<\/span>.<\/span>'
\n                    INNER JOIN `'<\/span>.<\/span>_DB_PREFIX_.<\/span>'product_lang` pl ON (
\n                        p.`id_product` = pl.`id_product`
\n                        AND pl.`id_lang` = '<\/span>.<\/span>(<\/span>int)<\/span>$id_lang<\/span>.<\/span>Shop::<\/span>addSqlRestrictionOnLang<\/span>(<\/span>'pl'<\/span>)<\/span>.<\/span>'
\n                    )
\n                    LEFT JOIN `'<\/span>.<\/span>_DB_PREFIX_.<\/span>'product_attribute` pa ON (p.`id_product` = pa.`id_product`)
\n                    '<\/span>.<\/span>Shop::<\/span>addSqlAssociation<\/span>(<\/span>'product_attribute'<\/span>,<\/span> 'pa'<\/span>,<\/span> false<\/span>,<\/span> 'product_attribute_shop.`default_on` = 1'<\/span>)<\/span>.<\/span>'
\n                    '<\/span>.<\/span>Product::<\/span>sqlStock<\/span>(<\/span>'p'<\/span>,<\/span> 'product_attribute_shop'<\/span>,<\/span> false<\/span>,<\/span> $context<\/span>-><\/span>shop<\/span>)<\/span>.<\/span>'
\n                    LEFT JOIN `'<\/span>.<\/span>_DB_PREFIX_.<\/span>'manufacturer` m ON m.`id_manufacturer` = p.`id_manufacturer`
\n                    LEFT JOIN `'<\/span>.<\/span>_DB_PREFIX_.<\/span>'image` i ON (i.`id_product` = p.`id_product`)'<\/span>.<\/span>
\n                Shop::<\/span>addSqlAssociation<\/span>(<\/span>'image'<\/span>,<\/span> 'i'<\/span>,<\/span> false<\/span>,<\/span> 'image_shop.cover=1'<\/span>)<\/span>.<\/span>'
\n                    LEFT JOIN `'<\/span>.<\/span>_DB_PREFIX_.<\/span>'image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = '<\/span>.<\/span>(<\/span>int)<\/span>$id_lang<\/span>.<\/span>')
\n                    WHERE p.`id_product` IN('<\/span>.<\/span>implode<\/span><\/a>(<\/span>','<\/span>,<\/span> $sphinx_results<\/span>[<\/span>'results'<\/span>]<\/span>)<\/span>.<\/span>')
\n                    GROUP BY product_shop.id_product'<\/span>;<\/span>
\n            $result<\/span> =<\/span> $db<\/span>-><\/span>executeS<\/span>(<\/span>$sql<\/span>)<\/span>;<\/span>
\n
\n            \/\/ results count<\/span>
\n            $total<\/span> =<\/span> $sphinx_results<\/span>[<\/span>'total'<\/span>]<\/span>;<\/span>
\n        }<\/span>
\n
\n        if<\/span> (<\/span>!<\/span>$result<\/span>)<\/span> {<\/span>
\n            $result_properties<\/span> =<\/span> array<\/span><\/a>(<\/span>)<\/span>;<\/span>
\n        }<\/span> else<\/span> {<\/span>
\n            $result_properties<\/span> =<\/span> Product::<\/span>getProductsProperties<\/span>(<\/span>(<\/span>int)<\/span>$id_lang<\/span>,<\/span> $result<\/span>)<\/span>;<\/span>
\n        }<\/span>
\n
\n        return<\/span> array<\/span><\/a>(<\/span>'total'<\/span> =><\/span> $total<\/span>,<\/span> 'result'<\/span> =><\/span> $result_properties<\/span>)<\/span>;<\/span>
\n    }<\/span>
\n
\n    protected<\/span> static function<\/span> getSphinxResults(<\/span>$search_query<\/span>,<\/span> $page_number<\/span>,<\/span> $page_size<\/span>)<\/span>
\n    {<\/span>
\n        $results<\/span> =<\/span> array<\/span><\/a>(<\/span>)<\/span>;<\/span>
\n        $total<\/span> =<\/span> 0<\/span>;<\/span>
\n
\n        if<\/span>(<\/span>!<\/span>$search_query<\/span>)<\/span>
\n            return<\/span> null<\/span>;<\/span>
\n
\n        \/\/ connect to Sphinx database<\/span>
\n        $link<\/span> =<\/span> mysqli_connect<\/span><\/a>(<\/span>'127.0.0.1'<\/span>,<\/span> ''<\/span>,<\/span> ''<\/span>,<\/span> ''<\/span>,<\/span> '9306'<\/span>)<\/span>;<\/span>
\n        if<\/span>(<\/span>$link<\/span>)<\/span>
\n        {<\/span>
\n            $query<\/span> =<\/span> 'SELECT * FROM `PrestaSite` WHERE MATCH(\\''<\/span>.<\/span>pSQL(<\/span>$search_query<\/span>)<\/span>.<\/span>'\\') limit '<\/span>.<\/span>(<\/span>int)<\/span>$page_number<\/span>.<\/span>', '<\/span>.<\/span>(<\/span>int)<\/span>$page_size<\/span>.<\/span>';'<\/span>;<\/span>
\n            if<\/span> (<\/span>$result<\/span> =<\/span> $link<\/span>-><\/span>query<\/span>(<\/span>$query<\/span>)<\/span>)<\/span>
\n            {<\/span>
\n                while<\/span>(<\/span>$query_results<\/span> =<\/span> $result<\/span>-><\/span>fetch_array<\/span>(<\/span>)<\/span>)<\/span>
\n                {<\/span>
\n                    $results<\/span> =<\/span> array_merge<\/span><\/a>(<\/span>$results<\/span>,<\/span> $query_results<\/span>)<\/span>;<\/span>
\n                }<\/span>
\n
\n                \/* clear result *\/<\/span>
\n                $result<\/span>-><\/span>close<\/span>(<\/span>)<\/span>;<\/span>
\n            }<\/span>
\n
\n            \/\/ get count of results<\/span>
\n            $query_total<\/span> =<\/span> 'SELECT count(*) FROM `PrestaSite` WHERE MATCH(\\''<\/span>.<\/span>pSQL(<\/span>$search_query<\/span>)<\/span>.<\/span>'\\');'<\/span>;<\/span>
\n            if<\/span> (<\/span>$result<\/span> =<\/span> $link<\/span>-><\/span>query<\/span>(<\/span>$query_total<\/span>)<\/span>)<\/span>
\n            {<\/span>
\n                $total<\/span> =<\/span> (<\/span>int)<\/span>$result<\/span>-><\/span>fetch_array<\/span>(<\/span>)<\/span>[<\/span>0<\/span>]<\/span>;<\/span>
\n                if<\/span>(<\/span>$total<\/span> ><\/span> 1000<\/span>)<\/span>
\n                    $total<\/span> =<\/span> 1000<\/span>;<\/span>
\n            }<\/span>
\n
\n            mysqli_close<\/span><\/a>(<\/span>$link<\/span>)<\/span>;<\/span>
\n        }<\/span>
\n
\n        return<\/span> array<\/span><\/a>(<\/span>'results'<\/span> =><\/span> $results<\/span>,<\/span> 'total'<\/span> =><\/span> $total<\/span>)<\/span>;<\/span>
\n    }<\/span>
\n}<\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

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.<\/p>\n

At the end, you need to clear the cache and remove class cache (\/cache\/class_index.php). All done!<\/p>\n","protected":false},"excerpt":{"rendered":"

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.<\/p>\n","protected":false},"author":1,"featured_media":49,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[25,1],"tags":[],"_links":{"self":[{"href":"https:\/\/presta.site\/blog\/wp-json\/wp\/v2\/posts\/182"}],"collection":[{"href":"https:\/\/presta.site\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/presta.site\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/presta.site\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/presta.site\/blog\/wp-json\/wp\/v2\/comments?post=182"}],"version-history":[{"count":16,"href":"https:\/\/presta.site\/blog\/wp-json\/wp\/v2\/posts\/182\/revisions"}],"predecessor-version":[{"id":562,"href":"https:\/\/presta.site\/blog\/wp-json\/wp\/v2\/posts\/182\/revisions\/562"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/presta.site\/blog\/wp-json\/wp\/v2\/media\/49"}],"wp:attachment":[{"href":"https:\/\/presta.site\/blog\/wp-json\/wp\/v2\/media?parent=182"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/presta.site\/blog\/wp-json\/wp\/v2\/categories?post=182"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/presta.site\/blog\/wp-json\/wp\/v2\/tags?post=182"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}