I am building a site search that involves a union of two tables in two databases and needs to have relevancy factored in.
I do not want to use a full-text or boolean search.
I can search one database and one table using relevance fine. I can union the two tables and search without relevance fine. When I put relevance on both tables with the union it no longer works.
Here is the query with union that works:
(SELECT gameID,title FROM searchTest.games
WHERE((title LIKE '%$searchTerm1%' or author LIKE '%$searchTerm1%' or description LIKE '%$searchTerm1%')))
UNION
(SELECT postID,title FROM searchTest2.posts WHERE((title LIKE '%$searchTerm1%' or username LIKE '%$searchTerm1%' or content LIKE '%$searchTerm1%') ))
Here is the query on one table with relevance that works:
SELECT gameID,title, ((2*(title LIKE '%$searchTerm1%'))+(1.5*(author LIKE '%$searchTerm1%'))+(1*(description LIKE '%$searchTerm1%')))AS relevance FROM games WHERE(title LIKE '%$searchTerm1%' or author LIKE '%$searchTerm1%' or description LIKE '%$searchTerm1%')AND locked=0 ORDER BY relevance DESC
Here is the query with union and relevance that does not work:
SELECT gameID,title ((2*(title LIKE '%$searchTerm1%'))+(1.5*(author LIKE '%$searchTerm1%'))+(1*(description LIKE '%$searchTerm1%')))AS relevance
FROM games WHERE
(((title LIKE '%$searchTerm1%' or author LIKE '%$searchTerm1%' or description LIKE '%$searchTerm1%'))AND locked=0)
UNION
SELECT postID,title ((2*(title LIKE '%$searchTerm1%'))+(1.5*(author LIKE '%$searchTerm1%'))+(1*(description LIKE '%$searchTerm1%')))AS relevance
FROM searchTest2.posts WHERE
(((title LIKE '%$searchTerm1%' or username LIKE '%$searchTerm1%' or content LIKE '%$searchTerm1%'))AND locked=0)
I wouldn't be surprised if I did something really silly and screwed up a paren or something. I have been working on this for days and just can't seem to find the mistake that is keeping the union with relevance from running.
Thank you for looking and I hope someone can help!