At 11:01 -0400 10/9/07, Baron Schwartz wrote:
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.
Actually, your pointing me towards UNION may have done the trick. I read
up on it on the MySQL docs site and I've ended up with this, which
actually covers more tables and fields than in my original post:
-----------
select distinct tb.speaker_id, tb.fore, tb.sur, tb.division from
(
(
select 1 as relevance, speaker_id, fore, sur, division
from speakers
where fore like '%education%' or sur like '%education%')
union
(
select 2 as relevance, s.speaker_id, fore, sur, division
from speakers s, speakers_topics st, topics t
where st.speaker_id = s.speaker_id and t.topic_id = st.topic_id and
topic like '%education%'
)
union
(
select 3 as relevance, speaker_id, fore, sur, division
from speakers where match (strap, shortbio, longbio) against ('education')
)
union
(
select 4 as relevance, s.speaker_id, fore, sur, division
from speakers s, articles a
where s.speaker_id = a.speaker_id and match (title, article) against
('education')
)
union
(
select 5 as relevance, s.speaker_id, fore, sur, division
from speakers s, other o
where s.speaker_id = o.speaker_id and match (title, article) against
('education')
)
union
(
select 6 as relevance, speaker_id, fore, sur, division
from speakers, books
where speaker_id = author and match (title, description) against
('education')
)
order by relevance, division, sur, fore
) as tb
-----------
First, I did it without the outer select, and I got speakers repeated if
they were matched in more than one block. One of the comments on the
MySQL docs site suggested the 'wrapper', which I did initially like this:
select distinct speaker_id, fore, sur, division from... with nothing
after the final ')'. This gave me an error to the effect that derived
tables must always have an alias. What the hey, let's just try it like
this (the above)... and to my astonishment it worked!
So before I sign off on this thread, can you see any way I could improve
this?
Naturally, I haven't yet incorporated the treatment of more than one
search term, but I'll try and work that out for myself. :-)
