joomla / joomla-cms

Home of the Joomla! Content Management System
https://www.joomla.org
GNU General Public License v2.0
4.78k stars 3.65k forks source link

The search suggestions do not appear #44373

Closed softarius closed 3 weeks ago

softarius commented 3 weeks ago

Steps to reproduce the issue

Expected result

Suggestion menu with words under search editor

Actual result

No suggestions

System information (as much as possible)

Joomla 4.4.* - 5.2 PHP 8.3 or any other Postgresql 16 (it's important, MySQL works fine) Windows or Linux Apache 2.4

Additional comments

index.php?option=com_finder&task=suggestions.suggest&format=json&tmpl=component&q=word returns JSON object with empty array "suggestions"

softarius commented 3 weeks ago

I found problem and suggest to fix it.

  1. Postgresql can not execute query with "distinct" and "order by" with out this fields into "select".
  2. Method "whereIn" does not work correctly (I don't know why). I suggest replace it just "implode" function.
diff --git a/components/com_finder/src/Model/SuggestionsModel.php b/components/com_finder/src/Model/SuggestionsModel.php
index afe9c01096..0daa0ea689 100644
--- a/components/com_finder/src/Model/SuggestionsModel.php
+++ b/components/com_finder/src/Model/SuggestionsModel.php
@@ -94,8 +94,9 @@ class SuggestionsModel extends ListModel

         // Select required fields
         $termQuery->select('DISTINCT(t.term)')
+            ->select('t.links, t.weight')
             ->from($db->quoteName('#__finder_terms', 't'))
-            ->whereIn('t.term_id', $termIds)
+            ->where('t.term_id in ('. implode(',', $termIds).')')
             ->order('t.links DESC')
             ->order('t.weight DESC');
richard67 commented 3 weeks ago

Postgresql 16 (it's important, MySQL works fine)

@softarius Which version(s) of MySQL have you checked? I'm asking because I get the problem number 1 also on MySQL 8.0.39.

Your suggested fix points to the right direction, but it cannot be used as it is because the getListQuery method should return a query for only the term_id column.

Another thing is that using DISTINCT(t.term) in this way like a function is not a documented syntax of MySQL or MariaDB. So I have no idea why that works e.g. on MySQL 8.0.39.

I remember that @Hackwar has struggled with a similar problem in his (still open and to be tested) pull request (PR) #44051 . Maybe he has an idea or a preference how to fix the query here?

softarius commented 3 weeks ago

@richard67

@softarius Which version(s) of MySQL have you checked? I'm asking because I get the problem number 1 also on MySQL 8.0.39.

I have test Joomla 5.2.0 stable with Maria DB 11.2 on Windows, Mysql 8.0.39 Ubuntu 22.04 PHP 8.3.6. My modifications works fine. Image

Your suggested fix points to the right direction, but it cannot be used as it is because the getListQuery method should return a query for only the term_id column.

\Joomla\Component\Finder\Site\Model\SuggestionsModel.getListQuery method returns query with string "term" field, no "term_id".

And it isn't problem, because getItems method use foreach to convert items to a simple array.

If to add ->select('t.links, t.weight') to $termQuery it works the same way

Another thing is that using DISTINCT(t.term) in this way like a function is not a documented syntax of MySQL or MariaDB. So I have no idea why that works e.g. on MySQL 8.0.39.

Yes. It works on MySQL 8.0.39 with and without brackets Image

/usr/sbin/mysqld Ver 8.0.39-0ubuntu0.24.04.2 for Linux on x86_64 ((Ubuntu))

select distinct (t.term), links, weight from cms_finder_terms as t order by t.links, t.weight; // works too

I remember that @Hackwar has struggled with a similar problem in his (still open and to be tested) pull request (PR) #44051 . Maybe he has an idea or a preference how to fix the query here?

I try to think about it

alikon commented 3 weeks ago

i've made a pr #44384 using a differnt query

SELECT DISTINCT(o.term) FROM (
SELECT t.term, t.links, t.weight
FROM "j501_finder_terms" AS "t"
INNER JOIN "j501_finder_links_terms" AS "tm" ON tm.term_id = t.term_id
INNER JOIN "j501_finder_links" AS "l" ON (tm.link_id = l.link_id)
WHERE t.term_id IN (2643,2897, 4277) AND l.access IN (1,5) AND l.state = 1 AND l.published = 1
ORDER BY t.links DESC,t.weight DESC) AS o
richard67 commented 3 weeks ago

@alikon 's solution is the right one. The solution provided here by @softarius applies the DISTINCTto all 3 columns in the SELECT and that gives a different result as the existing one and the one provided in #44384 if there are several records with different links and weight for the same term.

softarius commented 3 weeks ago

Well, Query "all in one".

select distinct o.term from (
select t.term, ti.links, ti.weight
FROM `cms_finder_terms` AS ti
inner join cms_finder_terms t on t.term_id =ti.term_id 
inner join cms_finder_links_terms flt on flt.term_id = ti.term_id 
inner join cms_finder_links l on l.link_id =flt.link_id and l.access IN (1,5) AND l.state = 1 AND l.published = 1
WHERE ti.term LIKE 'look%' AND ti.common = 0 AND ti.language IN ('en', '*')
ORDER BY ti.links DESC, ti.weight desc) as o

where look- q param

softarius commented 3 weeks ago

SELECT DISTINCT(o.term) FROM ( SELECT t.term, t.links, t.weight

No differents with SELECT distinct(t.term) , t.links, t.weight ... because distinct(param) use for param only, no t.links, t.weight Other words, returns only one first record of term with max links and weight

richard67 commented 3 weeks ago

SELECT DISTINCT(o.term) FROM ( SELECT t.term, t.links, t.weight

No differents with SELECT distinct(t.term) , t.links, t.weight ... because distinct(param) use for param only, no t.links, t.weight Other words, returns only one first record of term with max links and weight

@softarius Thats right here because you select only one column, but like you have suggested it in a comment above it would be different;

select distinct (t.term), links, weight ...

will not work as you intended, it will apply the DISTINCT over all 3 columns. At least that's what MySQL and MariaDB documentation say.

softarius commented 3 weeks ago

@richard67 Yes, you are right. Only select distinct trm from select ( works (https://github.com/joomla/joomla-cms/issues/44373#issuecomment-2449786257)

richard67 commented 3 weeks ago

Finally. So I think @alikon is fine and ready for testing. I think we should not try to combine the 2 queries. As far as I remember it had performance reasons why we had the first one with an early return.

alikon commented 3 weeks ago

@softarius if you are happy with #44384 please test it and close this issue thank in adavance

richard67 commented 3 weeks ago

Closing as having a pull request. Please test #44384 . Thanks in advance, and thanks for reporting the issue.