Connect Server や API Server で関連するテーブルのデータを一度に取得する

by 浦邊信太郎 | 2023年07月28日

本記事ではConnect Server のOData エンドポイントやAPI Server で、関連するテーブルのデータを一つのレスポンスとして取得する方法を紹介します。Connect Server やAPI Server では通常一つのエンドポイントで一つのテーブルのデータにアクセスしますが、このときテーブルに関連付けられた他のテーブルのデータを一緒に取得することができます。関連テーブルの取得にはOData の$expandクエリオプションを使います。$expandクエリオプションは関連リソースをインラインで取得します。

本機能のヘルプドキュメントも合わせてご覧ください。

• Connect Server V22
• API Server V22

基本的な関連テーブルの取得方法

関連テーブルのデータを取得するにはリソースに関連の定義を追加し、クエリ実行時に$expandクエリオプションで関連テーブルを指定します。

リソースに対する関連の定義

Connect Server を例にとり説明します。はじめに親テーブルと関連する子テーブルのリソースを作成します。以下の例では製品カテゴリとしてCategory テーブルが、製品としてProducts テーブルが存在します。ある製品カテゴリを取得したときに、当該カテゴリに属する製品データを一緒に取得するよう設定します。

親テーブルであるCategory リソースにリレーションの定義を追加します。リソースの設定画面を開き、[Code View]をクリックしてリソース定義編集画面を開きます。

リソース定義にリレーションの定義を追加します。キー項目にrelationships 属性を追加し、「リレーション名(リソース名.カラム名)」の形式でリレーションの定義を記述します。

クエリ実行方法

定義した関連を使って関連テーブルにアクセスするには$expand クエリオプションを使用します。以下のクエリではカテゴリIDが「1」のカテゴリと、それに属する製品のリストを取得します。

http://MyServer:MyPort/odata.rsc/Category(1)?$expand=Products

このクエリを実行すると以下のレスポンスが返ってきます。指定したカテゴリと関連する製品のリストが一つのレスポンスとして取得できます。

    
"value": [{
    "CategoryID": "1","CategoryName": "Books",
    "Products": [
        { "ProductID": "101", "Price": "45", "Name": "Astronomy 101", "Supplierid": "1", "CompanyId": "1", "CategoryId": "1"},
        { "ProductID": "102", "Price": "50", "Name": "Physics 101", "Supplierid": "2", "CompanyId": "1", "CategoryId": "1"},
        { "ProductID": "103", "Price": "65", "Name": "Chemistry 101", "Supplierid": "3", "CompanyId": "1", "CategoryId": "1" } 
    ]}
]

様々なリレーションシップの取得方法

関連テーブルは1対多や多対多など様々なリレーションシップが定義できます。以下では各パターンの取得方法を説明します。

サンプルデータ

このセクションでは以下のサンプルデータで説明します。サンプルデータはProducts (製品), Category (カテゴリ), Supplier (サプライヤ), ProductReviews (製品レビュー) の4つのテーブルがあり、各製品は一つのカテゴリと一つのサプライヤ、複数の製品レビューを持ちます。

各テーブルのデータを以下に示します。

1対多のリレーションシップ

一つのレコードと、関連付けられたテーブルの複数項目を取得します。例えば親子関係にあるテーブルのデータに対して親レコードとその子レコードを取得する場合が当てはまります。ここではカテゴリ「Books」と、当該カテゴリに属する製品「Astoronomy 101」、「Physics 101」、「Chemistory 101」を取得します。リレーションシップのイメージと取得するデータを以下に示します。

はじめにリレーションを定義します。Category リソースの項目CategoryId に対して以下のリレーションを定義します。

<attr name="CategoryID" key="true" type="int" isnullable="false" relationships="*Products(Products.CategoryID)">

リレーション名は「Products」で、一対多の関係を示すために「*」を付与します。取得する製品のキーとしてProducts リソースの外部キーCategoryId を参照します。

以下に実行するクエリの例を示します。このクエリはId が「1」のカテゴリ「Books」と、そのカテゴリに関連付けられた製品情報を取得します。$expand には定義したリレーション名「Products」を指定します。

http://MyServer:MyPort/api.rsc/Category(1)?$expand=Products

クエリの実行結果

