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');
 

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?

@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.

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

/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

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

@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.

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

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

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.

@richard67 Yes, you are right. Only select distinct trm from select ( works
(#44373 (comment))

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.

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

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