CData API Server で OData $expand を使い、関連する複数テーブルを 1 回の API 呼び出しで取得する

複数のテーブルにまたがる関連データを取得しようとすると、1 画面あたり 3～4 回の API 呼び出しが必要になりがちです。顧客情報に 1 回、その顧客の注文に 1 回、各注文に含まれる製品にもう 1 回、といった具合です。その結果、画面の表示は遅くなり、取得した結果をクライアント側でつなぎ合わせるコードもメンテナンスしにくくなってしまいます。

CData API Server はデータを OData で公開します。OData には $expand というキーワードがあり、親の行とそれに関連する子の行をすべて 1 回のリクエストで返すことができます。API Server でリレーションシップを一度定義しておけば、OData に対応したクライアントならどれでもすぐに利用できます。

この記事では、関連テーブルをまとめて取得する 2 つのアプローチと、それぞれがどんな場面に向いているかをご紹介します。

アプローチ A：API Server で定義したリレーションシップを使った OData $expand（OData ネイティブなアプローチ）
アプローチ B：データソース側で結合済みの SQL ビュー（より深い階層に対応するための代替手段）

前提条件

始める前に、以下を準備してください。

CData API Server がインストールされ、起動していること（まだセットアップしていない場合は、先にインストールと Postman でのテスト手順を済ませておきましょう。15 分ほどで完了します）
関連テーブルを作成できる SQL Server データベース（Azure SQL、オンプレミスの SQL Server、LocalDB のいずれか）
Postman などの REST クライアント
認証トークンを持つ API Server ユーザー（トークンはすべてのリクエストで x-cdata-authtoken ヘッダーに含めて送信します）

Step 1：サンプルデータを準備する

ここでは、4 つのテーブルからなるデータモデルを作成し、サンプルデータを登録します。すでに関連テーブルがある場合は Step 2 に進み、以降のテーブル名やカラム名をご自身のものに置き換えてお読みください。

スキーマ

サンプルのスキーマは、典型的な EC サイトの構成になっています。

Customers は複数の Orders を持つ（1 人の顧客に複数の注文）
Orders は複数の OrderDetails を持つ（1 件の注文に複数の明細）
OrderDetails は Products にリンクする（複数の明細が、それぞれ 1 つの製品に対応）

テーブルを作成する

次の SQL を実行して、4 つのテーブルをすべて作成します。スクリプトでは USE mydb を使っているので、データベース名が異なる場合は mydb を置き換えてください。

USE mydb;
GO

CREATE TABLE Customers (
    CustomerID    INT PRIMARY KEY IDENTITY(1,1),
    CustomerName  NVARCHAR(100) NOT NULL,
    Email         NVARCHAR(150),
    Country       NVARCHAR(50),
    CreatedDate   DATETIME DEFAULT GETDATE()
);

CREATE TABLE Products (
    ProductID     INT PRIMARY KEY IDENTITY(1,1),
    ProductName   NVARCHAR(100) NOT NULL,
    Category      NVARCHAR(50),
    UnitPrice     DECIMAL(10,2),
    StockQuantity INT,
    CreatedDate   DATETIME DEFAULT GETDATE()
);

CREATE TABLE Orders (
    OrderID          INT PRIMARY KEY IDENTITY(1,1),
    CustomerID       INT FOREIGN KEY REFERENCES Customers(CustomerID),
    OrderDate        DATETIME DEFAULT GETDATE(),
    TotalAmount      DECIMAL(10,2),
    Status           NVARCHAR(20),
    ShippingAddress  NVARCHAR(200)
);

CREATE TABLE OrderDetails (
    OrderDetailID  INT PRIMARY KEY IDENTITY(1,1),
    OrderID        INT FOREIGN KEY REFERENCES Orders(OrderID),
    ProductID      INT FOREIGN KEY REFERENCES Products(ProductID),
    Quantity       INT,
    UnitPrice      DECIMAL(10,2),
    Subtotal       AS (Quantity * UnitPrice) PERSISTED
);

