MySQL

Union Site Search with Relevance

Please support our MySQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

Thread Solved

•

Join Date: May 2008

Posts: 45

Reputation: DiGSGRL is an unknown quantity at this point

Solved Threads: 4

DiGSGRL

Offline

Light Poster

Union Site Search with Relevance

Dec 31st, 2008

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:

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
(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%') ))
(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:

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
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
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:

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
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)
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!

•