Calculate weight for entries and order it for search

Question

Hello

I have these tables:

businesses

• :name
• :rating

categories

• :name

business_categories

• :business_id
• :category_id

reviews

• content
• business_id

Now I want to do a search, when type keywords, match it in these columns:

• business.name
• business.category.name
• business.review.content

Then order the results by the weight, the weight calculation will be something like

weight = business name matchs * w1 + business rating * w2 + category name matchs * w3 + review content matchs * w4

so I got several questions here:

• Is there a easy way to figure out these weights(w1, w2 etc..)?
• Is it possible to do the search by using sql query?
• Is there a way to cache the weights? If it require to calculate the weight for each search, the speed will not be acceptable.

Answer 1

Only you can know what the weights are supposed to be and you might set them by trial and error until you're satisfied with the results.

You could try querying using SQL with something like where business.name in (search keywords list) (repeating this for categories and reviews...not sure if it's the best way to go about it, though.

Finally, I don't see a way for you to cache anything because you have an infinite number of inputs and the summary weight of each query result is a function of the input.