Search results are sorted by matching Fields

When it comes to searching, I often think of SQL Server'sFulltextsearch(Full-text search) function, which is indeed powerful, but it also requires some tedious preparation work to use, generally small projects or requirements for search (including performance requirements) if it is not very high, it is a little too heavy. You can use SQL to query simple search results. However, a common problem is how to query the search results.Sort by matching field priority.

For exampleProduct table(Products), its field package:Including the product ID,Product Name,Product Category,Product Brand,Product Introduction,Product details.

Field	type
prodid	int
prodname	nvarchar
categoryname	nvarchar
prodbrand	nvarchar
prodintro	nvarchar
proddescription	nvarchar

Now we need to use a keyword to search records that contain this keyword from the products table. All the records that contain this keyword in any of the following fields are listed: prodname, categoryname, prodbrand, prodintro, and proddescription. The search results are sorted by the matching priority of the preceding fields:

1) First, list the records of fields matching the keyword in the prodname field, then list the records matching the keyword in the field categoryname, and so on. Finally, list the records matching the keyword in the field proddescription;

2) In all records of fields that match the keyword in the prodname field, first list the records in which the field categoryname matches the keyword, then list the records in which the field prodbrand matches the keyword, and so on...

3) Sort each record group by rule 2 recursively ......

It is easy to search SQL statements that match all records of the Keyword:

Select * from products where prodname like '% keyword %' or categoryname like '% keyword %' or prodbrand like '% keyword %' or prodintro like '% keyword %' or proddescription like' % keyword %'

However, it is a little difficult to sort the matching priority of the search results. There are two ways to achieve this sorting in simple SQL search:Authorization MethodAndMulti-field sorting(My blind name ^-^ ).

I. Authorization

CalculateSorting weightThenSorting weightIn descending order. The sorting weight of each searched record isWeight. TheWeightIt depends on whether the field matches the keyword. If it does not match, it is0, If matched, the field is changedMatching weight. FieldMatching weightCalculation method:

Fieldpriority = 2 I times Ming(I indicates the position of this field in the descending order of priority of all searched fields)

For example, in our example, the matching weight of each field is:

Field	Inverted position	Matching weight
Prodname	4	16
Categoryname	3	8
Prodbrand	2	4
Prodintro	1	2
Proddescription	0	1

This is whyAlgorithmTo ensure that a field matches the recordSorting weightNo less than the other oneThis field does not matchHoweverAll subsequent fields matchOfSorting weight. For example, recordOnly match key words in prodname, So itsSorting weightIt is 16, but in record BAll fields except the field prodname matchSorting weightIs 15 (8 + 4 + 2 + 1 = 15 ). However, record a will still be placed before record B.

The corresponding SQL statement is roughly as follows:

Select *,(
(Case when charindex (prodname, keyword)>-1 then 16 else 0 end) +
(Case when charindex (categoryname, keyword)>-1 then 8 else 0 end) +
(Case when 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)
) 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

Ii. Multi-field sorting

The encryption method is a bit wordy. It is better to sort multiple fields by SQL directly to make it clearer and more direct. In fact, we can distribute the weights of each field to the SQL order. The SQL statements are 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, I will post a section that uses this method in nhib.pdf to implement search.Code:

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", nhib.pdf. 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 (expression. Like (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> ();
}