Component Smart Search doesn’t work with a postgresql database #30412

Hug067 · 2020-08-19T06:19:35Z

Steps to reproduce the issue

Home Dashboard -> Components -> Smart Search

Select Index for indexing articles
Add a menu type Smart Search
On the Front End, search terms « Getting Started » in the menu create at step 2
Click on Search

Expected result

When typing « Getting Started » in the search Terms bar, propositions should be proposed to the user. The article « Getting Started » should appear in the result.

Actual result

When typing « Getting Started » in the search Terms bar no proposition is displayed and no result is found for query « Getting Started ».

System information (as much as possible)

Joomla 3.19.20
Postgresql 9.5
PHP 7.3.12

Additional comments

After some search, I noticed that the table #__finder_terms is empty in postgresql, the problem seems to come from the file :
"… \administrator\components\com_finder\helpers\indexer\driver\postgresql.php"
The query from line 277 to 299 doesn’t work in postgresql. After modifying the query like below the query works as expected :

$query = 'INSERT INTO ' . $db->quoteName('#__finder_tokens_aggregate') .
		' (' . $db->quoteName('term_id') .
		', ' . $db->quoteName('map_suffix') .
		', ' . $db->quoteName('term') .
		', ' . $db->quoteName('stem') .
		', ' . $db->quoteName('common') .
		', ' . $db->quoteName('phrase') .
		', ' . $db->quoteName('term_weight') .
		', ' . $db->quoteName('context') .
		', ' . $db->quoteName('context_weight') .
		', ' . $db->quoteName('total_weight') .
		', ' . $db->quoteName('language') . ')' .
		' SELECT' .
		' COALESCE(t.term_id, 0) as term_id, \'\' as map_suffix, t1.term, t1.stem, t1.common, t1.phrase, t1.weight, t1.context,' .
		' ROUND( t1.weight * COUNT( t2.term ) * %F, 8 ) AS context_weight, 0 as total_weight, t1.language' .
		' FROM (' .
		'   SELECT DISTINCT t1.term, t1.stem, t1.common, t1.phrase, t1.weight, t1.context, t1.language' .
		'   FROM ' . $db->quoteName('#__finder_tokens') . ' AS t1' .
		'   WHERE t1.context = %d' .
		' ) AS t1' .
		' JOIN ' . $db->quoteName('#__finder_tokens') . ' AS t2 ON t2.term = t1.term' .
		' LEFT JOIN ' . $db->quoteName('#__finder_terms') . ' AS t ON t.term = t1.term' .
		' WHERE t2.context = %d' .
		' GROUP BY t1.term, t.term_id, t1.term, t1.stem, t1.common, t1.phrase, t1.weight, t1.context, t1.language' .
		' ORDER BY t1.term DESC';

Then the code from line 320 to 344 result in a error when indexing the content of the site. So I commented the lines 321 to 328 and line 344 like below.

// Emulation of IGNORE INTO behaviour
	//$db->setQuery(
	//	' SELECT ta.term' .
	//	' FROM ' . $db->quoteName('#__finder_tokens_aggregate') . ' AS ta' .
	//	' WHERE ta.term_id = 0'
	//);

	//if ($db->loadRow() === null)
	//{
		$db->setQuery(
			'INSERT INTO ' . $db->quoteName('#__finder_terms') .
			' (' . $db->quoteName('term') .
			', ' . $db->quoteName('stem') .
			', ' . $db->quoteName('common') .
			', ' . $db->quoteName('phrase') .
			', ' . $db->quoteName('weight') .
			', ' . $db->quoteName('soundex') .
			', ' . $db->quoteName('language') . ')' .
			' SELECT ta.term, ta.stem, ta.common, ta.phrase, ta.term_weight, SOUNDEX(ta.term), ta.language' .
			' FROM ' . $db->quoteName('#__finder_tokens_aggregate') . ' AS ta' .
			' WHERE ta.term_id = 0' .
			' GROUP BY ta.term, ta.stem, ta.common, ta.phrase, ta.term_weight, SOUNDEX(ta.term), ta.language'
		);
		$db->execute();
	//}

Now after indexing the content of the site, and typing « Getting » in the search terms bar, some propositions are displayed to the user. However the query returns a PHP error :

