CODEWITHSUNDEEP
sphinx
Yau Leung
Yau Leung

Reputation: 678

Many-to-Many searching with Sphinx

I would like to use Sphinx for many to many matching. I took the questions and tags case here in StackOverflow for illustration.

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

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

I would like to search for questions which contains as many tags in the set "java", "sphinx", "mysql", "hibernate" as possible. So the result might come up with questions with 3 matches, 2 matches or 1 match.

Currently, I create a field which concat all these tags with space and have sphinx search that field. But it sounds silly and create a lot of overhead when adding and removing tags. There gonna be some smarter way, right?

Upvotes: 4

Views: 1502

Answers (2)

Ian
Ian

Reputation: 1622

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

I have used this in the past to search "interests" against a "person". So I may like rock music and watching rugby. Sphinx can index this in an array and you can search them using OR or AND matching.

Upvotes: 4

Charly
Charly

Reputation: 1

Easiest should be to just join the tables and group by the 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 
}

that assumes, that you've got the following columns in your tables

question table:

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

Upvotes: 0

Related Questions