クエリオブクエリについて

タグまたは関数でレコードセットを作成したら、従属クエリを実行してそのレコードセットからデータを取得できます。レコードセットからデータを取得するクエリを、クエリオブクエリと呼びます。クエリオブクエリを使用する状況としては、最初のクエリでテーブル全体をメモリに読み込んだ後に、そのテーブルデータ(レコードセット)に対してソートクエリやフィルタクエリを実行する場合などがあります。つまり、レコードセットをデータベーステーブルと見なしてクエリを実行します。

注意:

レコードセットは cfquery タグを使用しなくても生成できるので、クエリオブクエリはメモリ内クエリとも呼ばれます。

クエリオブクエリの利点

クエリオブクエリには、次のような多くの利点があります。

  1. 同じテーブルに何度もアクセスする必要がある場合、データが既にメモリ(レコードセット)に格納されているので、アクセス時間が大幅に短縮されます。クエリオブクエリは、5,000〜50,000 行のテーブルに最適であり、 ColdFusion ホストコンピューターのメモリによってのみ制限されます。

  2. 複数の異なるデータソースから得られた結果に対して結合や UNION 操作を実行できます。例えば、複数の異なるデータベースから得られたクエリ結果に対して UNION 操作を実行して、メーリングリストの重複データを取り除くことができます。

  3. キャッシュされたクエリ結果は、様々な方法で効率的に操作できます。データベースを一度クエリすると、その結果を使用して複数の異なる集計テーブルを生成できます。例えば、合計給与を 部門別、 技能別および 職種別に集計するには、 データベースに対して 1 回クエリを実行し、その結果を 3 つの別個のクエリで使用して集計を生成できます。

  4. 詳細を得るためにデータベースにアクセスしなくても、ドリルダウンのプライマリ/詳細情報を取得できます。例えば、クエリで部門と従業員に関する情報を選択し、その結果をキャッシュできます。画面上に従業員名を表示します。ユーザーがアプリケーションで従業員を選択したら、データベースにアクセスせずに、キャッシュに格納されているクエリ結果からその従業員の詳細データを表示できます。

  5. レポート定義でクエリオブクエリを使用して、サブレポートデータを生成できます。詳しくは、 サブレポートの使用 の節を一般的なレポート作成作業と方法の記事で参照してください。

クエリオブクエリの実行

クエリオブクエリを実行するには、次の手順に従います。

  1. プライマリクエリを使用してレコードセットを生成します。プライマリクエリは、レコードセットを作成するタグまたは関数を使用して記述できます。詳しくは、レコードセットについてレコードセットの作成を参照してください。

  2. 詳細クエリ、つまり dbtype="query" を指定した cfquery タグを記述します。

  3. 詳細クエリの中に、関連するレコードを取得する SQL ステートメントを記述します。SQL コードのテーブル名には、1 つまたは複数の既存クエリの名前を指定します。なお、 datasource 属性は指定しないでください。

  4. データベースの内容が頻繁に変更されない場合は、プライマリクエリの cachedwithin 属性を使用して、ページリクエスト間でクエリ結果をキャッシュします。このようにすると、最初のページリクエストでデータベースにアクセスされた後は、指定の時間が経過するまでデータベースへのクエリは行われません。cachedwithin 属性値を(日数、時間、分、秒の形式で)指定するには、CreateTimeSpan 関数を使用します。
    詳細クエリによって、新しいクエリ結果セットが生成されます。この結果セットは、詳細クエリの name 属性の値で識別されます。次の例は、プライマリクエリと、そのプライマリクエリから情報を抽出する 1 つの詳細クエリを使用する方法を示しています。

