任意の検索条件や複数の並び順（ソート順）を指定してデータベースからレコードを引っ張り出してくるために、検索メソッドの設計や実装をどうしようかというお話です。
はじめに
ガチガチにピンポイントで設計する方法もありますが、ある程度の自由度・拡張性をもたせて実装に余裕を持たせたい所でもあります。その意外と困るのが検索系です。
最初はシンプルな検索でOKとしつつ、あとから機能拡張で追加条件が入りやすい部分だったりします。そんな想定を見据えた設計・実装例です。
ここでは TypeScript の型定義を活かして実装してみます。データベースは PostgreSQL 基準で実装してますが、 MySQL や SQL Server 等でも応用が効くと思います。
実装方針 - 共通設計
色々な検索メソッドを作る上で実装ルールがあると、メソッドを作る側・使う側共に使い勝手が分かりやすくなるので出来る限り統一してみます。方針はこんなところでしょうか。
- ページ処理やソート順指定、検索結果の枠組みは型定義として汎用化する
- ジェネリック型を活用する
- ソート指定する時の名称は抽象化する
- 検索を実装しているメソッドにテーブル名や列名を渡さない
- 絞り込み条件は実装ごとに定義する
- 後で絞り込み条件増えたとき対策
- 型定義のキー名で意味付けできる
- 検索メソッドをルール化する
- 第1引数で絞り込み条件を丸ごと指定する
- 第2引数に検索結果に関するオプションを指定する
- 戻り値は検索結果のコレクションとページ処理結果を必ず含む
サンプル
ここでは書籍データを検索することにします。
要件
- 検索要件
- 書籍名で絞り込める（部分一致）
- 著者名で絞り込める（部分一致）
- ソート項目・任意の順番で複数指定可能
- 書籍名でソートできる
- 著者名でソートできる
- 発行日でソートできる
実装してみる
共通の型定義
まずは実装方針1にある、汎用的に使いまわせれる部分の定義です。
ページ処理は必ずあるがソート出来ないメソッド実装のケースを考慮して分けてみます。
/** 検索オプション */
export type FindOptions = {
/** 取得する先頭位置 (0始まり) */
offset: number;
/** 取得する最大件数 */
limit: number;
};
/** 並び順 */
export type SortOrder<TKey extends string> = {
key: TKey;
/** 降順指定 (false:昇順・デフォルト / true:降順) */
descending?: boolean;
};
/**
* 並び順付き検索オプション
* @template TKey 並び順として指定できる名前。複数指定は `"id" | "name"` のようにする。
*/
export type FindOrderedOptions<TKey extends string> = FindOptions & {
/** 並び順 */
orders: SortOrder<TKey>[];
};
戻り値も共通する型定義を作ります。
/**
* 検索結果とページ処理
* @template TItem 検索結果1アイテム分の定義
*/
export type FindResult<TItem> = {
/** 検索結果: 0 から limit 個の配列 */
items: TItem[];
/** 検索結果の個数 */
count: number;
/** ページ処理 */
pagination: {
/** ページ絞り前の全件数 */
totalCount: number;
/** 取得した先頭位置 (0始まり) */
offset: number;
/** 取得する最大件数 */
limit: number;
};
};
固有の型定義
次は実装方針2,3に関わる所です。
書籍検索固有の型定義と共通の型定義を利用して作り込みます。ビジネスルールに則った名称を採用し、データベース構造（テーブル名や列名、命名規則）に依存しない名称にします。
ドメイン駆動設計を採用しているなら、ドメイン層で定義して値オブジェクト等々も活用する事になると思います。
/** 絞り込み条件 */
export type FindBookCondition = {
bookTitle?: string;
author?: string;
};
/** 検索結果の1アイテム分 */
export type FindBookResultItem = {
bookId: string;
bookTitle: string;
authorId: string;
author: string;
dateOfPublication: Date;
};
/** 指定できる並び順 */
type FindBookSortKey = "bookTitle" | "author" | "dateOfPublication";
// 短縮して使いやすいように型エイリアスを定義
export type FindBookOptions = FindOrderedOptions<FindBookSortKey>;
export type FindBookResult = FindResult<FindBookResultItem>;
検索処理の実装
最後に検索本体です。実装方針4に従って引数や戻り値を整えます。方針を再掲します。
- 第1引数で絞り込み条件を丸ごと指定する
- 第2引数に検索結果に関するオプションを指定する
- 戻り値は検索結果のコレクションとページ処理結果を必ず含む
SQL組み立ては「ページ処理しながら全件数を一撃で得るSELECT文」を応用してみます（宣伝）。
/**
* 書籍検索
* @param condition 絞り込み条件
* @param options ページ・並び替え
*/
export async function findBook(
condition: FindBookCondition,
options?: FindBookOptions
): Promise<FindBookResult> {
const offset = options?.offset ?? 0;
const limit = options?.limit ?? 10;
const wheres = new Array<string>();
// ページ処理用の値を先に詰めておく
const values = new Array<string | number>(offset, limit);
let valueIndex = 3;
// 書籍名
if (condition.bookTitle) {
values.push(`%${condition.bookTitle}%`);
wheres.push(`b.book_title LIKE \$${valueIndex++}`);
}
// 著者名
if (condition.author) {
values.push(`%${condition.author}%`);
wheres.push(`a.author LIKE \$${valueIndex++}`);
}
// 並び順
const orders =
options?.orders.map<string>((order) => {
const sortOrder = order.descending ? "DESC" : "ASC";
switch (order.key) {
case "bookTitle":
return `b.book_title ${sortOrder}`;
case "dateOfPublication":
return `b.date_of_publication ${sortOrder}`;
case "author":
return `a.author ${sortOrder}`;
default:
throw Error(`未定義の並び順: ${order.key}`);
}
}) || [];
// 常に付けたい並び順
orders.push("b.id ASC");
// SQL 組み立て
const sqlWhere = wheres.length ? `WHERE (${wheres.join(") AND (")})` : "";
const sql = `
WITH cte AS (
SELECT
b.id AS book_id, b.book_title, b.date_of_publication,
a.id AS author_id, a.author
FROM book AS b
INNER JOIN author AS a ON b.author_id = a.id
${sqlWhere}
ORDER BY ${orders.join(",")}
)
SELECT *, (SELECT COUNT(*) FROM cte) AS total_count
FROM cte
OFFSET $1 LIMIT $2`;
// データベース問い合わせ実行
const client = await connectDatabase();
try {
// https://node-postgres.com/features/queries
const result = await client.query(sql, values);
}
finally {
await client.end();
}
// 詰め替え
const items = result.rows.map<FindBookResultItem>((row) => {
return {
bookId: String(row.book_id),
bookTitle: String(row.book_title),
authorId: String(row.author_id),
author: String(row.author),
dateOfPublication: new Date(row.date_of_publication),
};
});
// 絞り込み結果の全件数
// NOTE: オフセットが範囲外だった場合を考慮せず
const totalCount = result.rows[0]?.total_count || 0;
return {
items,
count: items.length;
pagination: {
totalCount,
offset,
limit,
},
};
}
補足1 - エスケープ処理
LIKE として指定する値の絞り込み条件のエスケープ処理を省略しています。実用するには書籍名や著者名に含まれる % や _ などに対する特殊文字の対策が必要になります。
補足2 - 分割
引数処理や詰め替え部分など部分ごとに別メソッド化なり、そもそもクラスにした方が良い等ありますが、、。処理フローをざっと上から下へ追えることを重視して1メソッドに収めました。長いっ。
データベース接続はよしなに。
import { Client } from "pg";
export async function connectDatabase(): Promise<Client> {
const client = new Client();
await client.connect();
return client;
}
解説: 検索条件のSQL組み立て
検索条件の一文が組み立てが複雑になる所です。
const sqlWhere = wheres.length ? `WHERE (${wheres.join(") AND (")})` : "";
PostgreSQL のライブラリ pg で名前付きパラメーターを使えないので、可変する条件に併せてパラメーターのインデックスを工夫します。
CASE 1
書籍名、著者名の両方とも指定されたら以下のように展開されます。
WHERE (b.book_title LIKE $3) AND (a.author LIKE $4)
values は [0, 10, "%吾輩は%", "%夏目%"] のように積みます。
CASE 2
著者名だけ指定されたら以下のように展開されます。
WHERE (a.author LIKE $3)
values は [0, 10, "%夏目%"] のように積みます。
CASE 3
検索条件が全く無い場合は WHERE 句を生成しません。
values は [0, 10] のようにページ処理のパラメーターだけ積んだ状態になります。
大抵何かしらの固定条件があると思うので、実ケースとして WHERE 無しは少数でしょう。
検索する
こんな感じで検索できるようになります。
const result = await findBook({
bookTitle: "吾輩は",
author: "夏目",
},
{
offset: 0,
limit: 10,
// 第1ソートが書籍名、第2ソートが発行日の降順
orders: [
{ key: "bookTitle" },
{ key: "dateOfPublication", descending: true },
],
});
console.log(result.items);
console.log(result.count);
console.log(result.pagination);
findBook() を呼び出すときにテーブル構造を意識しないで、一貫した論理名で検索条件や並び順を指定できるようになりました！
さいごに
「第1引数が絞り込み条件、第2引数が検索結果に関わるオプション」というざっくり共通ルールを引きつつ、ある程度の柔軟性を確保できるメソッドデザインになったと思います。
今回は扱いませんでしたが、「含まない(NOT)」「または(OR)」みたいな複雑な条件が増える時にどう拡張するか、共通化していくかが肝になりそうです。
検索メソッドの引数・戻り値に悩む日々のお役に立てれば。
それではまた！
私達は『ワークスタイルの未来を切り拓く』という理念のもとワークフローの本質を追求し、新しいワークスタイルを提案するとともに、コラボスタイルが率先して時代に適したワークスタイルを常にバージョンアップし発信していきます。
Discussion