Notice: unserialize(): Error at offset 0 of 76 bytes

Problem comes from the line 130, the serialize object isn’t correctly insert in the database, replace the line 130 by the following code solves the problem :

. $db->quote(str_replace(array("\\\\", "''"), array("\\", "'"), pg_escape_bytea(serialize($item)))) . ', '

Do the same for the line 157 :

->set($db->quoteName('object') . ' = ' . $db->quote(str_replace(array("\\\\", "''"), array("\\", "'"), pg_escape_bytea(serialize($item)))))

Then, we just need to modify the line 129 of the file « …\components\com_finder\models\search.php » to obtain the expected result :

$result = unserialize(pg_unescape_bytea($row->object));

In the component "Smart Search", clear the index, re-click on Index for indexing articles. Now everything works as expected (well I think but there may be another problem...).

The modifed files are in attachments.search.txt
postgresql.txt

The text was updated successfully, but these errors were encountered:

richard67 · 2020-08-19T06:43:06Z

@Hackwar Could you have a look on this? Thanks in advance.

alikon · 2020-08-19T07:37:46Z

1st thank you @Hug067 for investigating on this

with postgresql 11.7 i've replaced
components\com_finder\models\search.php with your search.txt
administrator\components\com_finder\helpers\indexer\driver\postgresql.php with your postgresql.txt

before that indexer from admin doesn't work and search from frontend does not display suggestion,

now indexer from admin works and from frontend display suggestion but :

you cannot use $result = unserialize(pg_unescape_bytea($row->object)); in search.php cause it didn't work for mysql or sqlrv

i got notice on frontend search Undefined property: stdClass::$route

