Skip to content

Instantly share code, notes, and snippets.

@jdevalk
Created April 20, 2012 06:11
Show Gist options
  • Save jdevalk/2426489 to your computer and use it in GitHub Desktop.
Save jdevalk/2426489 to your computer and use it in GitHub Desktop.
Search query
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment