(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.
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.
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.
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.
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.
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.
Labels |
Added:
?
?
|
Category | ⇒ | com_search |
Status | New | ⇒ | Discussion |
It would seem reasonable to me to match the order in the current query to resolve that part of the issue
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 ?
I'd rather drop com_search and instead get com_finder finally working correctly.
I'd rather drop com_search and instead get com_finder finally working correctly.
You mean fix similar issue in com_finder ?
https://brian.teeman.net/component/finder/search?q=search+replace
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
(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
oops... i've used UNION ALL in the snippet my bad
... i like your suggestion
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 ?
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 ?
yes i'll do
Status | Discussion | ⇒ | Closed |
Closed_Date | 0000-00-00 00:00:00 | ⇒ | 2018-04-18 17:45:08 |
Closed_By | ⇒ | franz-wohlkoenig |
Closed_By | franz-wohlkoenig | ⇒ | joomla-cms-bot |
Set to "closed" on behalf of @franz-wohlkoenig by The JTracker Application at issues.joomla.org/joomla-cms/20175
awesome
what should be the order ?