CODEWITHSUNDEEP
sqlruby-on-railspostgresql
Puneet Pandey
Puneet Pandey

Reputation: 555

Postgresql: Sort Results based on maximum number of matches in table columns

EDITED BASED ON COMMENTS

I have 2 tables posts and categories. The structure and sample data are as follows:

POSTS
ID    NAME                  DESCRIPTION              CATEGORY_ID
--  -----------------   -------------------          -----------
1   For Song Lovers     A post all about music            8
2   For Music Lovers    About passion of music            8
3   I love songs        Listing favourite songs           8
4   Rock Music          ImagineDragon                     6
5   Retro Music         Old choice musical themes         7

CATEGORIES
ID    NAME
--  -----------------
6   Artists
7   Entertainment
8   Music

I would like to sort records in such a way that:

  1. Posts with matched keyword in name, description and category name appears first, followed by
  2. Posts with matched keyword in name, description followed by
  3. Posts with matched keyword in name followed by
  4. Posts with matched keyword in description followed by
  5. Posts with matched keyword in category

The resultant output I would like to have is (if keyword=music):

OUTPUT
---------
Post ID#2
Post ID#5
Post ID#4
Post ID#1
Post ID#3

I've been able to write 5 queries and combine it to have unique records. But that is not the optimised solution. Here's what I tried:

(
SELECT "posts".* FROM "posts" INNER JOIN "categories" ON "categories"."id" = "posts"."category_id" WHERE posts.name iLIKE ('%music%') AND posts.description iLIKE ('%music%') AND categories.name iLIKE ('%music%')
+
SELECT "posts".* FROM "posts" WHERE posts.name iLIKE ('%music%') AND posts.description iLIKE ('%music%')
+
SELECT "posts".* FROM "posts"  WHERE posts.name iLIKE ('%music%')
+
SELECT "posts".* FROM "posts"  WHERE posts.description iLIKE ('%music%')
+
SELECT "posts".* FROM "posts" INNER JOIN "categories" ON "categories"."id" = "posts"."category_id" WHERE categories.name iLIKE ('%music%')
).uniq

I even tried to write a single query but that is not returning me the desired result (with obvious reason that I am not using GROUP OR COUNT)

SELECT posts.* FROM posts INNER JOIN categories ON categories.id = posts.category_id
WHERE (
  (posts.name iLIKE ('%music%') AND posts.description iLIKE ('%music%') AND categories.name iLIKE ('%music%'))
  OR (posts.name iLIKE ('%music%') AND posts.description iLIKE ('%music%'))
  OR (posts.name iLIKE ('%music%'))
  OR (posts.description iLIKE ('%music%'))
  OR (categories.name iLIKE ('%music%')))

Please suggest how can I achieve the same with above SQL.

Environment Details:

Database: PostgreSQL

Version: postgres (PostgreSQL) 9.4.5

Upvotes: 0

Views: 428

Answers (3)

Puneet Pandey
Puneet Pandey

Reputation: 555

Thanks @juan-carlos-oropeza and @dnoeth for your responses. Based on your suggestions and snippet, I have come up with below and able to solve this

ORDER BY
  CASE WHEN posts.name iLIKE ('%music%') AND posts.description iLIKE ('%music%') AND categories.name iLIKE ('%music%') THEN 0 END,
  CASE WHEN posts.name iLIKE ('%music%') AND posts.description iLIKE ('%music%') THEN 1 END,
  CASE WHEN posts.name iLIKE ('%music%') THEN 2 END,
  CASE WHEN posts.description iLIKE ('%music%') THEN 3 END,
  CASE WHEN categories.name iLIKE ('%music%') THEN 4 END

Upvotes: 0

dnoeth
dnoeth

Reputation: 60482

As far as I know PostgreSQL supports booleans, so this should match your description, sort by seperate columns:

SELECT posts.* FROM posts INNER JOIN categories ON categories.id = posts.category_id
WHERE  -- can be simplified to 
      LOWER(posts.name)        iLIKE ('%music%')
   OR LOWER(posts.description) iLIKE ('%music%')
   OR LOWER(categories.name)   iLIKE ('%music%')
ORDER BY -- FALSE probably sorts lower than TRUE, thus DESC
  LOWER(posts.name)        iLIKE ('%music%') DESC, posts.name,
  LOWER(posts.description) iLIKE ('%music%') DESC, posts.description
  LOWER(categories.name)   iLIKE ('%music%') DESC, categories.name

Another remark: iLike is case insensitive, why do you apply LOWER then? This should return the same result:

SELECT posts.* FROM posts INNER JOIN categories ON categories.id = posts.category_id
WHERE  -- can be simplified to 
      posts.name        iLIKE ('%music%')
   OR posts.description iLIKE ('%music%')
   OR categories.name   iLIKE ('%music%')
ORDER BY -- FALSE probably sorts lower than TRUE, thus DESC
   posts.name        iLIKE ('%music%') DESC, posts.name,
   posts.description iLIKE ('%music%') DESC, posts.description
   categories.name   iLIKE ('%music%') DESC, categories.name

And now combine this with @JuanCarlosOropeza's answer :-)

Upvotes: 3

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

 ORDER BY 
       CASE WHEN LOWER(posts.name) iLIKE ('%music%') THEN posts.name END,
       CASE WHEN LOWER(posts.description) iLIKE ('%music%') THEN posts.description END,
       CASE WHEN LOWER(categories.name) iLIKE ('%music%') THEN categories.name END,

Upvotes: 2

Related Questions