クエリでのクエリ結果の使用

  1. 次の内容の ColdFusion ページを作成します。

    <h1>Employee List</h1>
    <!--- LastNameSearch(通常はインタラクティブに生成されます) --->
    <cfset LastNameSearch=&quot;Doe&quot;>
    <! --- プライマリクエリ --->
    <cfquery datasource=&quot;cfdocexamples&quot; name=&quot;master&quot;
    cachedwithin=#CreateTimeSpan(0,1,0,0)#>
    SELECT * from Employee
    </cfquery>
    <! --- 詳細クエリ(dbtype=query、データソースなし) --->
    <cfquery dbtype=&quot;query&quot; name=&quot;detail&quot;>
    SELECT Emp_ID, FirstName, LastName
    FROM master
    WHERE LastName=<cfqueryparam value=&quot;#LastNameSearch#&quot;
    cfsqltype=&quot;cf_sql_char&quot; maxLength=&quot;20&quot;></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=&quot;Doe&quot;> <! --- プライマリクエリ ---> <cfquery datasource=&quot;cfdocexamples&quot; name=&quot;master&quot; cachedwithin=#CreateTimeSpan(0,1,0,0)#> SELECT * from Employee </cfquery> <! --- 詳細クエリ(dbtype=query、データソースなし) ---> <cfquery dbtype=&quot;query&quot; name=&quot;detail&quot;> SELECT Emp_ID, FirstName, LastName FROM master WHERE LastName=<cfqueryparam value=&quot;#LastNameSearch#&quot; cfsqltype=&quot;cf_sql_char&quot; maxLength=&quot;20&quot;></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=&quot;Doe&quot;> 
    
    <! ---  プライマリクエリ ---> 
    <cfquery datasource=&quot;cfdocexamples&quot; name=&quot;master&quot; 
    cachedwithin=#CreateTimeSpan(0,1,0,0)#> 
    SELECT * from Employee 
    </cfquery> 
    
    <! --- 詳細クエリ(dbtype=query、データソースなし) ---> 
    <cfquery dbtype=&quot;query&quot; name=&quot;detail&quot;> 
    SELECT Emp_ID, FirstName, LastName 
    FROM master 
    WHERE LastName=<cfqueryparam value=&quot;#LastNameSearch#&quot; 
    cfsqltype=&quot;cf_sql_char&quot; maxLength=&quot;20&quot;></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>
  2. このページを query_of_query.cfm として保存します。 保存場所は   web_root 下の myapps ディレクトリです。

  3. ブラウザーで query_of_query.cfm を表示します。

注意:

クエリを作成し、例えば名前などの列を順序付きで選択すると、出力で名前の順序が正しく表示されない場合があります。

これを解決するには、jvm.args に -Dcoldfusion.application.orderby.caseinsensitive=true を追加します。

コードの説明

プライマリクエリで、cfdocexamples データソースの Employee テーブル全体が取得されます。詳細クエリで、特定の姓を持つ従業員の表示対象となる 3 つの列のみが選択されます。このコードおよびその機能について、次の表で説明します。

コード

説明

cfset LastNameSearch="Doe"

詳細クエリで選択する姓を設定します。実際のアプリケーションでは、この情報はユーザーの入力から取得します。



<cfquery datasource="cfdocexamples" name="master"
cachedwithin=#CreateTimeSpan(0,1,0,0)#>
SELECT * from Employee 
</cfquery>

cfdocexamples データソースに対してクエリを実行し、Employees テーブルのすべてのデータを選択します。クエリデータは、このページに対するリクエスト間でキャッシュされます。キャッシュされてから 1 時間経過するまで、データベースへのクエリは行われません。

<cfquery dbtype="query" name="detail"> 
SELECT Emp_ID, FirstName, LastName FROM master
WHERE LastName=<cfqueryparam value="#LastNameSearch#"
cfsqltype="cf_sql_char" maxLength="20">
</cfquery>

detail という名前の新しいクエリで、プライマリクエリをデータソースとして使用します。この新しいクエリでは、LastNameSearch 変数の値と同じ姓を持つエントリのみを選択しています。また、従業員 ID、名および姓の 3 つの列のデータのみを選択しています。このクエリでは、誤りのあるコードや危険なコードが渡されないように、cfqueryparam タグを使用しています。

<cfoutput query=detail> 
#Emp_ID#: #FirstName# #LastName# <br> 
</cfoutput>

詳細クエリを使用して、従業員 ID、名および姓のリストを表示します。

