Search query

gistfile1.sql

SELECT c.Content, c.Title, c.ID, c.CTID, c.RID, r.Sitename, 
CASE WHEN c.Title LIKE '%conversion%' THEN 1 ELSE 0 END AS titlematch, 
CASE WHEN c.Content LIKE '%conversion%' THEN 1 ELSE 0 END AS contentmatch,
MATCH (c.Title, c.Content) AGAINST ('conversion') AS relevance 
FROM Chapters c LEFT JOIN Reviews r 
ON c.RID = r.ID
WHERE MATCH(c.Title, c.Content) AGAINST ('conversion' IN BOOLEAN MODE) 
HAVING relevance > 0  
ORDER BY titlematch DESC, contentmatch DESC, relevance DESC
LIMIT 50