Programming ColdFusion

Drill-Down Queries

A drill-down query is one that starts by retrieving and displaying a relatively broad or general result set. Then, hyperlinks from one or more columns in the result set are used to call another template that performs a query based on URL parameters passed by the hyperlinks. This process is designed to narrow the number of records returned until a desired level of granularity is achieved, hence the name drill-down query.

For drill-down queries, you usually need two templates, but it is possible to use as many as you want to achieve the level of granularity you need. In a two-template drill-down application, the first template queries a data source and displays a summary (usually just a few fields) of every record in the data source meeting the user’s criteria. Hyperlinks from some of the fields in these results pass the primary key values of records to the second template. The second template then performs a query using the primary key value passed in via URL in the WHERE clause of the SELECT statement. The results (usually the full record) are then output to the browser. Example 11-6 demonstrates how a two-template drill-down query works by querying the EmployeeDirectory table and generating an HTML table containing the Name, Title, and Department of each employee.

Example 11-6. Initial Screen Listing Partial Information About Each Record

<!--- retrieve a list of all employees in the EmployeeDirectory table ---> <CFQUERY NAME="GetEmployeeList" DATASOURCE="ProgrammingCF"> ...