テーブルができたら、クエリで確認できるよう各テーブルにいくつか行を追加しておきます。顧客、製品、注文がそれぞれ数件あれば十分です。手作業での入力を省きたい場合は、この記事に添付しているサンプルスクリプト（APIServerTest_sample_schema.sql）をご利用ください。1 回実行するだけで、4 つのテーブルの作成とリアルなサンプルデータの登録が完了します。

API Server で 4 つのテーブルを公開する

API Server の管理画面で接続タブを開き、SQL Server への接続が設定済みであることを確認します。

次に API タブに移動してテーブルを追加をクリックし、Customers、Products、Orders、OrderDetails を選択して確認をクリックします。

この時点では、各テーブルは関連データを含まないフラットなリストを返します。Postman で 4 つのエンドポイントをすべてテストし、データが正しく返ってくることを確認しておきましょう。

GET http://localhost:8080/api.rsc/mydb_dbo_Customers
GET http://localhost:8080/api.rsc/mydb_dbo_Orders
GET http://localhost:8080/api.rsc/mydb_dbo_Products
GET http://localhost:8080/api.rsc/mydb_dbo_OrderDetails

Postman で Customers テーブルから 200 OK レスポンスが返される様子

Step 2：アプローチ A：リレーションシップを使った OData $expand

API Server におけるリレーションシップの仕組み

API Server はデフォルトでは、公開したテーブルをそれぞれ独立したものとして扱います。データベース側で Customers.CustomerID と Orders.CustomerID を結ぶ外部キーがあっても、API Server が自動で検出することはありません。そのため、リレーションシップはカラムに明示的に定義する必要があります。

リレーションシップとは、カラムがどのテーブルのどのカラムにリンクしているかを API Server に伝えるためのちょっとした設定です。一度定義すれば、OData の $expand キーワードがそのリレーションシップをたどり、関連データを同じレスポンスで返してくれます。

1対多のリレーションシップを設定する：Customers から Orders

API Server の管理画面で API タブを開き、Customers テーブルの行にある編集（鉛筆アイコン）をクリックします。カラム一覧で CustomerID をクリックしてプロパティを開き、リレーションシップフィールドに次の文字列を正確に入力します。

*Orders(mydb_dbo_Orders.CustomerID)

ここでのポイントは 2 つです。

先頭のアスタリスク（*）は、1対多のリレーションシップであることを示します。これがないと、API Server は親 1 件につき子を 1 行しか返しません。ほとんどの場合、これは意図した結果ではないはずです。
カッコ内のテーブル名には、Orders だけでなく完全なリソース名（mydb_dbo_Orders）を指定します。これは API Server が公開している OData のリソース名なので、完全な形式で書く必要があります。

Customers の CustomerID カラムに定義した 1対多のリレーションシップ

基本的な $expand クエリを実行する

それでは Postman を開いて、次のリクエストを実行してみましょう。API Server は認証のないリクエストを拒否するため、Basic 認証の資格情報が必要です。

GET http://localhost:8080/api.rsc/mydb_dbo_Customers?$expand=Orders

各顧客のデータに、ネストされた Orders 配列が含まれるようになりました。結合されたデータが 1 回のリクエストで返ってくるので、クライアント側でつなぎ合わせる必要はありません。

$expand=Orders で各顧客の中にネストされた注文データが返される様子

多対1のリレーションシップを設定する：Orders から Customers

次は、注文に対応する顧客を同じ呼び出しで返すリレーションシップです。API タブで Orders テーブルの編集をクリックし、CustomerID カラムをクリックして、リレーションシップフィールドに次のように入力します。

CustomersOrder(mydb_dbo_Customers.CustomerID)

前のステップとの違いは 2 つあります。

アスタリスクは付けません。これは多対1のリレーションシップで、各注文はちょうど 1 人の顧客に対応するためです。
CustomersOrder は、$expand= で使うリレーションシップ名です。どちら向きの関係なのかがひと目でわかる、内容を表す名前を付けましょう。

Orders テーブルの CustomerID に定義した多対1のリレーションシップ

次のリクエストを実行します。

GET http://localhost:8080/api.rsc/mydb_dbo_Orders?$expand=CustomersOrder

各注文に、親である顧客の情報がネストされた CustomersOrder オブジェクトとして含まれるようになりました。

