Search results sorted by matching fields

When it comes to search, it is generally reminiscent of SQL Server's Fulltextsearch (full-text search) feature, which is really powerful, but it also needs to do some more tedious preparation work, Generally small projects or the search requirements (including performance requirements) is not very high in the case of practical it is still a bit too heavy. Simple search with SQL query can be, but a general problem is how to search results by matching fields to prioritize.

For example, there is a product table (products), its field package: including Product ID, product name, product category, product brand, product description, product details.

We now require that a keyword be searched from the Products table for records containing that keyword, and that any of the following fields containing the key are listed: ProdName, CategoryName, Prodbrand, Prodintro, Proddescription. And the search results are sorted according to the matching precedence of the preceding fields:

1 list field prodname match keyword record, then list field CategoryName match key word record, and so on, finally list field proddescription match keyword record;

2 in all records of the field prodname matching keywords, list the fields CategoryName also match the keyword records, and then list the fields Prodbrand also match the records of the keyword, and so on ...

3 recursively sort each record group according to rule 2 ...

Searching for an SQL statement that matches all the records of that keyword is simple:

SELECT * from the products WHERE prodname like '%keyword% ' or CategoryName like '%keyword% ' or prodbrand like '%keyword% ' or Prodintro like '%keyword% ' or proddescription like '%keyword% '

However, it is somewhat difficult to prioritize the results of the search. There are two ways to do this in a simple SQL search: Weighted method and multiple-field sorting (my name is a blind one).

Weighted method

Calculates a sort weight for each record of the search, and then sorts all search results in descending order by this sort weight value. The sort weights for each record being searched are the sum of the weights of all the fields in the record. The weight of a field depends on whether the field matches a keyword, or 0 if it does not match, or if the match is a matching weight for the modified field. The matching weights for the fields are calculated as follows:

Fieldpriority = 2 of the I (i) for this field in all the searched field priority order in the position of the inverted row

For example, the matching weights for each field in our example are:

Field

Inverted position

Matching weights

ProdName

4

16

CategoryName

3

8

Prodbrand

2

4

Prodintro

1

2

Proddescription

0

1

This algorithm is used to ensure that a field matches a record with a sort weight that is no lower than another record that does not match the field but subsequent fields match. For example, record a only prodname matching keywords, so it has a sort weight value of 16, and record B has a value of 15 (8+4+2+1=15) for all other fields except the field prodname. But record a still ranks ahead of record B.

The corresponding SQL is roughly as follows:

SELECT *, (
                      case when CharIndex (Prodname,keyword) >-1 then else 0-end) +
                      case When CharIndex (Categoryname,keyword) >-1 then 8 else 0 end) +
                      case CharIndex (Prodbrand,keyword) >-1 then 4 else 0 end) +
                      case when CharIndex (Prodintro,keyword) >-1 then 2 else 0-end) +
                      ( Case when CharIndex (Proddescription,keyword) >-1 then 1 else 0-end)
  &Nbsp;              )   as OrderPriority
From Products
WHERE prodname like '%keyword% ' or
             CategoryName like '%keyword% ' or
             Prodbrand like '%keyword% ' or
            Prodintro like '%keyword% ' or
            Proddescription like '%keyword% '
ORDER by orderpriority Desc

Two, multi-field sorting method

The weighting method is a bit verbose, so it's better to use SQL directly to sort multiple fields to make it clearer and more straightforward. In fact, we scatter the weights of each field to the SQL order, roughly SQL as follows:

SELECT *
from Products
WHERE prodname like '%keyword% ' or
             CategoryName like '%keyword% ' or
             prodbrand like '%keyword% ' or
             Prodintro like '%keyword% ' or
            Proddescription like '%keyword% '
Order by        (case when CharIndex (prodname , KeyWord) >-1 then 0 else 1 end),
                      case when CharIndex (categoryname,keyword) >-1 then 0 else 1 end),
                      (case when CharIndex (prodbrand,keyword) >-1 then 0 else 1 end),
                      (case when CharIndex (prodintro,keyword) >-1 then 0 else 1 end),
                      Case When CharIndex ( Proddescription,keyword) >-1 then 0 else 1 end)

By the way, put a piece of code in NHibernate that uses this idea to implement the search:

Public list<products> searchproducts (string keyWord, int currentpageindex, int pageSize, out int recordCount)
{
String Likekeyword = String.Format ("%{0}%", KeyWord);
String] Matchfields = new string] {"ProdName", "Prodmodel", "Trademarkname", "Keywords", "Producter", "Prodintro", " Proddescription "/*," Cate. Catename "* */};

Icriteria crit = Session.createcriteria (typeof (Products));

Inner Join ProductCategory to search CategoryName
Crit. CreateAlias ("Cateid", "Cate", NHibernate.SqlCommand.JoinType.InnerJoin);

          //Set query condition.
           Crit. ADD (Restrictions.eq ("isdisabled", true);            

          //Add ' or ' SQL
            if (matchfields.length > 0)
            {
               Disjunction orexpression = new disjunction ();
               foreach (String Strfield In Matchfields)
               {
                   Orexpression.add (Expression.like (Strfield, Likekeyword));
              }
               Crit. ADD (orexpression);
          }           

Copy current Icriteria instance to the new one for getting the pagination records.
Icriteria Pagecrit = Criteriatransformer.clone (crit);

Get the total record count
RecordCount = Convert.ToInt32 (crit. Setprojection (Projections.rowcount ()). Uniqueresult ());

Set order parameter.
foreach (String Strfield in Matchfields)
{
Pagecrit.addorder (Order.asc projections.conditional (Strfield, Likekeyword), Projections.Constant (0), projections.constant (1)));
}
Pagecrit.addorder (New Order ("CreateDate", false);

Set pagination
Pagecrit.setfirstresult ((currentPageIndex-1) * pageSize). Setmaxresults (pageSize);

           return pagecrit.list<products> (). Tolist<products> ();
      }