Search code examples
mysqlsearch-engine

How to order tag filtering search result

I am building a search engine which support tag filter.below are the structure of my database which is many to many table

product_table
id   name
1     p1
2     p2
3     p3

tagged_table
id  pid   tid
1    1     1
2    1     2
3    1     3
4    1     4
5    1     5
6    2     1
7    2     2
8    3     1
9    3     2
10   3     4

tag_table
id tag_name
1    t1
2    t2
3    t3
4    t4
5    t5

  • tagged_table.pid are referring to id of product_table

  • tagged_table.tid are referring to id of tag_table

How to build fastest sql query if i want the result return if any of the matches are found and order the result by the number of most case that matched?

Solution

  • Thanks to zerkms i changed my single table to 'many-many relation' table (refer to question) And used a simple query to solve my question.

    SELECT COUNT(pid) AS matches FROM tagged_table WHERE tid IN (1,2,3,4) ORDER BY matches DESC