Full text search queries in KB

[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

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

[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

Edit: seems that this is the mysql default order when match function is in used.

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.

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