PostgreSQL: product search with multiple terms
Forum rules
Always add your Laravel, Aimeos and PHP version as well as your environment (Linux/Mac/Win)
Spam and unrelated posts will be removed immediately!
Always add your Laravel, Aimeos and PHP version as well as your environment (Linux/Mac/Win)
Spam and unrelated posts will be removed immediately!
PostgreSQL: product search with multiple terms
Hi, using aimeos 2020.10 and PostgreSQL 9.5 DB:
Product search (text search) with a single term like eg. "term1" works fine.
But searches with multiple terms separted by space eg. "term1 term2" cause an error:
"A non-recoverable error occured"
and in log
SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error in tsquery: "term1 term2"
Using + instead of space eg. "term1+term2" provides correct results.
Wrong setting / config or are multiple search term not supported by aimeos for PostgreSQL ?
Thanks!
Product search (text search) with a single term like eg. "term1" works fine.
But searches with multiple terms separted by space eg. "term1 term2" cause an error:
"A non-recoverable error occured"
and in log
SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error in tsquery: "term1 term2"
Using + instead of space eg. "term1+term2" provides correct results.
Wrong setting / config or are multiple search term not supported by aimeos for PostgreSQL ?
Thanks!
Re: PostgreSQL: product search with multiple terms
The problem may be located here:
https://github.com/aimeos/aimeos-core/b ... L.php#L102
Does it work when you change to
https://github.com/aimeos/aimeos-core/b ... L.php#L102
Does it work when you change
Code: Select all
' & '
Code: Select all
' +'
Professional support and custom implementation are available at Aimeos.com
If you like Aimeos, give us a star
If you like Aimeos, give us a star
Re: PostgreSQL: product search with multiple terms
Unfortunately it doesn't solved the issue.
- while entering "term1 term2" (without +) only search suggestions for term1 are presented (term2 ignored). After clicking "enter" the error message is shown.
- while entering "term1+term2" (with +), correct search suggestions (only results containing term1 and term2) are presented. After clicking "enter" the correct results are displayed
Re: PostgreSQL: product search with multiple terms
We've fixed the problem in this commit:
https://github.com/aimeos/aimeos-core/c ... def787b6be
You can update your installation using:
https://github.com/aimeos/aimeos-core/c ... def787b6be
You can update your installation using:
Code: Select all
composer up aimeos/aimeos-core:2020.10.x-dev
Professional support and custom implementation are available at Aimeos.com
If you like Aimeos, give us a star
If you like Aimeos, give us a star
Re: PostgreSQL: product search with multiple terms
The provided code did not solve it at first run. But after replacing:
with the original line:
its working fine now !
Here the complete code of PgSQL.php:
Thank you
Code: Select all
$params[1] = join( ' | ', $strings ) . ' | "' . join( ' <-> ', explode( ' ', $search ) ) . '"';
Code: Select all
$params[1] = '\'' . join( ' & ', $strings ) . '\'';
Here the complete code of PgSQL.php:
Code: Select all
<?php
/**
* @license LGPLv3, https://opensource.org/licenses/LGPL-3.0
* @copyright Aimeos (aimeos.org), 2016-2020
* @package MShop
* @subpackage Index
*/
namespace Aimeos\MShop\Index\Manager\Text;
/**
* PostgreSQL based index text for searching in product tables.
*
* @package MShop
* @subpackage Index
*/
class PgSQL
extends \Aimeos\MShop\Index\Manager\Text\Standard
{
private $searchConfig = array(
'index.text:relevance' => array(
'code' => 'index.text:relevance()',
//'internalcode' => ':site AND mindte."langid" = $1 AND CAST( to_tsvector(\'english\', mindte."content") @@ to_tsquery( $2 ) AS integer )',
'internalcode' => ':site AND mindte."langid" = $1 AND CAST( to_tsvector(mindte."content") @@ to_tsquery( $2 ) AS integer )',
'label' => 'Product texts, parameter(<language ID>,<search term>)',
'type' => 'float',
'internaltype' => \Aimeos\MW\DB\Statement\Base::PARAM_INT,
'public' => false,
),
'sort:index.text:relevance' => array(
'code' => 'sort:index.text:relevance()',
//'internalcode' => 'ts_rank(to_tsvector(\'english\', mindte."content"), to_tsquery( $2 ))',
'internalcode' => 'ts_rank(to_tsvector(mindte."content"), to_tsquery( $2 ))',
'label' => 'Product text sorting, parameter(<language ID>,<search term>)',
'type' => 'float',
'internaltype' => \Aimeos\MW\DB\Statement\Base::PARAM_INT,
'public' => false,
),
);
/**
* Initializes the object
*
* @param \Aimeos\MShop\Context\Item\Iface $context Context object
*/
public function __construct( \Aimeos\MShop\Context\Item\Iface $context )
{
parent::__construct( $context );
$level = \Aimeos\MShop\Locale\Manager\Base::SITE_ALL;
$level = $context->getConfig()->get( 'mshop/index/manager/sitemode', $level );
$name = 'index.text:relevance';
$expr = $this->getSiteString( 'mindte."siteid"', $level );
$this->searchConfig[$name]['internalcode'] = str_replace( ':site', $expr, $this->searchConfig[$name]['internalcode'] );
//$this->searchConfig[$name]['function'] = $this->getFunctionRelevance();
$this->searchConfig[$name]['function'] = $this->searchConfig['sort:' . $name]['function'] = $this->getFunctionRelevance();
}
/**
* Returns a list of objects describing the available criterias for searching.
*
* @param bool $withsub Return also attributes of sub-managers if true
* @return \Aimeos\MW\Criteria\Attribute\Iface[] List of search attriubte items
*/
public function getSearchAttributes( bool $withsub = true ) : array
{
$list = parent::getSearchAttributes( $withsub );
foreach( $this->searchConfig as $key => $fields ) {
$list[$key] = new \Aimeos\MW\Criteria\Attribute\Standard( $fields );
}
return $list;
}
/**
* Returns the search function for searching by relevance
*
* @return \Closure Relevance search function
*/
protected function getFunctionRelevance()
{
return function( $source, array $params ) {
if( isset( $params[1] ) )
{
$strings = [];
$regex = '/(\&|\||\!|\-|\+|\>|\<|\(|\)|\~|\*|\:|\"|\'|\@|\\| )+/';
$search = trim( mb_strtolower( preg_replace( $regex, ' ', $params[1] ) ), "' \t\n\r\0\x0B" );
foreach( explode( ' ', $search ) as $part )
{
if( strlen( $part ) > 2 ) {
$strings[] = $part . ':*';
}
}
$params[1] = '\'' . join( ' & ', $strings ) . '\'';
//$params[1] = join( ' | ', $strings ) . ' | "' . join( ' <-> ', explode( ' ', $search ) ) . '"';
}
return $params;
};
}
}
Re: PostgreSQL: product search with multiple terms
There was another commit yesterday adding the apostrophes after the mentioned commit:
https://github.com/aimeos/aimeos-core/c ... a2ee494188
If you update to the latest commit, it should work without modifications and rank phrase queries higher than prefix terms.
https://github.com/aimeos/aimeos-core/c ... a2ee494188
If you update to the latest commit, it should work without modifications and rank phrase queries higher than prefix terms.
Professional support and custom implementation are available at Aimeos.com
If you like Aimeos, give us a star
If you like Aimeos, give us a star
Re: PostgreSQL: product search with multiple terms
thank you! - tested the newer code; it combines search terms with logical "OR".
For our purpose an "AND"-combination would be better. So we plan to use the "older" code with ' & '
Do you plan any configurations (eg. in shop.php) how search terms are combined or are settings already available yet?
eg. available product names:
- Product 1 term1 abc
- Product 2 term2 def
- Product 3 term1 term2 ghi
- Product 4 term3 jkl
search "term1 term2" with:
result:
- Product 3 term1 term2 ghi
but search "term1 term2" with:
result:
- Product 1 term1 abc
- Product 2 term2 def
- Product 3 term1 term2 ghi
For our purpose an "AND"-combination would be better. So we plan to use the "older" code with ' & '
Do you plan any configurations (eg. in shop.php) how search terms are combined or are settings already available yet?
eg. available product names:
- Product 1 term1 abc
- Product 2 term2 def
- Product 3 term1 term2 ghi
- Product 4 term3 jkl
search "term1 term2" with:
Code: Select all
$params[1] = '\'' . join( ' & ', $strings ) . '\'';
- Product 3 term1 term2 ghi
but search "term1 term2" with:
Code: Select all
$params[1] = '\'' . join( ' | ', $strings ) . ' | "' . join( ' <-> ', explode( ' ', $search ) ) . '"\'';
- Product 1 term1 abc
- Product 2 term2 def
- Product 3 term1 term2 ghi
Re: PostgreSQL: product search with multiple terms
Does it return the results in that order? Would expectcolumbo wrote: ↑03 Jan 2021, 13:38 eg. available product names:
- Product 1 term1 abc
- Product 2 term2 def
- Product 3 term1 term2 ghi
- Product 4 term3 jkl
search "term1 term2" with:result:Code: Select all
$params[1] = '\'' . join( ' | ', $strings ) . ' | "' . join( ' <-> ', explode( ' ', $search ) ) . '"\'';
- Product 1 term1 abc
- Product 2 term2 def
- Product 3 term1 term2 ghi
- Product 3 term1 term2 ghi
- Product 1 term1 abc
- Product 2 term2 def
due to a higher ranking of the phrase compared to the single terms. In e-commerce its usually better to return more results instead of nothing but the better matching products must be displayed first.
Professional support and custom implementation are available at Aimeos.com
If you like Aimeos, give us a star
If you like Aimeos, give us a star
Re: PostgreSQL: product search with multiple terms
Unfortunately not.
Phrases or products wiht both terms (term1 and term2) in the product name are not higher ranked / listed as first result
Phrases or products wiht both terms (term1 and term2) in the product name are not higher ranked / listed as first result
Re: PostgreSQL: product search with multiple terms
We've made some tests with the Aimeos unittest data and we get the ranking we expect.
Terms:
Generated SQL query:
Result:
Can you see what query is generated in your installation and what the result set is?
Terms:
Code: Select all
cafe noire disc
Code: Select all
SELECT mpro."id", mpro.label, MIN(ts_rank(to_tsvector(mindte."content"), to_tsquery( 'cafe:* | noire:* | disc:* | "cafe <-> noire <-> disc"' ))) AS "s0"
FROM "mshop_product" AS mpro
LEFT JOIN "mshop_index_text" AS mindte ON mindte."prodid" = mpro."id"
WHERE ( ( mpro."siteid" = '482.' OR mpro."siteid" = '' ) AND ( mindte."siteid" = '482.' OR mindte."siteid" = '' ) AND mindte."langid" = 'de' AND CAST( to_tsvector(mindte."content") @@ to_tsquery( 'cafe:* | noire:* | disc:* | "cafe <-> noire <-> disc"' ) AS integer ) > 0 )
GROUP BY mpro."id"
ORDER BY "s0" DESC
OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY;
Code: Select all
id | label | s0
------+-----------------------+-----------
4933 | Cafe Noire Expresso | 0.080494
4932 | Cafe Noire Cappuccino | 0.0576968
4910 | MNOP/16 discs | 0.0202642
4911 | QRST/16 discs | 0.0202642
Professional support and custom implementation are available at Aimeos.com
If you like Aimeos, give us a star
If you like Aimeos, give us a star