Page 1 of 1
Full text search queries in KB
Posted: Tue Mar 27, 2012 4:40 pm
by araviski
Script URL: hesk (paied licence available in mp)
Version of script: 2.3
Hosting company: myself
What terms did you try when SEARCHING for a solution:
Full text search
search text behaviour
Write your message below:
Before writting an addon, I'm curious in how works the mysql Full Text search query in Hesk. I have read the source, and it seems that neither MATCH relevant result nor rating / votes / views columns are taken into account (no order clause).
Does any body from phpjunkyard or anybody else could confirm it ?
Is their any reason ? Do you think selecting the Match relevant result multiplied by the rating / votes / views would have an effect ?
Thank you in advance for your comments
Re: Full text search queries in KB
Posted: Wed Mar 28, 2012 7:50 am
by Klemen
MATCH is indeed used on the "subject" and "content" columns for KB articles:
Code: Select all
MATCH(`subject`,`content`) AGAINST (\''.hesk_dbEscape($query).'\')
Votes/ratings/views are not taken into account. I'm not sure using them would add to the relevancy of the results as you don't expect the knowledgebase to have several "competing" articles on the same subject.
Re: Full text search queries in KB
Posted: Wed Mar 28, 2012 8:43 am
by araviski
Thank you for your comment. I really appreciate
Sure articles need to be pertinent and content should be more pertinent than rating/view/votes, at least perhaps a sort on the relevance (how much search terms is present in result) might be helpfull, since I have a lot of results matching the same key words.
I was thinking to something like
Code: Select all
SELECT ... MATCH(`subject`,`content`) AGAINST (\''.hesk_dbEscape($query).'\') as relevancy FROM .... WHERE ... AND MATCH(`subject`,`content`) AGAINST (\''.hesk_dbEscape($query).'\') ORDER BY relevancy DESC
And if not enough, something like this to add rank based on the title
Code: Select all
SELECT ... MATCH(`subject`,`content`) AGAINST (\''.hesk_dbEscape($query).'\') + MATCH(`subject`) AGAINST (\''.hesk_dbEscape($query).'\') as relevancy FROM .... WHERE ... AND MATCH(`subject`,`content`) AGAINST (\''.hesk_dbEscape($query).'\') ORDER BY relevancy DESC
My original idea, something like this to add order based on the notes
Code: Select all
SELECT ... MATCH(`subject`,`content`) AGAINST (\''.hesk_dbEscape($query).'\')*() as relevancy FROM .... WHERE ... AND MATCH(`subject`,`content`) AGAINST (\''.hesk_dbEscape($query).'\') ORDER BY relevancy DESC, views ASC, rating ASC, votes ASC
Or much more complicated by creating a relevancy factor from the view/rating/votes, but much more complicated and heuristic.
Re: Full text search queries in KB
Posted: Thu Mar 29, 2012 3:21 pm
by Klemen
Yes, order by relevancy is the default MySQL behavior for fulltext searches.
You idea would only work if two results returned the exact same relevancy - if not all other "order by" don't have any effect.