$expand=CustomersOrder で各注文の中に親の顧客情報が返される様子

もう 1 つリレーションシップを設定する：Orders から OrderDetails

API タブで Orders テーブルの編集をクリックし、OrderID カラムをクリックして次のように入力します。

*OrderDetails(mydb_dbo_OrderDetails.OrderID)

各注文は複数の OrderDetails 明細を持つため、アスタリスクを付けて1対多であることを示します。アスタリスクがないと、注文ごとに明細が 1 行しか返されません。

Orders テーブルの OrderID に定義した 1対多のリレーションシップ

$expand をほかの OData クエリオプションと組み合わせる

$expand は、ほかの OData クエリオプションと組み合わせて使えます。ほとんどのユースケースは、次の 3 つのパターンでカバーできます。

1 回の呼び出しで複数のリレーションシップを展開する：

GET http://localhost:8080/api.rsc/mydb_dbo_Orders?$expand=CustomersOrder,OrderDetails

すべての注文が、顧客情報と OrderDetails の両方を含んだ形で 1 つのレスポンスとして返されます。

CustomersOrder と OrderDetails の両方が 1 つのレスポンス内で展開される様子

$select で返すカラムを絞り込む：

GET http://localhost:8080/api.rsc/mydb_dbo_Orders?$expand=CustomersOrder($select=CustomerName)

ネストされた CustomersOrder オブジェクトには、CustomerName だけが含まれるようになります。関連テーブルのカラム数が多く、画面に必要なのは 1～2 カラムだけ、という場合に便利です。

$expand 内の $select で関連テーブルから CustomerName のみが返される様子

$filter で関連する行を絞り込む：

GET http://localhost:8080/api.rsc/mydb_dbo_Customers?$expand=Orders($filter=Status eq 'Completed')

各顧客の Orders 配列には、完了済みの注文だけが含まれるようになります。処理中の注文を表示したくないダッシュボードなどで役立ちます。

$expand 内の $filter で顧客ごとに完了済みの注文のみが返される様子

よくある落とし穴

アスタリスクを忘れずに。1対多のリレーションシップでアスタリスクを省略すると、API Server はそれを多対1として扱い、親 1 件につき子を 1 件だけ返します。一見正しそうな結果に見えるため、見落としやすいミスです。1対多のリレーションシップを定義するときは、必ずアスタリスクが付いているか確認しましょう。

$expand のネストには対応していません。$expand の中にさらに $expand を入れ子にすることはできません。たとえば、次のリクエストはサポートされていません。

GET http://localhost:8080/api.rsc/mydb_dbo_Customers?$expand=Orders($expand=OrderDetails)

顧客、その注文、さらに各注文の明細のように 3 階層以上の深さが必要な場合は、後述のアプローチ B を使います。

リレーションシップにはわかりやすい名前を。カッコの前に書く名前が、$expand= で指定する名前になります。特に同じ 2 つのテーブル間に1対多と多対1の両方のリレーションシップがある場合は、内容がわかる一貫した名前を付けておきましょう。

Step 3：アプローチ B：より深い階層には結合済みの SQL ビューを使う

$expand はネストできないため、親・子・孫のような階層構造には別のアプローチが必要です。結合をあらかじめ組み込んだ SQL ビューを作成して API Server で公開すれば、クライアントは結合の組み合わせごとに 1 行となるフラットな結果を受け取れます。

$expand ではなくビューを使うべきケース

3 階層以上の深さが必要な場合（Customers > Orders > OrderDetails > Products）
BI ツールやスプレッドシート、CSV エクスポートなど、ネストされた JSON よりフラットな行のほうが扱いやすいクライアントを使う場合
レスポンスに含めるカラムを細かくコントロールしたい場合
OData では表現できない集計、CASE 文、ウィンドウ関数などを結合に含める場合

ビューを作成する

SSMS で次の SQL を実行します。CREATE VIEW はバッチの最初のステートメントである必要があるため、GO での区切りが必須です。

USE mydb;
GO

