How to Create a Relevance Based Search Query for SQL

• Download source - 101.04 KB

Introduction

Many a times, we require to implement the Search algorithm in the applications. So in a normal case, we may search the term for various criteria and then find the best match for the Search Query. I also wanted a same thing where I wanted to search the Cars for my application. In my application, I wanted five criteria namely, Brakes, Fuel, Lock, Engine, and Steering. So the customer will select the options from the combo box (only valid values from the columns) and then display the Cars that match maximum features.

Background

For implementing this article, you must be aware of UNION, ORDER BY, GROUP BY operations of clauses and database connection in VB.NET.

Using the Code

We are going to learn two parts:

1. How to load column in the Combo box so that user can select only valid values
2. How to create a Search to get the best result

So starting off with that, I have a small snippet of my code. I just implemented the code as logic. We have five combo boxes from which user will select values and then by searching, the values will be loaded.

Dim connObj As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
	Application.StartupPath & "\Car_Showroom.mdb")
Dim commObj As New OleDbCommand
Dim myDataReader As OleDbDataReader
Dim myDataSet, myDataSet1 As New DataSet
Dim query(3) As String
Dim myAdapter As OleDbDataAdapter

These are the Database Connection objects. Here is the code to load a single Combo with that specific column.

myDataSet1.Clear()
      connObj.Open()
      ''''''' Load Steering
      myDataSet1.Clear() ' clear all the values from Data Set if any
      cmbSteering.Items.Clear() ' clear all the items of the Combo BOx
      commObj = New OleDbCommand("SELECT DISTINCT Steering _
  FROM CarDetails", connObj) ' Create a command object and add connection
      myAdapter = New OleDbDataAdapter(commObj)
      myAdapter.Fill(myDataSet1, "cDetails") ' Execute the Query and
                  ' fill the results in the dataset.
      cmbSteering.Text = "Select Company

' This is the most important code
For Each ds As DataRow In myDataSet1.Tables("cDetails").Rows 
            cmbSteering.Items.Add(ds("Steering").ToString())
        Next 

Explanation of the Above Code

We are creating a DataRow object and then using a For Each Loop we are adding the Steering Column value to the combo box.

This is the next part of the segment. This is what I searched on the internet but couldn't find one. So I worked upon and figured this out as one of the solutions.

Following shows the SQL Query and later its explanation:

Dim steer As String = cmbSteering.SelectedItem
       Dim engine As String = cmbEngine.SelectedItem
       Dim fuel As String = cmbFuel.SelectedItem
       Dim lock As String = cmbLock.SelectedItem
       Dim brakes As String = cmbBrakes.SelectedItem
       myDataSet.Clear()
       connObj.Open()
       query(1) = "SELECT PRODID,Occurence FROM (SELECT ProdId,count(*) _
       AS Occurence FROM (SELECT ProdId FROM CarDetails WHERE _
       Steering = '{0}' UNION ALL SELECT ProdId FROM CarDetails _
       WHERE Engine ='{1}' UNION ALL SELECT ProdId FROM CarDetails _
       WHERE Fuel ='{2}' UNION ALL SELECT ProdId FROM CarDetails _
       WHERE Brakes ='{3}' UNION ALL SELECT ProdId FROM CarDetails _
       WHERE Lock ='{4}' ) Group By ProdID )ORDER BY Occurence DESC"
       query(2) = String.Format(query(1), cmbSteering.SelectedItem, _
       cmbEngine.SelectedItem, cmbFuel.SelectedItem, _
       cmbBrakes.SelectedItem, cmbLock.SelectedItem)
       commObj = New OleDbCommand(query(2), connObj)
       myAdapter = New OleDbDataAdapter(commObj)
       myAdapter.Fill(myDataSet1, "cDetails")
       connObj.Close()

So now this is the Query stored in var=query(1).

Here, what we are doing is that first we'll search the terms individually in those columns. Like Steering in Steering, Brakes in Brakes, etc..

We'll then UNION all the results. UNION combines all the results in to single result set. But UNION does not add repeated results, and we need repeated results because the one that occurs most is the best match. Hence we are using UNION ALL.

So after the UNION, we now have the combined result. So now, we'll group all the results as per the number of occurrences. So we have a Super Query (and all the UNIONs as the sub query), that use COUNT(*) and group them by GROUP BY clause.

But again, this will sort the Result as the least matched to best matched. Hence we'll give COUNT(*) as Occurence and then sort the Occurence in Descending order using the ORDER BY clause.

So here, after implementing this query, I have returned the Product ID (Prod ID unique for all rows) and its occurrence. This is just a snippet, not a usable project. So you need to make changes as per your code.

I hope this post was helpful. :)

Points of Interest

This is my first ever article on CodeProject. I had been searching this so curiously, but couldn't find any relevant article or code for this. So if there are any errors, doubts, corrections, then please let me know. Excuse any mistake because I am not a professional. I am a student.