Multiple MySQL Search Queries

Question

I am trying to add a full text search to the system. In the query I want to write, I need to include several searches, followed by a search (if possible).

I have a teacher table and a topic table.

teacherProfile
teacherId [int] - primary key
subjectOneId [int]
subjectTwoId [int]
subjectThreeId [int]
teacherBiography [text]

subjects
subjectId [int]
subjectName [text]

So in the end I want to get a set of results row by row.

teacherId [int]
teacherBiography [text]
( subjectOneName [text] )
( subjectTwoName [text] )
( subjectThreeName [text] )

So, these last three fields in brackets do not exist, but I want to perform a text search on them, I need to set the foriegn key restriction (which I would prefer not to do in case of further impact on the existing system) or is there something more eloquent that I can do?

Answer 1

MySQL , MySQL.

, Sphinx, , JOINS

:

CREATE TABLE ftsearch
        (
        teacherId INT PRIMARY KEY,
        teacherBiography TEXT,
        subject1 TEXT,
        subject2 TEXT,
        subject3 TEXT,
        )
ENGINE=MyISAM

:

INSERT
INTO    ftsearch
SELECT  teacherId, teacherBiography,
        s1.name, s2.name, s3.name
FROM    teacherProfile
LEFT JOIN
        subject s1
ON      s1.id = subjectOneId 
LEFT JOIN
        subject s2
ON      s2.id = subjectTwoId 
LEFT JOIN
        subject s3
ON      s3.id = subjectThreeId 

.

, MyISAM, ( ) .

, '+Jones +math +physics', Jones math physics , :

SELECT  teacherId, teacherBiography,
        s1.name, s2.name, s3.name
FROM    teacherProfile
LEFT JOIN
        subject s1
ON      s1.id = subjectOneId 
LEFT JOIN
        subject s2
ON      s2.id = subjectTwoId 
LEFT JOIN
        subject s3
ON      s3.id = subjectThreeId 
WHERE   MATCH(teacherBiography) AGAINST ('+Jones' IN BOOLEAN MODE)
        AND MATCH(t.teacherBiography, s1.name, s2.name, s3.name) AGAINST ('+Jones +math +physics' IN BOOLEAN MODE)

MATCH(teacherBiography) AGAINST ('+Jones') FULLTEXT teacher, ; MATCH .

, OR , .