CREATE VIEW dbo.vw_CustomersFullExpand AS
SELECT
    c.CustomerID     AS c_CustomerID,
    c.CustomerName   AS c_CustomerName,
    c.Country        AS c_Country,
    o.OrderID        AS o_OrderID,
    o.OrderDate      AS o_OrderDate,
    o.Status         AS o_Status,
    o.TotalAmount    AS o_TotalAmount,
    od.OrderDetailID AS od_OrderDetailID,
    od.Quantity      AS od_Quantity,
    od.Subtotal      AS od_Subtotal,
    p.ProductID      AS p_ProductID,
    p.ProductName    AS p_ProductName,
    p.Category       AS p_Category
FROM Customers c
LEFT JOIN Orders o        ON o.CustomerID  = c.CustomerID
LEFT JOIN OrderDetails od ON od.OrderID    = o.OrderID
LEFT JOIN Products p      ON p.ProductID   = od.ProductID;

カラム名に c_、o_、od_、p_ といったプレフィックスを付けておくと、レスポンスを見た人が各カラムの元テーブルをひと目で判別できます。

API Server でビューを公開する

API タブでテーブルを追加をクリックします。ビューはテーブルと同じ一覧に表示されます。SQL Server の接続を選択して vw_CustomersFullExpand を探し、選択してから確認をクリックします。

API Server で公開する vw_CustomersFullExpand を選択

ビューにクエリを実行する

GET http://localhost:8080/api.rsc/mydb_dbo_vw_CustomersFullExpand

結果は、（顧客、注文、注文明細、製品）の組み合わせごとに 1 行となるフラットな形式で返されます。同じ顧客や注文が注文明細の数だけ複数行に登場しますが、結合ビューとしては想定どおりの動作です。必要に応じてクライアント側で集計すれば、4 つのテーブルのデータを 1 回のリクエストで取得できます。

vw_CustomersFullExpand で 4 つのテーブルを結合したフラットな行が返される様子

Step 4：2 つのアプローチの使い分け

どちらのアプローチも有効で、同じ API Server 環境で併用することもできます。使い分けのポイントは、階層の深さとクライアントが求めるデータの形です。

$expand（アプローチ A）が向いているケース：

階層の深さが 1～2 レベル（親から子、または子から親）の場合
クライアントが OData に対応している、またはネストされた JSON を想定している場合
データベースに手を加えずに、どのリレーションシップを展開するかを呼び出し側がクエリ時に選びたい場合
スキーマが頻繁に変わり、それに合わせてビュー定義をメンテナンスするのが負担になる場合

結合済みビュー（アプローチ B）が向いているケース：

3 階層以上の深さが必要な場合（$expand のネストには対応していないため）
スプレッドシートや CSV、BI ツールで使うために、クライアントがフラットな行を必要とする場合
返すカラムや結合の構成を完全にコントロールしたい場合
集計、CASE 文、ウィンドウ関数などを結合に含める場合

迷ったときは、メンテナンスするインフラが少なくて済む $expand から始めるのがおすすめです。$expand では対応できない要件が出てきたら、ビューに切り替えましょう。

CData API Server でデータアクセスをシンプルに

CData API Server を使えば、クライアントのコードを書き直したり、アプリケーションごとに独自の結合ロジックを管理したりすることなく、標準的な OData クエリで関連データを簡単に公開できます。1～2 レベルのリレーションシップは OData の $expand でネイティブに扱え、さらに結合済みの SQL ビューを使えば、データモデルに応じてどんな深さの階層にも対応できます。

ぜひ CData API Server の 30 日間の無料トライアルで、より高速でスケーラブルな REST API 連携をお試しください。

CData は、AI を本番環境で機能させるデータレイヤーです。数百の主要エンタープライズソースへのライブコネクティビティとレプリケーション、セマンティックコンテキスト、組み込みガバナンスを提供。Databricks、Microsoft、Google、Palantir をはじめ、世界中の 10,000 以上のお客様の AI を支えています。

お問い合わせ

CData Connect AI Gateway のご紹介

ユースケース別

プラットフォーム別

ユースケース別

ソース別

導入事例

デベロッパーセンター

5分で始めるクイックスタート

マネージド

セルフホスト

探索

参加する

サポート & サービス