sql - Query and sort by relevance -

sql - Query and sort by relevance -

i have multiple conditions in query this:

select * image name '%text%' , group_id = 10 limit 1

the statements consist of 3 conditions:

text match match of foreign key

what if want sort result relevance, depending on:

how exact text matched how much conditions met @ (e.g. text match and foreign key)

this 2 questions in 1 think times these in handy in combination. i'm referring question arising former post of mine (way seek multiple selects till result available?).

thanks in advance!

to know how text matched, need fuzzystrmatch postgresql module. provides functions difference , levenshtein give estimation of similarity between 2 strings.

then, can build query conditions like:

select * image name '%text%' , ( group_id = 10 or second_field = 4 or thirth_field = 5 ) order ( (group_id=10)::int + (second_field=4)::int + (thirth_field=5)::int ) * weight_1 + (strlen(name)-levenshtein('text',name))* wheight_2

you can adjust weight_1 , weight_2 give preference text distance or number of conditions met.

sql postgresql postgresql-performance