SQL Query Format for Database Collection
SQL Queries in SearchBlox have a few basic rules:
- SQL Query must be given as a single line.
- Only one query can be given per collection.
- It is mandatory to map a unique field to uid field in the query.
- Applications supported by database collections (such as Salesforce or Gmail) should have the query words such as "SELECT", "FROM", etc., in capital letters.
- Fields can be mapped to the SearchBlox fields. The following lists the user-defined fields within SearchBlox to which field values can be mapped.
| Field | Description |
|---|---|
| uid | A primary or unique field from a table has to be mapped to this field. This is a mandatory field. Make sure to map a unique field to this field |
| title | If any field has to appear as a title in search results, it should be mapped as the title. |
| description | If any field has to appear in the description then it should be mapped to the description field. |
| keywords | If any field has to appear in the keywords section it has to be mapped as keywords. |
| content | If you want any field to be updated in the content section you can map the same in the query. |
Sample Database
The sample data provided in the following table is used for the sample queries on this page.
| no | fname | lname | address | dept | dept id | university |
|---|---|---|---|---|---|---|
| 1 | Smith | Jones | 1, Georgetown | Biotech | D05 | VCU |
| 2 | James | Brown | 5, Nearville | Physics | D01 | VCU |
| 3 | Will | Smith | 3, Napatown | Bioinfo | D04 | VCU |
Sample SQL Query
Important Information
- If you need to map a field to the uid field in SearchBlox, always make sure that it is unique.
- Note that the SQL query must be given as a single line.
- Do not enter any new line characters in between, as this could cause problems with indexing.
- The following query is the most simple query that can be given in Database Collection. It is required to map a unique field to uid field.
select *,<primary or unique field> as uid from <tablename>
select *,no as uid from sample
- Please map one of the fields to the title to get proper results in faceted or regular search
- In this query all fields have been fetched and fields have been mapped to uid and title.
select *,<primary or unique field> as uid,<title field> as title from table
select *,no as uid, fname as title from sample
- You can limit the number of fields fetched in a query
- In these query fields have been mapped to uid, title, and content. Also, only three fields have been fetched for indexing.
select <index field> as uid , <column name2> as title, <column name3> as content from table
select no as uid, fname as title,address as content from sample
- In this query all user-defined fields in SearchBlox have been mapped.
select <index field> as uid,<column2> as title,<column3> as description,<column4> as keywords, <column5> as content,<column 6> from table
select no as uid, fname as title,lname as description, dept as keywords, address as content from sample
- In this query few fields are not mapped but indexed as it is.
select <index field> as uid,<column2>, <column3>, <column4>, <column5> from table
select no as uid, fname,lname, dept, address from sample
- Please note that the field mapped as uid will be indexed as uid, field indexed as title will be indexed as title and will appear in the title in the results. Similarly, fields mapped as description, keywords, and content fields will appear as the respective fields in search results.
- You can also provide queries for views in SearchBlox. The data from the view will be indexed in the collection based on the select query provided for the view.
To learn about the SQL syntax for Database Collection read: SQL Query for Database Collection
Comments
0 comments
Please sign in to leave a comment.