{"@odata.context":"http://MyServer:MyPort/odata.rsc/$metadata#Category/$entity",
"Products": [
    {"RowId": 2, "CompanyId": "1", "Name": "Astronomy 101", "Price": "45", "ProductID": "101", "SupplierId": "1", "CategoryID": 1},
    {"RowId": 3, "CompanyId": "1", "Name": "Physics 101", "Price": "50", "ProductID": "102", "SupplierId": "2", "CategoryID": 1},
    {"RowId": 4, "CompanyId": "1", "Name": "Chemistry 101", "Price": "65", "ProductID": "103", "SupplierId": "3", "CategoryID": 1}
],
"RowId": 2, "CategoryName": "Books", "CategoryId": 1}

多対多のリレーションシップ

一つのテーブルの複数レコードと、関連付けられた他テーブルの項目を取得します。ここでは製品「Astoronomy 101」、「Physics 101」と、当該製品が属するカテゴリを取得します。リレーションシップのイメージと取得するデータを以下に示します。リレーション名は「ProductsCategory」で、取得するカテゴリのキーとしてCategory リソースのCategoryId を参照します。

Products リソースの項目CategoryId を以下のように編集します。

<attr name="CategoryId" key="false" type="int" isnullable="true" relationships="ProductsCategory(Category.CategoryId)" desc="">

以下に実行するクエリの例を示します。このクエリは定義したリレーションProductsCategory を取得しますが、$filter で価格が60ドル以下の製品「Astoronomy 101」、「Physics 101」に絞っています。このように$expand は他のクエリオプションと併用することができます。

http://MyServer:MyPort/api.rsc/Products?$expand=ProductsCategory&$filter=price lt 60

クエリの実行結果

{"@odata.context":"http://MyServer:MyPort/odata.rsc/$metadata#Products",
"value":[
    {
        "ProductsCategory": {"RowId": 2, "CategoryId": 1, "CategoryName": "Books", "CategoryId": 1},
        "RowId": 3, "CategoryID": 1, "CompanyId": "1", "Name": "Physics 101", "Price": "50", "ProductID": "102", "SupplierId": "2", "CategoryID": 1
    },
    {
        "ProductsCategory": {"RowId": 2, "CategoryId": 1, "CategoryName": "Books", "CategoryId": 1},
        "RowId": 2, "CategoryID": 1, "CompanyId": "1", "Name": "Astronomy 101", "Price": "45", "ProductID": "101", "SupplierId": "1", "CategoryID": 1
    }
]}

複数リレーションシップ

$expand パラメータは複数のリレーションシップをカンマ区切りで指定することができます。リレーションシップのイメージと取得するデータを以下に示します。ここでは製品「Physics 101」のサプライヤと製品レビューを取得します。製品とサプライヤのリレーションシップは多対1、製品と製品レビューのリレーションシップは1対多です。

Products リソースの項目ProductId とSupplierId を以下のように編集します。

<attr name="ProductId" key="true" type="int" isnullable="true" readonly="readonly" relationships="*ProductReviews(ProductReviews.ProductId)" desc="" >
<attr name="SupplierId" key="false" type="int" isnullable="true" readonly="readonly" relationships="*Supplier(Supplier.SupplierId)" desc="" >

以下に実行するクエリの例を示します。このクエリはId が「102」の製品と、その製品に関連付けられた製品レビュー、サプライヤを取得します。$expand には定義したリレーション名「ProductReview」、「Supplier」をカンマ区切りで指定します。

http://MyServer:MyPort/odata.rsc/Products(102)?$expand=ProductReviews,Supplier

クエリの実行結果

{"@odata.context":"http://MyServer:MyPort/odata.rsc/$metadata#Products/$entity",

"Supplier": [
    {"RowId": 2, "SupplierId": "2", "SupplierName": "New Science Publishing"}
],

"ProductReviews": [
    {"RowId": 2, "Description": "Easy to read, good illustrations.", "ProductId": "102", "ProductReviewID": "1001", "Rating": "4.5"},
    {"RowId": 3, "Description": "Boring!", "ProductId": "102", "ProductReviewID": "1002", "Rating": "1.5"}
], 

"RowId": 3, "CompanyId": "1", "Name": "Physics 101", "Price": "50", "SupplierId": 2, "CategoryID": 1, "ProductID": 102, "SupplierId"

関連コンテンツ