? ?
avatar brianteeman
brianteeman
16 Apr 2018

TL:DR

  • Ordering can only be set in the menu item so if you dont have a search menu item and only a search module there is no way to order the results
  • Search hits in titles have no greater priority than search hits at the bottom of an article

Details

(copy pasted from a blog post on brian.teeman.net from last year)

It has bugged me for a long time that the search on my site is broken. I've never had the time to work out why but today I did so let me share it with you so that you can fix it on your site as well.
We all know that for google and other search engines the most important search term will be in your heading. So if you have two articles that match the search term the article with the search term in the title will appear first.

Now try to use the search module on my site for the search term replace.

If you scroll down and down and down you will find an article called "Search and Replace" at about position 18. That is the only article with the search term in the title so why is it so far down the search results?
I had assumed that this was a bug in the core Joomla code. That the search plugin was perhaps not looking in the title field or perhaps it was looking at the title field after the content field. But that turns out not to be the case.

foreach ($words as $word)
{
$word = $db->quote('%' . $db->escape($word, true) . '%', false);
$wheres2 = array();
$wheres2[] = 'LOWER(a.title) LIKE LOWER(' . $word . ')';
$wheres2[] = 'LOWER(a.introtext) LIKE LOWER(' . $word . ')';
$wheres2[] = 'LOWER(a.fulltext) LIKE LOWER(' . $word . ')';
$wheres2[] = 'LOWER(a.metakey) LIKE LOWER(' . $word . ')';
$wheres2[] = 'LOWER(a.metadesc) LIKE LOWER(' . $word . ')';
$wheres2[] = 'LOWER(fv.value) LIKE LOWER(' . $word . ')';
$wheres[] = implode(' OR ', $wheres2);
}

 
As you can see in the code above it is searching in the title and it is searching there for the search term before anywhere else.

So what was the problem?

Looking a ittle further down the content search plugin I saw this code.

switch ($ordering)
{
case 'oldest':
$order = 'a.created ASC';
break;

case 'popular':
$order = 'a.hits DESC';
break;

case 'alpha':
$order = 'a.title ASC';
break;

case 'category':
$order = 'c.title ASC, a.title ASC';
break;

case 'newest':
default:
$order = 'a.created DESC';
break;
}

So here you can see that by default the search results are ordered by the creation date of the article so that any article created yesterday will always display in the search results before an older article EVEN if the search term is in the title and therefore more important. That is exactly the problem I am facing. The "Search and Replace" article is quite old. But for me I think it still should display first as the term is in the title and so it is more important than an article where the term is six paragraphs in.

Setting the Sort Options

Now comes the interesting part. When I saw the code above I assumed that this meant there was a parameter in the plugin where I could set the default ordering to be used. So in my case I would change from the default "newest" to the "alpha" option and then I would have search working exactly the way that I want it to work. But guess what? There is no such option in the plugin at all.

search plugin options

Could this option really not be used at all? Surely not?

So if its not in the content-search plugin options it must be in the search module options. That would be the next most logical place for it to be wouldn't it. After all - there is no point in having the content-search plugin enabled if you dont have a search module on the site.

No joy here either - the module doesnt have any ordering options either. Wow - all this code in the plugin and it's just not used at all. Buit wait that can't be right can it. No one writes code that can never be used - that would just be stupid.

There is one last place that we can setup search on a joomla site. From a menu item. Perhaps it is there. Well it turns out that it is used - just not where I expected it to be and probably not somewhere you even have on your site. It's in the options for the search menu item.

search menu option

Success?

So now my search results will be ordered how I expect them to be and the article with the search term in the title will be first on the search results? The non-developers amongst us would expect that wouldn't we? We have conducted a search and said to sort the results by title.

Well that is exactly what is happening - the results are being sorted alphabeticaly by title. Just not how I was expecting. the code says to sort the results by the order of the title. My article begins with the letter S so it is sorted near the end and not at the top - it actually moves up about one place on this site to about 17 but still way below where I was expecting it. Try it yourself here https://brian.teeman.net/search and set the ordering to Alphabetical.

