同じテーブルに何度もアクセスする必要がある場合、データが既にメモリ(レコードセット)に格納されているので、アクセス時間が大幅に短縮されます。クエリオブクエリは、5,000〜50,000 行のテーブルに最適であり、 ColdFusion ホストコンピューターのメモリによってのみ制限されます。
タグまたは関数でレコードセットを作成したら、従属クエリを実行してそのレコードセットからデータを取得できます。レコードセットからデータを取得するクエリを、クエリオブクエリと呼びます。クエリオブクエリを使用する状況としては、最初のクエリでテーブル全体をメモリに読み込んだ後に、そのテーブルデータ(レコードセット)に対してソートクエリやフィルタクエリを実行する場合などがあります。つまり、レコードセットをデータベーステーブルと見なしてクエリを実行します。
レコードセットは cfquery タグを使用しなくても生成できるので、クエリオブクエリはメモリ内クエリとも呼ばれます。
クエリオブクエリの利点
クエリオブクエリには、次のような多くの利点があります。
-
-
複数の異なるデータソースから得られた結果に対して結合や UNION 操作を実行できます。例えば、複数の異なるデータベースから得られたクエリ結果に対して UNION 操作を実行して、メーリングリストの重複データを取り除くことができます。
-
キャッシュされたクエリ結果は、様々な方法で効率的に操作できます。データベースを一度クエリすると、その結果を使用して複数の異なる集計テーブルを生成できます。例えば、合計給与を 部門別、 技能別および 職種別に集計するには、 データベースに対して 1 回クエリを実行し、その結果を 3 つの別個のクエリで使用して集計を生成できます。
-
詳細を得るためにデータベースにアクセスしなくても、ドリルダウンのプライマリ/詳細情報を取得できます。例えば、クエリで部門と従業員に関する情報を選択し、その結果をキャッシュできます。画面上に従業員名を表示します。ユーザーがアプリケーションで従業員を選択したら、データベースにアクセスせずに、キャッシュに格納されているクエリ結果からその従業員の詳細データを表示できます。
-
レポート定義でクエリオブクエリを使用して、サブレポートデータを生成できます。詳しくは、 サブレポートの使用 の節を一般的なレポート作成作業と方法の記事で参照してください。
クエリオブクエリの実行
クエリオブクエリを実行するには、次の手順に従います。
-
プライマリクエリを使用してレコードセットを生成します。プライマリクエリは、レコードセットを作成するタグまたは関数を使用して記述できます。詳しくは、レコードセットについてのレコードセットの作成を参照してください。
-
詳細クエリ、つまり dbtype="query" を指定した cfquery タグを記述します。
-
詳細クエリの中に、関連するレコードを取得する SQL ステートメントを記述します。SQL コードのテーブル名には、1 つまたは複数の既存クエリの名前を指定します。なお、 datasource 属性は指定しないでください。
-
データベースの内容が頻繁に変更されない場合は、プライマリクエリの cachedwithin 属性を使用して、ページリクエスト間でクエリ結果をキャッシュします。このようにすると、最初のページリクエストでデータベースにアクセスされた後は、指定の時間が経過するまでデータベースへのクエリは行われません。cachedwithin 属性値を(日数、時間、分、秒の形式で)指定するには、CreateTimeSpan 関数を使用します。
詳細クエリによって、新しいクエリ結果セットが生成されます。この結果セットは、詳細クエリの name 属性の値で識別されます。次の例は、プライマリクエリと、そのプライマリクエリから情報を抽出する 1 つの詳細クエリを使用する方法を示しています。
クエリでのクエリ結果の使用
-
次の内容の ColdFusion ページを作成します。
<h1>Employee List</h1><!--- LastNameSearch(通常はインタラクティブに生成されます) ---><cfset LastNameSearch="Doe"><! --- プライマリクエリ ---><cfquery datasource="cfdocexamples" name="master"cachedwithin=#CreateTimeSpan(0,1,0,0)#>SELECT * from Employee</cfquery><! --- 詳細クエリ(dbtype=query、データソースなし) ---><cfquery dbtype="query" name="detail">SELECT Emp_ID, FirstName, LastNameFROM masterWHERE LastName=<cfqueryparam value="#LastNameSearch#"cfsqltype="cf_sql_char" maxLength="20"></cfquery><! --- 詳細クエリ結果を出力 ---><p>Output using a query of query:</p><cfoutput query=detail>#Emp_ID#: #FirstName# #LastName#<br></cfoutput><p>Columns in the master query:</p><cfoutput>#master.columnlist#<br></cfoutput><p>Columns in the detail query:</p><cfoutput>#detail.columnlist#<br></cfoutput><h1>Employee List</h1> <!--- LastNameSearch(通常はインタラクティブに生成されます) ---> <cfset LastNameSearch="Doe"> <! --- プライマリクエリ ---> <cfquery datasource="cfdocexamples" name="master" cachedwithin=#CreateTimeSpan(0,1,0,0)#> SELECT * from Employee </cfquery> <! --- 詳細クエリ(dbtype=query、データソースなし) ---> <cfquery dbtype="query" name="detail"> SELECT Emp_ID, FirstName, LastName FROM master WHERE LastName=<cfqueryparam value="#LastNameSearch#" cfsqltype="cf_sql_char" maxLength="20"></cfquery> <! --- 詳細クエリ結果を出力 ---> <p>Output using a query of query:</p> <cfoutput query=detail> #Emp_ID#: #FirstName# #LastName#<br> </cfoutput> <p>Columns in the master query:</p> <cfoutput> #master.columnlist#<br> </cfoutput> <p>Columns in the detail query:</p> <cfoutput> #detail.columnlist#<br> </cfoutput><h1>Employee List</h1> <!--- LastNameSearch(通常はインタラクティブに生成されます) ---> <cfset LastNameSearch="Doe"> <! --- プライマリクエリ ---> <cfquery datasource="cfdocexamples" name="master" cachedwithin=#CreateTimeSpan(0,1,0,0)#> SELECT * from Employee </cfquery> <! --- 詳細クエリ(dbtype=query、データソースなし) ---> <cfquery dbtype="query" name="detail"> SELECT Emp_ID, FirstName, LastName FROM master WHERE LastName=<cfqueryparam value="#LastNameSearch#" cfsqltype="cf_sql_char" maxLength="20"></cfquery> <! --- 詳細クエリ結果を出力 ---> <p>Output using a query of query:</p> <cfoutput query=detail> #Emp_ID#: #FirstName# #LastName#<br> </cfoutput> <p>Columns in the master query:</p> <cfoutput> #master.columnlist#<br> </cfoutput> <p>Columns in the detail query:</p> <cfoutput> #detail.columnlist#<br> </cfoutput>
-
このページを query_of_query.cfm として保存します。 保存場所は web_root 下の myapps ディレクトリです。
-
ブラウザーで query_of_query.cfm を表示します。
クエリを作成し、例えば名前などの列を順序付きで選択すると、出力で名前の順序が正しく表示されない場合があります。
これを解決するには、jvm.args に -Dcoldfusion.application.orderby.caseinsensitive=true を追加します。
コードの説明
プライマリクエリで、cfdocexamples データソースの Employee テーブル全体が取得されます。詳細クエリで、特定の姓を持つ従業員の表示対象となる 3 つの列のみが選択されます。このコードおよびその機能について、次の表で説明します。
コード |
説明 |
---|---|
cfset LastNameSearch="Doe" |
詳細クエリで選択する姓を設定します。実際のアプリケーションでは、この情報はユーザーの入力から取得します。 |
|
cfdocexamples データソースに対してクエリを実行し、Employees テーブルのすべてのデータを選択します。クエリデータは、このページに対するリクエスト間でキャッシュされます。キャッシュされてから 1 時間経過するまで、データベースへのクエリは行われません。 |
<cfquery dbtype="query" name="detail"> |
detail という名前の新しいクエリで、プライマリクエリをデータソースとして使用します。この新しいクエリでは、LastNameSearch 変数の値と同じ姓を持つエントリのみを選択しています。また、従業員 ID、名および姓の 3 つの列のデータのみを選択しています。このクエリでは、誤りのあるコードや危険なコードが渡されないように、cfqueryparam タグを使用しています。 |
<cfoutput query=detail> |
詳細クエリを使用して、従業員 ID、名および姓のリストを表示します。 |
<cfoutput> |
プライマリクエリで返されたすべての列のリストを表示します。 |
<cfoutput> |
詳細クエリで返されたすべての列のリストを表示します。 |
レコードセットのデータを分割して表示する方法
データベースが大きい場合は、一度に表示する行数を制限できます。これをクエリオブクエリの currentRow クエリ変数を使用して行う方法を次の例で示します。クエリ変数について詳しくは、Getting information about query results を参照してください。
-
次の内容の ColdFusion ページを作成します。
<html><head><title>QoQ with incremental row return</title></head><body><h3>QoQ with incremental row return</h3><!--- startrow と maxrows を定義して、「次の N 件」スタイルのブラウジングを容易にする ---><cfparam name = "MaxRows" default = "5"><cfparam name = "StartRow" default = "1"><! --- プライマリクエリ:Employee テーブルからすべての情報を取得 ---><cfquery name = "GetSals" datasource = "cfdocexamples">SELECT * FROM EmployeeORDER BY LastName</cfquery><! --- 詳細クエリ:プライマリクエリから 3 つのフィールドを選択 ---><cfquery name = "GetSals2" dbtype = "query">SELECT FirstName, LastName, SalaryFROM GetSalsORDER BY LastName</cfquery><! --- 出力を表示するためのテーブルを作成 ---><table cellpadding = 1 cellspacing = 1><tr><td bgcolor = f0f0f0><b><i> </i></b></td><td bgcolor = f0f0f0><b><i>FirstName</i></b></td><td bgcolor = f0f0f0><b><i>LastName</i></b></td><td bgcolor = f0f0f0><b><i>Salary</i></b></td></tr><! --- クエリを出力し、startrow および maxrowsパラメーターを定義。クエリ変数 currentRow を使用して、表示中の行を追跡 ---><cfoutput query = "GetSals2" startrow = "#StartRow#" maxrows = "#MaxRows#"><tr><td valign = top bgcolor = ffffed><b>#GetSals2.currentRow#</b></td><td valign = top><font size = "-1">#FirstName#</font></td><td valign = top><font size = "-1">#LastName#</font></td><td valign = top><font size = "-1">#LSCurrencyFormat(Salary)#</font></td></tr></cfoutput><!--- レコードの総数が行の総数以下の場合は、StartRow 値を MaxRows(この例では 5)だけインクリメントしたうえで同じページへのリンクを提供 ---><tr><td colspan = 4><cfif (startrow + maxrows) lte getsals2.recordcount><a href="qoq_next_row.cfm?startrow=<cfoutput>#Evaluate(StartRow +MaxRows)#</cfoutput>">See next <cfoutput>#MaxRows#</cfoutput>rows</a></cfif></td></tr></table></body></html><html> <head> <title>QoQ with incremental row return</title> </head> <body> <h3>QoQ with incremental row return</h3> <!--- startrow と maxrows を定義して、「次の N 件」スタイルのブラウジングを容易にする ---> <cfparam name = "MaxRows" default = "5"> <cfparam name = "StartRow" default = "1"> <! --- プライマリクエリ:Employee テーブルからすべての情報を取得 ---> <cfquery name = "GetSals" datasource = "cfdocexamples"> SELECT * FROM Employee ORDER BY LastName </cfquery> <! --- 詳細クエリ:プライマリクエリから 3 つのフィールドを選択 ---> <cfquery name = "GetSals2" dbtype = "query"> SELECT FirstName, LastName, Salary FROM GetSals ORDER BY LastName </cfquery> <! --- 出力を表示するためのテーブルを作成 ---> <table cellpadding = 1 cellspacing = 1> <tr> <td bgcolor = f0f0f0> <b><i> </i></b> </td> <td bgcolor = f0f0f0> <b><i>FirstName</i></b> </td> <td bgcolor = f0f0f0> <b><i>LastName</i></b> </td> <td bgcolor = f0f0f0> <b><i>Salary</i></b> </td> </tr> <! --- クエリを出力し、startrow および maxrows パラメーターを定義。クエリ変数 currentRow を使用して、 表示中の行を追跡 ---> <cfoutput query = "GetSals2" startrow = "#StartRow#" maxrows = "#MaxRows#"> <tr> <td valign = top bgcolor = ffffed> <b>#GetSals2.currentRow#</b> </td> <td valign = top> <font size = "-1">#FirstName#</font> </td> <td valign = top> <font size = "-1">#LastName#</font> </td> <td valign = top> <font size = "-1">#LSCurrencyFormat(Salary)#</font> </td> </tr> </cfoutput> <!--- レコードの総数が行の総数以下の場合は、 StartRow 値を MaxRows(この例では 5)だけインクリメントしたうえで 同じページへのリンクを提供 ---> <tr> <td colspan = 4> <cfif (startrow + maxrows) lte getsals2.recordcount> <a href="qoq_next_row.cfm?startrow=<cfoutput>#Evaluate(StartRow + MaxRows)#</cfoutput>">See next <cfoutput>#MaxRows#</cfoutput> rows</a> </cfif> </td> </tr> </table> </body> </html><html> <head> <title>QoQ with incremental row return</title> </head> <body> <h3>QoQ with incremental row return</h3> <!--- startrow と maxrows を定義して、「次の N 件」スタイルのブラウジングを容易にする ---> <cfparam name = "MaxRows" default = "5"> <cfparam name = "StartRow" default = "1"> <! --- プライマリクエリ:Employee テーブルからすべての情報を取得 ---> <cfquery name = "GetSals" datasource = "cfdocexamples"> SELECT * FROM Employee ORDER BY LastName </cfquery> <! --- 詳細クエリ:プライマリクエリから 3 つのフィールドを選択 ---> <cfquery name = "GetSals2" dbtype = "query"> SELECT FirstName, LastName, Salary FROM GetSals ORDER BY LastName </cfquery> <! --- 出力を表示するためのテーブルを作成 ---> <table cellpadding = 1 cellspacing = 1> <tr> <td bgcolor = f0f0f0> <b><i> </i></b> </td> <td bgcolor = f0f0f0> <b><i>FirstName</i></b> </td> <td bgcolor = f0f0f0> <b><i>LastName</i></b> </td> <td bgcolor = f0f0f0> <b><i>Salary</i></b> </td> </tr> <! --- クエリを出力し、startrow および maxrows パラメーターを定義。クエリ変数 currentRow を使用して、 表示中の行を追跡 ---> <cfoutput query = "GetSals2" startrow = "#StartRow#" maxrows = "#MaxRows#"> <tr> <td valign = top bgcolor = ffffed> <b>#GetSals2.currentRow#</b> </td> <td valign = top> <font size = "-1">#FirstName#</font> </td> <td valign = top> <font size = "-1">#LastName#</font> </td> <td valign = top> <font size = "-1">#LSCurrencyFormat(Salary)#</font> </td> </tr> </cfoutput> <!--- レコードの総数が行の総数以下の場合は、 StartRow 値を MaxRows(この例では 5)だけインクリメントしたうえで 同じページへのリンクを提供 ---> <tr> <td colspan = 4> <cfif (startrow + maxrows) lte getsals2.recordcount> <a href="qoq_next_row.cfm?startrow=<cfoutput>#Evaluate(StartRow + MaxRows)#</cfoutput>">See next <cfoutput>#MaxRows#</cfoutput> rows</a> </cfif> </td> </tr> </table> </body> </html>
-
このページを qoq_next_row.cfm として保存します。 保存場所は web_root 下の myapps ディレクトリです。
-
ブラウザーで qoq_next_row.cfm を表示します。
cfdump タグによるクエリ結果の表示
CFML コードをデバッグする際は、cfdump タグを使用すると、クエリの内容をすぐに表示できます。このタグの形式は次のとおりです。
<cfdump var="#query_name#">
cfdump タグについて詳しくは、CFML リファレンスを参照してください。
SQL 以外のレコードセットでのクエリオブクエリの使用
クエリオブクエリは、レコードセットを返す任意の CFML タグまたは関数に対して使用できます。対象は cfquery の結果に限りません。SQL 以外のレコードセットに対してクエリを実行できます。例えば、 cfdirectory タグ、 cfsearch タグ、 cfldap タグなどです。