- fulltext search does not present relevant results when ordering by [rating]; I explain why at the bottom of this post after the details.
- [rating] is an average of many ratings and is stored as a floating point number from 0 to 9
Info:
I have the following fields in table_1:
[name]
[category]
[address]
[city]
[state]
[info]
[rating]
I'm using fulltext search to generate results from two variables: keyword_1 and keyword_2.
- keyword_1 is used to match against [name],[category] and [info]
- keyword_2 is used to match against [address],[city] and [state]
- why is there another match against for keyword_2? Because I would like to increase the weighting for a positive match against [city] in addition to the above
- what is $orderby_modifier? Modifies "ORDER BY" to either sort by rating first or relevance first
Code: Select all
SELECT *,
MATCH ('table_1.name, table_1.category, table_1.info') AGAINST ('keyword_1') +
MATCH ('table_1.address, table_1.city, table_1.state') AGAINST ('keyword_2') AS key_1_plus_key_2_score,
MATCH ('table_1.city') AGAINST ('keyword_2') AS key_2_city_score
WHERE
MATCH ('table_1.name, table_1.category, table_1.info') AGAINST ('keyword_1') AND
MATCH ('table_1.address, table_1.city, table_1.state') AGAINST ('keyword_2') AND
MATCH ('table_1.city') AGAINST ('keyword_2')
$orderby_modiferCode: Select all
ORDER BY key_1_plus_key_2__score DESC, rating DESCCode: Select all
ORDER BY rating DESC, key_1_plus_key_2 DESCThe database has thousands of records. If user searches for keyword_1="something", keyword_2="somewhere", the script returns thousands of results, starting with the highest score down to the lowest. That's fine.
However, if the user wants to sort relevant results by [rating], irrelevant results sometimes appear at the top. For example, if keyword_1="nothing",keyword_2="nowhere" had the highest rating, even though it has a small total score, it'll be ordered first.
I would like to ORDER BY rating for only relevant results, if the sample is large. I assume (although I could be wrong), that the solution would entail comparing the MAX and MIN scores, then stripping out x% of the MIN scores if (MAX_score-MIN_score)>n .
I've stared at this too long... wondering if someone else is up to trying. In all probability I'm looking at this the wrong way.
I'll pay $50-75 depending on the complexity of the solution. If it works, I'll paypal you the funds.
Let me know if I need to clarify any points.
Thanks