I was expecting it sort the results by matches in the title and then by matches in the content. But that's not what the code says or does.

Failure

So how can I get Joomla to give me search results that follow established practices of making matches in the title more important? With the current code in the Joomla default search component it's just not possible. Even the hidden and hard to use options don't help you.

avatar brianteeman brianteeman - open - 16 Apr 2018
avatar joomla-cms-bot joomla-cms-bot - change - 16 Apr 2018
Labels Added: ? ?
avatar joomla-cms-bot joomla-cms-bot - labeled - 16 Apr 2018
avatar joomla-cms-bot joomla-cms-bot - labeled - 16 Apr 2018
avatar franz-wohlkoenig franz-wohlkoenig - change - 16 Apr 2018
Category com_search
avatar franz-wohlkoenig franz-wohlkoenig - change - 16 Apr 2018
Status New Discussion
avatar alikon
alikon - comment - 17 Apr 2018

what should be the order ?

  1. title
  2. introtext
  3. fulltext
  4. metakey
  5. metadesc
avatar brianteeman
brianteeman - comment - 17 Apr 2018

It would seem reasonable to me to match the order in the current query to resolve that part of the issue

avatar ggppdk
ggppdk - comment - 17 Apr 2018

title
introtext
fulltext
metakey
metadesc

If ordering all of them like above would impact performance
then i would say only do this for title, it is by far the most important

Also for title the ordering , e.g. creation date ordering would not "feel" broken, as the text is shown inside the title, doing same prioritizing for order for the rest of cases , it would start looking weird / broken ?

avatar Hackwar
Hackwar - comment - 17 Apr 2018

I'd rather drop com_search and instead get com_finder finally working correctly.

avatar ggppdk
ggppdk - comment - 17 Apr 2018

I'd rather drop com_search and instead get com_finder finally working correctly.

@Hackwar

You mean fix similar issue in com_finder ?
https://brian.teeman.net/component/finder/search?q=search+replace

avatar alikon
alikon - comment - 17 Apr 2018

so suppose this is the sql query currently runned by search content plugin

SELECT a.title AS title, a.metadesc, a.metakey, a.created AS created, a.language, a.catid,CONCAT(a.introtext,a.fulltext) AS text,c.title AS section

  FROM j390_content AS a

  INNER JOIN j390_categories AS c 
  ON c.id=a.catid

  WHERE ((LOWER(a.title) LIKE LOWER('%JOOMLA%') OR LOWER(a.introtext) LIKE LOWER('%JOOMLA%') OR LOWER(a.fulltext) LIKE LOWER('%JOOMLA%') OR LOWER(a.metakey) LIKE LOWER('%JOOMLA%') OR LOWER(a.metadesc) LIKE LOWER('%JOOMLA%'))) 
  AND a.state=1 
  AND c.published = 1 
  AND a.access IN (1,1,2) 
  AND c.access IN (1,1,2)

  GROUP BY a.id, a.title, a.metadesc, a.metakey, a.created, a.language, a.catid, a.introtext, a.fulltext, c.title, a.alias, c.alias, c.id

  ORDER BY a.created DESC 

to prioritize title match result we can do an UNION ALL like this

SELECT 1, a.title AS title, a.metadesc, a.metakey, a.created AS created, a.language, a.catid,CONCAT(a.introtext,a.fulltext) AS text,c.title AS section
  

  FROM j390_content AS a

  INNER JOIN j390_categories AS c 
  ON c.id=a.catid

  WHERE (LOWER(a.title) LIKE LOWER('%JOOMLA%') ) 
  AND a.state=1 
  AND c.published = 1 
  AND a.access IN (1,1,2) 
  AND c.access IN (1,1,2)
  

  GROUP BY a.id, a.title, a.metadesc, a.metakey, a.created, a.language, a.catid, a.introtext, a.fulltext, c.title, a.alias, c.alias, c.id
  
  UNION ALL
  
