Database Search
Database Search is one of the faceted search interfaces that is available for database collection which supports databases like MySQL, SQL Server, Oracle as well as for CSV and MongoDB collections. Using this you can build your database search and display the same in a few minutes.
In SearchBlox v10, database plugin is available by default to make customized searches to Database, CSV and MongoDB collections.
To access database plugin please use the following link in your build
https://localhost:8443/database/index.html
Prerequisite
Although the database plugin would work across all collections, it would be best for database, CSV and MongoDB collections. Therefore please make sure to create either of these collections to check out this plugin.
Steps to use Database Plugin for Database and CSV Collection
- Once the collections are created and indexed you can directly access the plugin using the link
https://localhost:8443/database/index.html
and make some searches to view the results as shown here:
Facet Filters
The default facets for filters such as file type, keyword and modified date can be found in the left-hand menu. After selecting each filter, the facets will appear selected with x mark to deselect the same. By clicking the x next to the facet, it can be removed from the search results as shown in the following image.
- File Type lists the file types available in the search results for filtering.
- Keywords lists the words that are the keywords of the search, other than the search term results.
- Modified Date has five options ranging from week to year, along with customized date range.
You can also add collection as a facet by making changes in ../database/facet.js - Collection lists the collections that have the search results for the given search term.
Similarly, you can also add other fields from Database or CSV or MongoDB as facets.
Adding a Facet in Database search
- SearchBlox also supports fields like Collection, Title, Description, Language, Author and Subject as facets.
- facet.js is the configuration file that provides options that can be configured to provide search results as required. The path for the facet.js file, after placing the plugin folder in SearchBlox, is ../webapps/searchblox/database/facet.js.
- To add a new facet filter, define the field name, display name and the size of the filter values to be shown in facet.js. Facet filters can be string, number or date types. Except for the predefined facets such as keywords, title, description, size or date, if you need to add more custom facets, you can add them in facet.js (example: facet field: "group").
- To add or edit facets and filters, please make the changes to ../searchblox/database/facet.js file. This file would have the search-related configuration information which can be customized by the user.
- If you need to a custom field as facet, then the field has to be mapped in jdbc.json for database collection, csv.json for CSV collection, mongodb.json for MongoDB and mapping.json for file and HTTP collection.
- If you need to add a facet field for type number or date, the field needs to be updated in csv.json, jdbc.json, mongodb.json, amazonS3.json and mapping.json here: ../webapps/searchblox/WEB-INF/ for CSV, database, Mongodb, AWS collections and other collections respectively.
ref: Custom Fields in Search
Fields in facets.js
The content of facet.js are as follows:
window.facets = {
"facets": [{
"field": "colname",
"display": "Collection Name",
"size": "10"
},
{
"field": "contenttype",
"display": "File Type",
"size": "10"
},
{
"field": "keywords",
"display": "keywords",
"size": "10"
},
{
"field": "lastmodified",
"display": "Last Modified",
"dateRange": [{
"name": "Last 24 hours",
"calendar": "days",
"value": "1"
},
{
"name": "Past Week",
"calendar": "days",
"value": "7"
},
{
"name": "Past Month",
"calendar": "months",
"value": "1"
},
{
"name": "Past Year",
"calendar": "years",
"value": "1"
}
]
}
],
"collection": [],
"sortBtns": [{
"field": "lastmodified",
"display": "Sort by date"
},
{
"field": "relevance",
"display": "Sort by relevance"
}
],
"facetFiltersOrder": [
"colname","keywords"
],
"facetFiltersType": "AND",
"sortDir": "desc",
"matchAny": "off",
"pageSize": "10",
"showAutoSuggest": "true",
"defaultCname": "",
"adsDisplay": "true",
"featuredResultsCount": "3",
"predictSearch": "false",
"predictResultSize": "3",
"customDateDisplayText":"Custom",
"customDateEnable":"true",
"relatedQuery": false,
"relatedQueryFields": {
"apikey": "",
"field": "content",
"operator": "and",
"limit": "5",
"terms": "10",
"type": "phrase",
"col": ""
},
"dataToBeDisplayed": {
"1": {
"title": "Title",
"description": "Description"
},
"other": {
"description": "Description"
},
"displayAll": true
},
"tune": {
"enable": "true",
"tune.0": "5",
"tune.1": "3",
"tune.2": "2",
"tune.3": "5",
"tune.4": "180",
"tune.5": "10"
},
"gridColumns": [
{
"name": "Id",
"field": "@no"
},
{
"name": "Title",
"field": "title"
},
{
"name": "Description",
"field": "description"
},
{
"name": "URL",
"field": "url"
},
{
"name": "Last modified",
"field": "lastmodified"
}
],
"defaultType": "",
"pluginDomain": ""
};
The description of the fields in the preceding js file is provided in the following table:
| Field | Description |
|---|---|
| facets | The facet filters have to be added in this section. The default facet filters available in the plugin are collection name (colname), content type (contenttype), Keywords(keywords) and last modified date(lastmodified) "field" represents the actual field name, provided in preceding brackets "display" represents the display name in the plugin "size" represents the number of values displayed in the filter for user selection "daterange" represents the range for the date filter |
| collection | Using this field you can specify/limit the collections to be used for this plugin. You can specifiy one or more collections in this field. The collection numbers must be specified as digits, if more than one collection they have to be separated by comma. |
| SortBtns | Sort By field has to be provided here. |
| facetFiltersOrder | Order of facet filters to be displayed in plugin has to be specified here |
| sortDir | The direction of sort whether ascending or descending has to be specified here |
| pageSize | Using this parameter the number of results per page can be controlled in faceted search |
| showAutoSuggest | Autosuggest can be enabled or disabled using this parameter |
| defaultCname | This is to specify default collection in which the search has to be made in the beginning |
| adsDisplay | Featured results can be enabled or disabled in search results using this |
| featuredResultsCount | The number of featured results to be displayed in the results can be specified here |
| predictSearch | Predicted results can be enabled using this field |
| predictResultSize | Parameters for predicted search can be specified here |
| customDateDisplayText | This is for custom date label change in facet filters |
| customDateEnable | This is to enable or disable custom date |
| relatedQuery | Enable or disable related query by giving true or false value for this field. By default it would be true |
| relatedQueryFields | If you want related queries in search results then specify the API key within the field. Also you can limit it based on collection by giving specific column number. "apikey": "BFC82BE93A351C018CB27C32E1138047", "field": "content", "operator": "and", "limit": "5", "terms": "10", "type": "phrase", "col": "" |
| dataToBeDisplayed | This is specific for collections like csv and database where results have to appear in tabular format. The fields can be specified here. If "displayAll" is true within this field all the data would be fetched from the results |
| tune | Tune parameter for relevancy of search results. This can be enabled by giving "enable": "true", within this field |
| gridcolumns | This is to specify the fields to be displayed in result grid. More details regarding the same is presented in the next section |
| defaultType | if AND is specified then boolean operator AND would be used in searching multiple terms. If none is specified it would be OR search |
| pluginDomain | The domain of SearchBlox where the plugin has to fetch results |
Configuring Result Grid for Database Plugin
This result grid can be configured for Database, CSV and MongoDB collections. For Database collections alone there would be a source attribute however we will not be using the same in our plugin. The fields can be directly used from Database collection similar to CSV and MongoDB collections.
Sample database results in JSON:
[
{
"@no": "1",
"@id": "1",
"score": "100",
"raw_score": "10.0",
"college": "VIT",
"col": "1",
"no": "1",
"keywords": " VIT 1 1 Amit Science",
"indexdate": "01 Aug 2019 12:15:59 UTC",
"description": [],
"language": "en",
"source": {
"college": "VIT",
"no": "1",
"name": "Amit",
"dept": "Science"
},
"dept": "Science",
"title": "Amit",
"sb_boost": "1.0",
"url": "db?url=",
"contenttype": "db",
"uid": "c4ca4238a0b923820dcc509a6f75849b",
"colname": "database",
"old_uid": "1",
"lastmodified": "01 Aug 2019 12:15:59 UTC",
"alpha": [],
"name": "Amit",
"es_metadata_id": "1"
},
{
"@no": "2",
"@id": "3",
"score": "100",
"raw_score": "10.0",
"college": "MIT",
"col": "1",
"no": "3",
"keywords": " MIT 3 3 Camila Biology",
"indexdate": "01 Aug 2019 12:15:59 UTC",
"description": [],
"language": "en",
"source": {
"college": "MIT",
"no": "3",
"name": "Camila",
"dept": "Biology"
},
"dept": "Biology",
"title": "Camila",
"sb_boost": "1.0",
"url": "db?url=",
"contenttype": "db",
"uid": "eccbc87e4b5ce2fe28308fd9f2a7baf3",
"colname": "database",
"old_uid": "3",
"lastmodified": "01 Aug 2019 12:15:59 UTC",
"alpha": [],
"name": "Camila",
"es_metadata_id": "3"
}]
Changes in facet.json grid columns based on the preceding sample data:
"gridColumns": [
{
"name": "No",
"field": "no"
},
{
"name": "Title",
"field": "title"
},
{
"name": "Department",
"field": "dept"
},
{
"name": "College",
"field": "college"
},
{
"name": "Name",
"field": "name"
},
{
"name": "Last modified",
"field": "lastmodified"
}
]
}
Configuring the Data to be Displayed on Clicking Result
By default database plugin displays all fields. By default, displayAll is true which fetches all fields in the individual result display. This can be customized base on collection by specifying the collection number in the JSON code within the dataToBeDisplayed field. In the following code we have specified the field for collection no 1 and only the description field for other collections. Also, displayAll is disabled so only the three fields will appear in individual result display.
"dataToBeDisplayed": {
"1": {
"title": "Title",
"dept": "Department",
"college": "College"
},
"other": {
"description": "Description"
},
"displayAll": false
},
The result for the preceding settings would be as shown:
Updated over 2 years ago