[Wed Aug 19 09:21:36.071033 2020] [php7:notice] [pid 10447] [client 127.0.0.1:54530] PHP Notice: Undefined property: stdClass::$route in /var/www/html/jbs3/components/com_finder/views/search/tmpl/default_result.php on line 74, referer: http://localhost/jbs3/index.php/smart-search?q=Template&Search= [Wed Aug 19 09:21:36.071038 2020] [php7:notice] [pid 10447] [client 127.0.0.1:54530] PHP Stack trace:, referer: http://localhost/jbs3/index.php/smart-search?q=Template&Search= [Wed Aug 19 09:21:36.071043 2020] [php7:notice] [pid 10447] [client 127.0.0.1:54530] PHP 1. {main}() /var/www/html/jbs3/index.php:0, referer: http://localhost/jbs3/index.php/smart-search?q=Template&Search= [Wed Aug 19 09:21:36.071050 2020] [php7:notice] [pid 10447] [client 127.0.0.1:54530] PHP 2. Joomla\\CMS\\Application\\SiteApplication->execute() /var/www/html/jbs3/index.php:49, referer: http://localhost/jbs3/index.php/smart-search?q=Template&Search= [Wed Aug 19 09:21:36.071056 2020] [php7:notice] [pid 10447] [client 127.0.0.1:54530] PHP 3. Joomla\\CMS\\Application\\SiteApplication->doExecute() /var/www/html/jbs3/libraries/src/Application/CMSApplication.php:196, referer: http://localhost/jbs3/index.php/smart-search?q=Template&Search= [Wed Aug 19 09:21:36.071062 2020] [php7:notice] [pid 10447] [client 127.0.0.1:54530] PHP 4. Joomla\\CMS\\Application\\SiteApplication->dispatch() /var/www/html/jbs3/libraries/src/Application/SiteApplication.php:233, referer: http://localhost/jbs3/index.php/smart-search?q=Template&Search= [Wed Aug 19 09:21:36.071068 2020] [php7:notice] [pid 10447] [client 127.0.0.1:54530] PHP 5. Joomla\\CMS\\Component\\ComponentHelper::renderComponent() /var/www/html/jbs3/libraries/src/Application/SiteApplication.php:194, referer: http://localhost/jbs3/index.php/smart-search?q=Template&Search= [Wed Aug 19 09:21:36.071074 2020] [php7:notice] [pid 10447] [client 127.0.0.1:54530] PHP 6. Joomla\\CMS\\Component\\ComponentHelper::executeComponent() /var/www/html/jbs3/libraries/src/Component/ComponentHelper.php:377, referer: http://localhost/jbs3/index.php/smart-search?q=Template&Search= [Wed Aug 19 09:21:36.071116 2020] [php7:notice] [pid 10447] [client 127.0.0.1:54530] PHP 7. require_once() /var/www/html/jbs3/libraries/src/Component/ComponentHelper.php:402, referer: http://localhost/jbs3/index.php/smart-search?q=Template&Search= [Wed Aug 19 09:21:36.071128 2020] [php7:notice] [pid 10447] [client 127.0.0.1:54530] PHP 8. FinderController->execute() /var/www/html/jbs3/components/com_finder/finder.php:15, referer: http://localhost/jbs3/index.php/smart-search?q=Template&Search= [Wed Aug 19 09:21:36.071135 2020] [php7:notice] [pid 10447] [client 127.0.0.1:54530] PHP 9. FinderController->display() /var/www/html/jbs3/libraries/src/MVC/Controller/BaseController.php:710, referer: http://localhost/jbs3/index.php/smart-search?q=Template&Search= [Wed Aug 19 09:21:36.071142 2020] [php7:notice] [pid 10447] [client 127.0.0.1:54530] PHP 10. FinderController->display() /var/www/html/jbs3/components/com_finder/controller.php:55, referer: http://localhost/jbs3/index.php/smart-search?q=Template&Search= [Wed Aug 19 09:21:36.071154 2020] [php7:notice] [pid 10447] [client 127.0.0.1:54530] PHP 11. FinderViewSearch->display() /var/www/html/jbs3/libraries/src/MVC/Controller/BaseController.php:672, referer: http://localhost/jbs3/index.php/smart-search?q=Template&Search= [Wed Aug 19 09:21:36.071163 2020] [php7:notice] [pid 10447] [client 127.0.0.1:54530] PHP 12. FinderViewSearch->display() /var/www/html/jbs3/components/com_finder/views/search/view.html.php:159, referer: http://localhost/jbs3/index.php/smart-search?q=Template&Search= [Wed Aug 19 09:21:36.071173 2020] [php7:notice] [pid 10447] [client 127.0.0.1:54530] PHP 13. FinderViewSearch->loadTemplate() /var/www/html/jbs3/libraries/src/MVC/View/HtmlView.php:230, referer: http://localhost/jbs3/index.php/smart-search?q=Template&Search= [Wed Aug 19 09:21:36.071180 2020] [php7:notice] [pid 10447] [client 127.0.0.1:54530] PHP 14. include() /var/www/html/jbs3/libraries/src/MVC/View/HtmlView.php:701, referer: http://localhost/jbs3/index.php/smart-search?q=Template&Search= [Wed Aug 19 09:21:36.071187 2020] [php7:notice] [pid 10447] [client 127.0.0.1:54530] PHP 15. FinderViewSearch->loadTemplate() /var/www/html/jbs3/components/com_finder/views/search/tmpl/default.php:36, referer: http://localhost/jbs3/index.php/smart-search?q=Template&Search= [Wed Aug 19 09:21:36.071194 2020] [php7:notice] [pid 10447] [client 127.0.0.1:54530] PHP 16. include() /var/www/html/jbs3/libraries/src/MVC/View/HtmlView.php:701, referer: http://localhost/jbs3/index.php/smart-search?q=Template&Search= [Wed Aug 19 09:21:36.071201 2020] [php7:notice] [pid 10447] [client 127.0.0.1:54530] PHP 17. FinderViewSearch->loadTemplate() /var/www/html/jbs3/components/com_finder/views/search/tmpl/default_results.php:52, referer: http://localhost/jbs3/index.php/smart-search?q=Template&Search= [Wed Aug 19 09:21:36.071208 2020] [php7:notice] [pid 10447] [client 127.0.0.1:54530] PHP 18. include() /var/www/html/jbs3/libraries/src/MVC/View/HtmlView.php:701, referer: http://localhost/jbs3/index.php/smart-search?q=Template&Search=

and this is the frontend search result

you are very near to the perfect solution 👍

Hug067 · 2020-08-21T06:21:51Z