SELECT 2, a1.title AS title, a1.metadesc, a1.metakey, a1.created AS created, a1.language, a1.catid,CONCAT(a1.introtext,a1.fulltext) AS text,c1.title AS section
  FROM j390_content AS a1
  INNER JOIN j390_categories AS c1 
  ON c1.id=a1.catid

  WHERE (LOWER(a1.introtext) LIKE LOWER('%JOOMLA%') OR LOWER(a1.fulltext) LIKE LOWER('%JOOMLA%') OR LOWER(a1.metakey) LIKE LOWER('%JOOMLA%') OR LOWER(a1.metadesc) LIKE LOWER('%JOOMLA%')) 
  AND a1.state=1 
  AND c1.published = 1 
  AND a1.access IN (1,1,2) 
  AND c1.access IN (1,1,2)

  GROUP BY a1.id, a1.title, a1.metadesc, a1.metakey, a1.created, a1.language, a1.catid, a1.introtext, a1.fulltext, c1.title, a1.alias, c1.alias, c1.id
  ORDER BY 1, created DESC

should be a good trade off ? @ggppdk

avatar ggppdk
ggppdk - comment - 17 Apr 2018

(speaking of com_search content search plugin)

This union would mean that you will get a query with duplicate rows
so then you will have to eliminate duplicate and keep order

I would suggest keep the existing query as is
and add to it:

SELECT ...,
  CASE WHEN title LIKE '%$keyword%' THEN 1 ELSE 0 END AS priority
...
ORDER BY priority DESC, ...  (the configured ordering here)

simple solution with small performance impact

avatar alikon
alikon - comment - 18 Apr 2018

oops... i've used UNION ALL in the snippet my bad
... i like your suggestion

avatar ggppdk
ggppdk - comment - 18 Apr 2018

i've used UNION ALL in the snippet my bad

hhmm right, but still then UNION will be more work
it will eliminate duplicates
but it does not guarantee order after removal of duplicates (right ?)

So you will need ?
SELECT 1 as prority, ... ( in the first one select)
SELECT 2 as prority, ... ( in the second select)

and then order the UNION' s result set by
ORDER BY priority, ... + again the configured order

[EDIT]
the above is wrong way, it will prevent duplicate row elimination

plus for limit 50 you will not get e.g. 50 or less
but you will get 100 or less

so solution with UNION will work but it is more work and a little slower ?

avatar ggppdk
ggppdk - comment - 18 Apr 2018

@alikon

i tested already this solution in my search plugin

SELECT ...,
  CASE WHEN title LIKE '%$keyword%' THEN 1 ELSE 0 END AS priority
...
ORDER BY priority DESC, ...  (the configured ordering here)

and it works
(except i am not using LIKE (my plugin only works in MySql ...))

will you make PR for Joomla content search plugin ?

avatar alikon
alikon - comment - 18 Apr 2018

yes i'll do

avatar alikon
alikon - comment - 18 Apr 2018

please look at #20197

avatar franz-wohlkoenig franz-wohlkoenig - change - 18 Apr 2018
Status Discussion Closed
Closed_Date 0000-00-00 00:00:00 2018-04-18 17:45:08
Closed_By franz-wohlkoenig
avatar joomla-cms-bot joomla-cms-bot - change - 18 Apr 2018
Closed_By franz-wohlkoenig joomla-cms-bot
avatar joomla-cms-bot joomla-cms-bot - close - 18 Apr 2018
avatar joomla-cms-bot
joomla-cms-bot - comment - 18 Apr 2018
avatar franz-wohlkoenig
franz-wohlkoenig - comment - 18 Apr 2018

closed as having Pull Request #20197.

avatar brianteeman
brianteeman - comment - 18 Apr 2018

awesome

Add a Comment

Login with GitHub to post a comment