<cfoutput> 
#master.columnlist#<br> 
</cfoutput>

プライマリクエリで返されたすべての列のリストを表示します。

<cfoutput> 
#detail.columnlist#<br> 
</cfoutput>

詳細クエリで返されたすべての列のリストを表示します。

レコードセットのデータを分割して表示する方法

データベースが大きい場合は、一度に表示する行数を制限できます。これをクエリオブクエリの  currentRow クエリ変数を使用して行う方法を次の例で示します。クエリ変数について詳しくは、Getting information about query results を参照してください。

  1. 次の内容の ColdFusion ページを作成します。

    <html>
    <head>
    <title>QoQ with incremental row return</title>
    </head>
    <body>
    <h3>QoQ with incremental row return</h3>
    <!--- startrow と maxrows を定義して、「次の N 件」スタイルのブラウジングを容易にする --->
    <cfparam name = &quot;MaxRows&quot; default = &quot;5&quot;>
    <cfparam name = &quot;StartRow&quot; default = &quot;1&quot;>
    <! --- プライマリクエリ:Employee テーブルからすべての情報を取得 --->
    <cfquery name = &quot;GetSals&quot; datasource = &quot;cfdocexamples&quot;>
    SELECT * FROM Employee
    ORDER BY LastName
    </cfquery>
    <! --- 詳細クエリ:プライマリクエリから 3 つのフィールドを選択 --->
    <cfquery name = &quot;GetSals2&quot; dbtype = &quot;query&quot;>
    SELECT FirstName, LastName, Salary
    FROM GetSals
    ORDER BY LastName
    </cfquery>
    <! --- 出力を表示するためのテーブルを作成 --->
    <table cellpadding = 1 cellspacing = 1>
    <tr>
    <td bgcolor = f0f0f0>
    <b><i>&nbsp;</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 = &quot;GetSals2&quot; startrow = &quot;#StartRow#&quot; maxrows = &quot;#MaxRows#&quot;>
    <tr>
    <td valign = top bgcolor = ffffed>
    <b>#GetSals2.currentRow#</b>
    </td>
    <td valign = top>
    <font size = &quot;-1&quot;>#FirstName#</font>
    </td>
    <td valign = top>
    <font size = &quot;-1&quot;>#LastName#</font>
    </td>
    <td valign = top>
    <font size = &quot;-1&quot;>#LSCurrencyFormat(Salary)#</font>
    </td>
    </tr>
    </cfoutput>
    <!--- レコードの総数が行の総数以下の場合は、
    StartRow 値を MaxRows(この例では 5)だけインクリメントしたうえで
    同じページへのリンクを提供 --->
    <tr>
    <td colspan = 4>
    <cfif (startrow + maxrows) lte getsals2.recordcount>
    <a href=&quot;qoq_next_row.cfm?startrow=<cfoutput>#Evaluate(StartRow +
    MaxRows)#</cfoutput>&quot;>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 = &quot;MaxRows&quot; default = &quot;5&quot;> <cfparam name = &quot;StartRow&quot; default = &quot;1&quot;> <! --- プライマリクエリ:Employee テーブルからすべての情報を取得 ---> <cfquery name = &quot;GetSals&quot; datasource = &quot;cfdocexamples&quot;> SELECT * FROM Employee ORDER BY LastName </cfquery> <! --- 詳細クエリ:プライマリクエリから 3 つのフィールドを選択 ---> <cfquery name = &quot;GetSals2&quot; dbtype = &quot;query&quot;> SELECT FirstName, LastName, Salary FROM GetSals ORDER BY LastName </cfquery> <! --- 出力を表示するためのテーブルを作成 ---> <table cellpadding = 1 cellspacing = 1> <tr> <td bgcolor = f0f0f0> <b><i>&nbsp;</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 = &quot;GetSals2&quot; startrow = &quot;#StartRow#&quot; maxrows = &quot;#MaxRows#&quot;> <tr> <td valign = top bgcolor = ffffed> <b>#GetSals2.currentRow#</b> </td> <td valign = top> <font size = &quot;-1&quot;>#FirstName#</font> </td> <td valign = top> <font size = &quot;-1&quot;>#LastName#</font> </td> <td valign = top> <font size = &quot;-1&quot;>#LSCurrencyFormat(Salary)#</font> </td> </tr> </cfoutput> <!--- レコードの総数が行の総数以下の場合は、 StartRow 値を MaxRows(この例では 5)だけインクリメントしたうえで 同じページへのリンクを提供 ---> <tr> <td colspan = 4> <cfif (startrow + maxrows) lte getsals2.recordcount> <a href=&quot;qoq_next_row.cfm?startrow=<cfoutput>#Evaluate(StartRow + MaxRows)#</cfoutput>&quot;>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 = &quot;MaxRows&quot; default = &quot;5&quot;> 
    <cfparam name = &quot;StartRow&quot; default = &quot;1&quot;> 
    
    <! --- プライマリクエリ:Employee テーブルからすべての情報を取得 ---> 
    <cfquery name = &quot;GetSals&quot; datasource = &quot;cfdocexamples&quot;> 
    SELECT * FROM Employee 
    ORDER BY LastName 
    </cfquery> 
    
    <! --- 詳細クエリ:プライマリクエリから 3 つのフィールドを選択 ---> 
    <cfquery name = &quot;GetSals2&quot; dbtype = &quot;query&quot;> 
    SELECT FirstName, LastName, Salary 
    FROM GetSals 
    ORDER BY LastName 
    </cfquery> 
    
    <! --- 出力を表示するためのテーブルを作成 ---> 
    <table cellpadding = 1 cellspacing = 1> 
    <tr> 
    <td bgcolor = f0f0f0> 
    <b><i>&nbsp;</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 = &quot;GetSals2&quot; startrow = &quot;#StartRow#&quot; maxrows = &quot;#MaxRows#&quot;> 
    <tr> 
    <td valign = top bgcolor = ffffed> 
    <b>#GetSals2.currentRow#</b> 
    </td> 
    
    <td valign = top> 
    <font size = &quot;-1&quot;>#FirstName#</font> 
    </td> 
    
    <td valign = top> 
    <font size = &quot;-1&quot;>#LastName#</font> 
    </td> 
    
    <td valign = top> 
    <font size = &quot;-1&quot;>#LSCurrencyFormat(Salary)#</font> 
    </td> 
    </tr> 
    </cfoutput> 
    <!--- レコードの総数が行の総数以下の場合は、
    StartRow 値を MaxRows(この例では 5)だけインクリメントしたうえで
    同じページへのリンクを提供 ---> 
    <tr> 
    <td colspan = 4> 
    <cfif (startrow + maxrows) lte getsals2.recordcount> 
    <a href=&quot;qoq_next_row.cfm?startrow=<cfoutput>#Evaluate(StartRow + 
    MaxRows)#</cfoutput>&quot;>See next <cfoutput>#MaxRows#</cfoutput> 
    rows</a> 
    </cfif> 
    </td> 
    </tr> 
    </table> 
    </body> 
    </html>
  2. このページを qoq_next_row.cfm として保存します。 保存場所は  web_root 下の myapps ディレクトリです。

  3. ブラウザーで qoq_next_row.cfm を表示します。

cfdump タグによるクエリ結果の表示

CFML コードをデバッグする際は、cfdump タグを使用すると、クエリの内容をすぐに表示できます。このタグの形式は次のとおりです。

<cfdump var=&quot;#query_name#&quot;>
<cfdump var=&quot;#query_name#&quot;>
<cfdump var=&quot;#query_name#&quot;>

cfdump タグについて詳しくは、CFML リファレンスを参照してください。

SQL 以外のレコードセットでのクエリオブクエリの使用

クエリオブクエリは、レコードセットを返す任意の CFML タグまたは関数に対して使用できます。対象は cfquery の結果に限りません。SQL 以外のレコードセットに対してクエリを実行できます。例えば、 cfdirectory タグ、 cfsearch タグ、 cfldap タグなどです。

ヘルプをすばやく簡単に入手

新規ユーザーの場合