Indexing data returned by a query

Indexing the results of a query is similar to indexing physical files located on your website, with the added step that you must write a query that retrieves the data you want to search. The following are the steps to perform a Verity search on record sets returned from a query:

Create a collection.
Write a query that retrieves the data you want to search, and generate a record set.
Index the record set using the cfindex tag.
The cfindex tag indexes the record set as if it were a collection of documents in a folder within your website.
Search the collection.
The information returned from the collection includes the database key and other selected columns. You can then use the information as-is, or use the key value to retrieve the entire row from the database table.

You should use Verity to search databases in the following cases:

You want to perform full-text search on database data. You can search Verity collections that contain textual data much more efficiently with a Verity search than using SQL to search database tables.
You want to give your users access to data without interacting directly with the data source itself.
You want to improve the speed of queries.
You want users to be able to execute queries, but not update database tables.

Unlike indexing documents stored on your web server, indexing information contained in a database requires an additional step--you must first write a query (using the cfquery, cfldap, or cfpop tag) that retrieves the data you want to let your users search. You then pass the information retrieved by the query to a cfindex tag, which indexes the data.

When indexing data with the cfindex tag, you must specify which column of the query represents the filename, which column represents the document title, and which column (or columns) represents the document's body (the information that you want to make searchable).

When indexing a recordset retrieved from a database, the cfindex tag uses the following attributes that correspond to the data source:

Attribute	Description
key	Primary key column of the data source table.
title	Specifies a query column name.
body	Columns that you want to search for the index.
type	If set to `custom`, this attribute specifies the columns that you want to index. If set to `file` or `path`, this is a column that contains either a directory path and filename, or a directory path that contains the documents to be indexed.

Using the cfindex tag to index tabular data is similar to indexing documents, with the exception that you refer to column names from the generated record set in the body attribute. In the following example, the type attribute is set to custom, specifying that the cfindex tag index the contents of the record set columns Emp_ID, FirstName, LastName, and Salary, which are identified using the body attribute. The Emp_ID column is listed as the key attribute, making it the primary key for the record set.

To index a ColdFusion query:

Create a Verity collection for the data that you want to index.
The following example assumes that you have a Verity collection named CodeColl. You can use the ColdFusion MX Administrator to create the collection, or you can create the collection programmatically by using the cfcollection tag. For more information, see Creating a collection with the ColdFusion MX Administrator or Creating a collection with the cfcollection tag.

Create a ColdFusion page with the following content:

<html>
<head>
	<title>Adding Query Data to an Index</title>
</head>
<body>

<!--- Retrieve data from the table. --->
<cfquery name="getEmps" datasource="cfdocexamples">
	SELECT * FROM EMPLOYEE
</cfquery>

<!--- Update the collection with the above query results. --->
<cfindex 
	query="getEmps"
	collection="CodeColl"
	action="Update"
	type="Custom"
	key="Emp_ID"
	title="Emp_ID"
	body="Emp_ID,FirstName,LastName,Salary">

<h2>Indexing Complete</h2>

<!--- Output the record set. --->
<p>Your collection now includes the following items:</p>
<cfoutput query="getEmps">
	<p>#Emp_ID# #FirstName# #LastName# #Salary#</p>
</cfoutput>
</body>
</html>

Save the file as collection_db_index.cfm in the myapps directory under the web root directory.
Open the file in the web browser to index the collection.
The resulting record set appears.

To search and display the query results:

Create a ColdFusion page with the following content:

<html>
<head>
	<title>Searching a collection</title>
</head>
<body>

<h2>Searching a collection</h2>

<form method="post" action="collection_db_results.cfm">
	<p>Collection name: <input type="text" name="collname" size="30" 
maxLength="30"></p>

	<p>Enter search term(s) in the box below. You can use AND, OR, NOT, 
	and parentheses. Surround an exact phrase with quotation marks.</p>
	<p><input type="text" name="criteria" size="50" maxLength="50">
	</p>
	<p><input type="submit" value="Search"></p>
</form>

</body>
</html>

Save the file as collection_db_search_form.cfm in the myapps directory under the web_root.
This file is similar to collection_search_form.cfm, except the form uses collection_db_results.cfm, which you create in the next step, as its action page.

Create another ColdFusion page with the following content:

<html>
<head>
<title>Search Results</title>
</head>

<body>

<cfsearch 
   collection="#Form.collname#"
   name="getEmps"
   criteria="#Form.Criteria#"
	maxrows = "100">

<!--- Output the record set. --->
<cfoutput>
Your search returned #getEmps.RecordCount# file(s).
</cfoutput>

<cfoutput query="getEmps">
	<p><table>
	<tr><td>Title: </td><td>#Title#</td></tr>
	<tr><td>Score: </td><td>#Score#</td></tr>
	<tr><td>Key: </td><td>#Key#</td></tr>
	<tr><td>Summary: </td><td>#Summary#</td></tr>
	<tr><td>Custom 1:</td><td>#Custom1#</td></tr>
	<tr><td>Column list: </td><td>#ColumnList#</td></tr>
</table></p>

</cfoutput>
</body>
</html>

Save the file as collection_db_results.cfm in the myapps directory under the web_root.
View collection_db_search_form.cfm in the web browser and enter the name of the collection and search terms.

View comments in LiveDocs