php - organizing search results based on longest match -

-

i have database table contains project keywords. each project has several keywords.

in web application, user can perform search entering multiple keywords search for. if user enters (for example) 4 keywords, want return search result list of projects. projects should ordered based on longest matches first. first projects listed ones matched on 4 keywords, followed projects matched on 3 out of 4 keywords, etc.

what query can write return results?

this rough idea of think query like:

select projectid    project_keyword   keyword = '*keyword1*'      or keyword = '*keyword2*'     or keyword = '*keyword3*'

but want projectid matches 3 keywords displayed first (if any), followed projectid matched 2 out of 3 keywords, etc. projectid don't have matching keywords not returned.

example:

projectid  keyword  ---------  -------  456       salsa  456       guacamole  456       tamale  511       salsa  511       tamale  511       burrito  511       taco  654       margarita  654       nachos  789       margarita  789       salsa    789       taco

for user keyword search 'salsa','tamale','burrito', query should return these projectid, in order.

projectid ---------  511          (matches three)  456          (matches two)  789          (matches one)

how rows query returned in specified order? (what query can write that.)

something should work:

 select k.projectid    project_keyword k   k.keyword in ('*keyword1*','*keyword2*','*keyword3*')   group k.projectid   order count(distinct k.keyword) desc, k.projectid

(this assumes table contains multiple rows projectid, 1 row each keyword. if that's not how table organized, if it's single row, , looking matching keywords occur somewhere in column, we'd need different query.)

if want return list of keywords matched, use group_concat aggregate in select list

 select k.projectid       , group_concat(distinct k.keyword order k.keyword) keywords_matched    ...

Comments

Post a Comment

Popular posts from this blog

Java 3D LWJGL collision -

-
i making 3d java game lwjgl library, , wondering how add collision detection, player not go through models. i using obj models. here objloader class, loads models: package renderengine; import java.io.bufferedreader; import java.io.file; import java.io.filenotfoundexception; import java.io.filereader; import java.util.arraylist; import java.util.list; import models.rawmodel; import org.lwjgl.util.vector.vector2f; import org.lwjgl.util.vector.vector3f; public class objloader { public static rawmodel loadobjmodel(string filename, loader loader){ filereader fr = null; try { fr = new filereader(new file("res/"+filename+".obj")); } catch (filenotfoundexception e) { system.err.println("couldn't load file!"); e.printstacktrace(); } bufferedreader reader = new bufferedreader(fr); string line; list<vector3f> vertices = new arraylist<vector3f...
Read more

spring - SubProtocolWebSocketHandler - No handlers -

-
i have ugly error during deploying spring app on jboss. 18:11:16,025 error [org.apache.catalina.core.containerbase.[jboss.web].[default-host].[/consumer]] (msc service thread 1-7) exception sending context initialized event listener instance of class org.springframework.web.context.contextloaderlistener: org.springframework.context.applicationcontextexception: failed start bean 'subprotocolwebsockethandler'; nested exception java.lang.illegalargumentexception: no handlers @ org.springframework.context.support.defaultlifecycleprocessor.dostart(defaultlifecycleprocessor.java:176) [spring-context-4.1.3.release.jar:4.1.3.release] @ org.springframework.context.support.defaultlifecycleprocessor.access$200(defaultlifecycleprocessor.java:51) [spring-context-4.1.3.release.jar:4.1.3.release] @ org.springframework.context.support.defaultlifecycleprocessor$lifecyclegroup.start(defaultlifecycleprocessor.java:346) [spring-context-4.1.3.release.jar:4.1.3.release] @ org.s...
Read more

methods - python can't use function in submodule -

-
my folder structure this: pythonstuff/ program.py modulea/ __init__.py foo.py bar.py here's code bar.py : def hello(): print("hello, world!") here's code program.py : #!/usr/bin/env python3 modulea import bar bar.hello() i run $ python3 program.py somehow error: file "program.py", line 3, in <module> bar.hello() attributeerror: 'module' object has no attribute 'hello' edit: __init__.py file empty. edit2: after trying realized had bar.py in root directory contained hello() method. bar.py in modulea/ directory empty. add it import os __all__ = [] module in os.listdir(os.path.dirname(__file__)): if module != '__init__.py' , module[-3:] == '.py': __all__.append(module[:-3]) the init .py files required make python treat directories containing packages; done prevent directories common name, such string, unintentionally hiding v...
Read more