Finding Many With Many With Sphinx

Question

I would like to use Sphinx for many, many matches. I cited questions and tags in StackOverflow for illustration.

A question can be associated with many tags and vice versa for a tag.

So, in mysql I have 3 tables: question, tag, question_tag.

I would like to find questions that contain as many tags in the "java", "sphinx", "mysql", "hibernate" sets as possible. Thus, the result may arise with questions with 3 matches, 2 matches or 1 match.

I am currently creating a field that combines all of these tags with a space and is looking for sphinx in this field. But that sounds silly and creates a lot of overhead when adding and removing tags. There will be some smarter way, right?

Answer 1

Take a look at MVA - Multi Value Attributes, in the MVA documentation and sql_attr_multi .

I have used this in the past to search for "interests" versus "man." Therefore, I may like rock music and watch rugby. Sphinx can index this in an array, and you can search for them using OR or AND.

Answer 2

The easiest way is to simply attach the tables and group by question ID. Sphinx does the rest for you:

source src_questions{ select question_id, question_subject, question_body, tag_value from question \ JOIN question_tag on question.question_id = question_tag.question_id \ JOIN tag on question_tag.tag_id = tag.tag_id GROUP BY question_id } 

which assumes you have the following columns in tables

table of questions:

 question_id INT question_subject VARCHAR or TEXT question_body VARCHAR or TEXT 

tag table:

 tag_id INT tag_value VARCHAR or TEXT 

question_tag_table:

 question_id INT tag_id INT