CODEWITHSUNDEEP
sql
Dendory
Dendory

Reputation: 630

Finding "related posts" in a database

So quick question: I'm working on adding "related posts" for my site, using my own custom CMS I built. Right now I'm trying to have it done in a single SQL query, where it would return the 3 top results. I don't use tags btw. This is what I came up with so far, and it's doing a decent result, but I'd like to somehow prioritize keywords that appear in titles versus descriptions. Anyone can think of a better way?

SELECT * FROM db WHERE title LIKE "%keyword1%" OR title LIKE "%keyword2%" OR description LIKE "%keyword1%" OR description LIKE "%keyword2%" LIMIT 3;

Upvotes: 0

Views: 908

Answers (4)

Senad Meškin
Senad Meškin

Reputation: 13756

You can do it with the "case"

SELECT *, case WHEN title like '%keyword1%' or title like  '%keyword2%' THEN 1
  ELSE 0 END as Priority
 FROM db WHERE title LIKE '%keyword1%' OR title LIKE '%keyword2%' OR description LIKE '%keyword1%' OR description LIKE '%keyword2%' LIMIT 3 Order by Priority desc

so if title contains keyword set Priority to 1, otherwise set to 0 and then order it by Priority

Upvotes: 1

ivanatpr
ivanatpr

Reputation: 1880

If you happen to be using Oracle DB, then you can use Oracle Text queries to do this sort of thing very efficiently. It'll let you rank results based on relatedness to keywords.

Upvotes: 0

Keith
Keith

Reputation: 2628

If you want to prioritize, you could assign some weight to each LIKE match. Something like this:

SELECT TOP 3 * FROM
(
  SELECT *, 1 'Weight' FROM db WHERE title LIKE '%keyword1' OR title LIKE '%keyword2'
  UNION 
  SELECT *, 2 'Weight' FROM db WHERE description LIKE '%keyword1' OR description LIKE '%keyword2'
)T1
ORDER BY Weight ASC

Upvotes: 0

Larry Lustig
Larry Lustig

Reputation: 50970

UNION together two internal SELECTs, one for the keyword matches and one for the description matches. In each of those internal SELECTs, assign a new priority column with value 1 for the keyword SELECT and 2 for the description SELECT.

Then, from the UNIONed results, SELECT the top 3 ORDER BY priority.

I'd give an example for your database / data except that you didn't give much of a hint about what database you're using, or about what your data looks like.

Upvotes: 0

Related Questions