Drilldown Queries
A drilldown query (sometimes referred to as master-detail) 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 drilldown query.
For drilldown 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 drilldown 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 drilldown
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" ...
Get Programming ColdFusion MX, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
