CODEWITHSUNDEEP
mysqlsqllaravelsql-order-by
Osama Khalid
Osama Khalid

Reputation: 337

SQL Order By on the basis of Matching

I have this table, where I am searching for records based on name, description, category_name. What I want the order of records to be:

  1. Results that matched with name or part of the name,

  2. Then Results that matched with the description as well as category_name,

  3. Results that matched with the only category_name,

  4. Results that matched with the description

Please see that I am trying to order the records based on their match. Simple order by name with sorting them all according to name.

Sample Data:

name,description,category_name

  1. abc,xyz_usama, asz

  2. usama,this is description,person

  3. abc, xyz, usama

  4. jhon,this is usama,person usama

now if I searched using keyword usama: result should be:

2 ( matched with name )

4 ( description + category )

3 ( category )

1 ( description )

Upvotes: 0

Views: 64

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

You can use a case expression`. Your question is not really clear on what the data looks like, but the idea is:

order by (case when name like '%' || :search || '%' 
               then 1
               when category_name like '%' || :search || '%' and
                    description like '%' || :search || '%'
               then 2
               when category_name like '%' || :search || '%'
               then 3
               when description like '%' || :search || '%'
               then 4
               else 5
          end)

Upvotes: 1

Akina
Akina

Reputation: 42728

ORDER BY 
    /* Results that matched with name or part of the name, */
name LIKE 'name_pattern' DESC,
    /* Then Results that matched with the description as well as category_name, */
    /* Results that matched with the only category_name, */
category_name LIKE `category_name_pattern` DESC, 
description LIKE `description_pattern` DESC
    /* Results that matched with the description */
    /* i.e. all another records */

If all pattern are the same substring you may use simple 

ORDER BY LOCATE('pattern', CONCAT(name, category_name, description))

Upvotes: 1

Related Questions