Joomla! Issue Tracker - CMS

Download
Launch
Feeling Lucky

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

Information Required J3 Issue ?
avatar Hug067
Hug067
19 Aug 2020

Steps to reproduce the issue

Home Dashboard -> Components -> Smart Search

  1. Select Index for indexing articles
  2. Add a menu type Smart Search
  3. On the Front End, search terms « Getting Started » in the menu create at step 2
  4. 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

avatar Hug067 Hug067 - open - 19 Aug 2020
avatar joomla-cms-bot joomla-cms-bot - labeled - 19 Aug 2020
avatar richard67
richard67 - comment - 19 Aug 2020

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

avatar alikon
alikon - comment - 19 Aug 2020

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
Screenshot from 2020-08-19 09-35-04

you are very near to the perfect solution 👍

avatar Hug067
Hug067 - comment - 21 Aug 2020

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.

avatar brianteeman
brianteeman - comment - 10 Oct 2020

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

avatar Hackwar
Hackwar - comment - 10 Oct 2020

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.

avatar Quy Quy - change - 10 Oct 2020
Labels Added: Information Required
avatar Quy Quy - labeled - 10 Oct 2020
avatar alikon
alikon - comment - 12 Oct 2020

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

avatar alikon alikon - change - 12 Oct 2020
Labels Added: J3 Issue
avatar alikon alikon - labeled - 12 Oct 2020
avatar Hackwar
Hackwar - comment - 12 Oct 2020

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

avatar Hackwar
Hackwar - comment - 27 Dec 2020

Here would be the fix: #31787

avatar richard67 richard67 - change - 27 Dec 2020
Status New ⇒ Closed
Closed_Date 0000-00-00 00:00:00 ⇒ 2020-12-27 22:31:22
Closed_By ⇒ richard67
avatar richard67
richard67 - comment - 27 Dec 2020

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

avatar richard67 richard67 - close - 27 Dec 2020
avatar alikon alikon - change - 30 Dec 2020
Status Closed ⇒ New
Closed_Date 2020-12-27 22:31:22 ⇒
Closed_By richard67 ⇒
avatar alikon alikon - reopen - 30 Dec 2020
avatar alikon
alikon - comment - 30 Dec 2020

not solved by #31787

avatar alikon
alikon - comment - 30 Dec 2020

backend
image

frontend

image

avatar Hackwar
Hackwar - comment - 14 May 2021

#31809 should fix this.

avatar richard67 richard67 - close - 14 May 2021
avatar richard67
richard67 - comment - 14 May 2021

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

avatar richard67 richard67 - change - 14 May 2021
Status New ⇒ Closed
Closed_Date 0000-00-00 00:00:00 ⇒ 2021-05-14 08:50:02
Closed_By ⇒ richard67

Add a Comment

Login with GitHub to post a comment