Chris Sansom wrote:
> I'm sure there must be an accepted technique for this, but it's
> something I haven't tried before, so if anyone can point me in the right
> direction I'd be grateful.
>
> I'm writing a search facility for a site where the data is stored in
> several tables - let's say 5 for this example - and I want to order my
> results according to where (if anywhere) matches are found. So...
>
> Let's say I have tables 'speakers', 'topics', 'speakers_topics',
> 'articles', 'other'.
> 'speakers' is a table of speakers, with id, name and some text fields.
> 'topics' is a list of topics they address
> 'speakers_topics' relates the above two by pairs of id numbers
> 'articles' and 'other' are further tables of text data with possibly
> more than one row for some speakers, identified by id.
>
> I want to search the data in the following order:
> name from 'speakers'
> topics
> text data from 'speakers'
> text data from 'articles' and 'other'
> ...and order the results according to where in that hierarchy a match is
> found.
>
> So, if the user's search term matches one speaker's name field,
> another's topic and someone else's text data, that's the order in which
> the results should be ordered. Also, if the same person is matched from,
> say, both name and text fields (which is very likely, as their name will
> almost certainly appear in some of the text), the name should take
> precedence in the ordering.
>
> To complicate matters further, I'd like if possible to extend this to an
> and/or situation. If the user enters two or more words, any results that
> match all the words should be ordered above those that match only some
> of the words.
>
> I can probably do this relatively easily with a series of separate
> queries (I'm doing all this from PHP, by the way), but that strikes me
> as inefficient. Can it all be done in one big query, perhaps with
> subqueries?
I've built similar systems with a series of UNION queries. Each UNION
has a column for "relevance", which can be a sum of CASE statements,
such as
IF(<name matches>, 1, 0) + IF(<text matches>, 1, 0) AS relevance...
The entire UNION can then be ordered by relevance. You could also just
add in an arbitrary number in each UNION, to get the effect of ordering
by where in the hierarchy the match is found.
Baron