I hadn't thought of that...
So the code below should work but is it clean enough ?

// Convert the rows to result objects.
		if ($db->serverType == 'postgresql')
		{
			foreach ($rows as $rk => $row)
			{
				// Build the result object.
				$result = unserialize(pg_unescape_bytea($row->object));
				$result->weight = $results[$rk];
				$result->link_id = $rk;

				// Add the result back to the stack.
				$results[$rk] = $result;
			}
		}
		else 
		{
			foreach ($rows as $rk => $row)
			{
				// Build the result object.
				$result = unserialize($row->object);
				$result->weight = $results[$rk];
				$result->link_id = $rk;

				// Add the result back to the stack.
				$results[$rk] = $result;
			}
		}

The new file is in attachmentsearch.txt

_{This comment was created with the J!Tracker Application at issues.joomla.org/tracker/joomla-cms/30412.}

brianteeman · 2020-10-10T09:48:41Z

@alikon just wondering if there will be a PR coming for this

Hackwar · 2020-10-10T19:06:11Z

Can you please test this again? Because the code has been changed quite a bit (by me) and the bug should have been unknowingly fixed during that. I can't replicate it anymore.

alikon · 2020-10-12T10:37:21Z

i can re-confirm that index from admin doesn't work on 3.x
if you instead use the version provided by @Hug067 the you are able to >index from admin

@brianteeman

@alikon just wondering if there will be a PR coming for this

if @Hug067 cannot, then i will try do it

Hackwar · 2020-10-12T20:40:12Z

Sorry, I missed that this is for 3.9 and not 4.0.

Hackwar · 2020-12-27T22:29:46Z

Here would be the fix: #31787

richard67 · 2020-12-27T22:31:17Z

Closing as having a pull request. Please test #31787 . Thanks in advance.

alikon · 2020-12-30T09:51:26Z

not solved by #31787

alikon · 2020-12-30T09:52:57Z

backend

frontend

Hackwar · 2021-05-14T08:37:58Z

#31809 should fix this.

richard67 · 2021-05-14T08:50:00Z

Closing as having a pull request. Please test #31809 . Thanks in advance.

joomla-cms-bot added the No Code Attached Yet label Aug 19, 2020

Quy added the Information Required label Oct 10, 2020

alikon added the J3 Issue label Oct 12, 2020

Hackwar mentioned this issue Dec 27, 2020

[3.9] Smart Search: Fix indexing for Postgres #31787

Merged

richard67 closed this as completed Dec 27, 2020

alikon reopened this Dec 30, 2020

Hackwar mentioned this issue Dec 30, 2020

Smart Search: Fix indexing for Postgres #31809

Merged

richard67 closed this as completed May 14, 2021

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Component Smart Search doesn’t work with a postgresql database #30412

Component Smart Search doesn’t work with a postgresql database #30412

Hug067 commented Aug 19, 2020

richard67 commented Aug 19, 2020

alikon commented Aug 19, 2020

Hug067 commented Aug 21, 2020

brianteeman commented Oct 10, 2020

Hackwar commented Oct 10, 2020

alikon commented Oct 12, 2020

Hackwar commented Oct 12, 2020

Hackwar commented Dec 27, 2020

richard67 commented Dec 27, 2020

alikon commented Dec 30, 2020

alikon commented Dec 30, 2020

Hackwar commented May 14, 2021

richard67 commented May 14, 2021

Component Smart Search doesn’t work with a postgresql database #30412

Component Smart Search doesn’t work with a postgresql database #30412

Comments

Hug067 commented Aug 19, 2020

Steps to reproduce the issue

Expected result

Actual result

System information (as much as possible)

Additional comments

richard67 commented Aug 19, 2020

alikon commented Aug 19, 2020

Hug067 commented Aug 21, 2020

brianteeman commented Oct 10, 2020

Hackwar commented Oct 10, 2020

alikon commented Oct 12, 2020

Hackwar commented Oct 12, 2020

Hackwar commented Dec 27, 2020

richard67 commented Dec 27, 2020

alikon commented Dec 30, 2020

alikon commented Dec 30, 2020

Hackwar commented May 14, 2021

richard67 commented May 14, 2021