はじめに

前回は応用編としてリレーション関連の操作を書きました。

今回は応用編シリーズの第2回目として複雑なクエリについて解説します。
なお、基本的な環境構築については、初回の記事を参考にしてください。

事前準備

データモデルの定義

3つのテーブルを使用します。

schema.prismaに以下のような定義を追加します。

// 他の定義は省略

model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String
  profile   Profile?
  posts     Post[]
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

model Profile {
  id          Int       @id @default(autoincrement())
  bio         String?
  birthDate   DateTime?
  phoneNumber String?
  address     String?
  user        User      @relation(fields: [userId], references: [id], onDelete: Cascade)
  userId      Int       @unique
  createdAt   DateTime  @default(now())
  updatedAt   DateTime  @updatedAt
}

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String
  published Boolean  @default(false)
  author    User     @relation(fields: [authorId], references: [id], onDelete: Cascade)
  authorId  Int
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

以下のコマンドを実行することで、schema.prismaの設定を反映します。

npx prisma migrate dev --name init

データの用意

データ投入用に以下のスクリプトを用意しました。
prismaフォルダ配下にseed.tsというファイルで作成します。

/* prisma/seed.ts */
import { PrismaClient } from "@prisma/client";
const prisma = new PrismaClient();
async function main() {
  // DBをクリーンアップ
  await prisma.post.deleteMany();
  await prisma.user.deleteMany();
  await prisma.post.deleteMany();

  // ユーザーデータ、およびプロフィールデータの作成
  const users = await Promise.all([
    prisma.user.create({
      data: {
        email: "joint@example.com",
        name: "Joint 太郎",
        profile: {
          create: {
            bio: "バックエンドエンジニア",
            birthDate: new Date("1990-01-15"),
            phoneNumber: "090-xxxx-xxxx",
            address: "東京都千代田区",
          },
        },
      },
    }),
    prisma.user.create({
      data: {
        email: "crew@example.com",
        name: "Crew 花子",
        profile: {
          create: {
            bio: "フロントエンドエンジニア",
            birthDate: new Date("1992-03-20"),
            phoneNumber: "080-xxxx-xxxx",
            address: "大阪府大阪市",
          },
        },
      },
    }),
  ]);

  // 投稿データの作成
  const articles = [
    {
      title: "TypeScriptの基礎",
      content: "TypeScriptの基本的な型システムについて解説します。",
    },
    {
      title: "Prismaの使い方",
      content: "PrismaのCRUD操作について説明します。",
    },
    {
      title: "Express入門",
      content: "Express.jsでAPIを作成する方法を紹介します。",
    },
    {
      title: "データベース設計",
      content: "データベース設計のベストプラクティスについて解説します。",
    },
    {
      title: "APIの設計方法",
      content: "RESTful APIの設計方法について説明します。",
    },
  ];

  const posts = [];
  const dates = [
    new Date("2024-01-15"),
    new Date("2024-02-01"),
    new Date("2024-02-15"),
    new Date("2024-03-01"),
    new Date("2024-03-15"),
  ];

  for (const user of users) {
    for (let i = 0; i < articles.length; i++) {
      posts.push(
        prisma.post.create({
          data: {
            title: articles[i].title,
            content: articles[i].content,
            published: i % 3 === 0,
            authorId: user.id,
            createdAt: dates[i],
            updatedAt: dates[i],
          },
        })
      );
    }
  }

  await Promise.all(posts);

  console.log("テストデータの作成が完了しました。");
}

main()
  .catch((e) => {
    console.error("データ作成中にエラーが発生しました:", e);
    process.exit(1);
  })
  .finally(async () => {
    await prisma.$disconnect();
  });

以下のコマンドを実行することで、データが投入されます。

npx ts-node prisma/seed.ts

ここまでで準備は完了です。

複雑なクエリ

全体の実装の確認

SQLの検索条件には、ORやAND、NOTなどの論理演算子だけでなく、INやLIKEなど、様々な演算子も指定できます。
Prismaを使ってそれらの条件を指定する方法を、具体的な実装から確認していきましょう。

/* index.ts */
import express, { Request } from "express";
import { PrismaClient, Prisma } from "@prisma/client";

// expressの設定
const app = express();
app.use(express.json());
const PORT = process.env.PORT || 3000;

// リッスンするポートの設定
app.listen(PORT, () => {
  console.log(`Server is running on http://localhost:${PORT}`);
});

// ルートパスへのリクエスト
app.get("/", (req, res) => {
  res.send("Server is running");
});

// prismaインスタンスの生成
const prisma = new PrismaClient({
  log: [{ emit: "event", level: "query" }],
});

// ログ出力の指定
prisma.$on("query", (e: Prisma.QueryEvent) => {
  console.log("Query: " + e.query);
  console.log("Params: " + e.params);
  console.log("Duration: " + e.duration + "ms");
});

// 検索用クエリパラメータの型定義
interface PostSearchPrams {
  title?: string;
  content?: string;
  published?: boolean;
  startDate?: Date;
  endDate?: Date;
  authorName?: string;
}

// 検索処理
app.get("/search", async (req: Request<{}, {}, {}, PostSearchPrams>, res) => {
  try {
    // クエリパラメータの取得
    const { title, content, published, startDate, endDate, authorName } =
      req.query;

    const posts = await prisma.post.findMany({
      where: {
        OR: [
          { title: { contains: title } },
          { content: { contains: content } },
        ],
        createdAt: {
          gte: startDate,
          lte: endDate,
        },
        author: {
          name: {
            startsWith: authorName,
          },
        },
      },
      include: {
        author: {
          select: {
            id: true,
            name: true,
            email: true,
          },
        },
      },
    });
    res.json({ data: posts });
  } catch (error) {
    console.error(error);
  }
});

たくさんの記述がありますが、重要なのは検索処理の中身です。

検索のオプション設定

findManyが検索処理を実施するためのメソッドです。SELECT文を発行します。
このfindManyに対して、オブジェクト形式で検索のオプションを渡します。

ここでは、whereとincludeを指定しています。いずれも値として更にオブジェクトを受け取ります。
このことからわかるように、Prismaでは基本的に様々なオブジェクトをオプションとして設定することでSQL文を組み立てます。

findManyのオブジェクトに指定することができるプロパティには、他にselect、distinct、orderBy、skip、take、cursorがあります。

selectは取得するカラムの指定、distinctは重複の削除、orderByはソート順、skip、take、cursorはページネーションに関係するオプションです。

本記事では、上記にあげたwhereとincludeについて詳しく解説します。

検索条件の設定

findManyの引数として設定するオブジェクトのwhereプロパティは、検索条件を設定するためのプロパティです。SQLで言うところのWHERE句に該当します。

whereプロパティに指定するオブジェクトには、大きく分けて5つのプロパティを指定します。
OR、AND、NOT、テーブルのカラム、リレーションのあるテーブルの5つです。

実装例ではORとテーブルのカラム、（createdAt）、リレーションのあるテーブル（author）を指定していました。

where: {
  OR: [
    { title: { contains: title } },
    { content: { contains: content } },
  ],
  createdAt: {
    gte: startDate,
    lte: endDate,
  },
  author: {
    name: {
      startsWith: authorName,
    },
  },
},     

OR、AND、NOTの指定

Prismaで使われるOR、AND、NOTは、SQLの論理演算子と同様の意味を持ちます。
ただ、何も指定しなければAND条件で設定されるので、ANDを明示的に指定することは基本的にはありません。

これらのプロパティには配列で値を指定します。配列の要素はwhereに指定するオブジェクトと同じ形式（OR、AND、NOT、テーブルのカラムのいずれか）になります。
実装例ではOR条件としてtitleとcontentを指定しています。

title、contentに指定できる値については、次で解説します。

テーブルのカラムの指定

createdAt、title、contentなどのテーブルのカラムのプロパティには、値、またはオブジェクトを指定します。

値を指定した場合は、SQLのwhere句における「=」と同じ意味になります。
基本的には値をそのまま設定すれば問題ありません。

「>=」や「<>」、「LIKE」、「IN」などを表現したい場合、オブジェクトを指定します。

指定できるオブジェクトは、カラムの型によって異なります。
なお、カラムの型は「schema.prisma」で指定した「model」の定義で決まります。

String型、Int型、Datetime型の場合、共通して以下のプロパティが設定できます。

• equals：SQLの「=」に該当、明示的に指定することは基本的にない
• not：SQLの「<>」に該当
• in：SQLの「IN」に該当
• notIn：SQLの「NOT IN」に該当
• lt：SQLの「<」に該当、「less than（〜より少ない）」の頭文字より
• lte：SQLの「<=」に該当、「less than or equal to」より
• gt：SQLの「>」に該当、「greater than（〜より多い）」の頭文字より
• gte：SQLの「>=」に該当、「greater than or equal to」より

String型特有のプロパティとしては以下があります。

• contains：部分一致、SQLの「LIKE "%値%"」に該当
• startsWith：前方一致、SQLの「LIKE "値%"」に該当
• endsWith：後方一致、SQLの「LIKE "%値"」に該当
• mode：検索モード、「insensitive」を指定すると大文字小文字の区別をしない
  ※PostgreSQL、MongoDBで指定可能なオプション。MySQL、MariaDB、SQL Serverではデフォルトで大文字小文字の区別をしないので、使用する必要なし。

実装例の中では、lte、gte、contains、startWithを使用していました。

テーブルのカラムの指定方法は以上になりますが、上記の中で、「IS NULL」、「IS NOT NULL」が無いことに気付いたでしょうか。

「IS NULL」については、そのまま値としてnullを設定します。
「IS NOT NULL」はオブジェクトのnotプロパティにnullを指定することで表現できます。

リレーションのあるテーブルの指定

authorはカラムのように見えますが、カラムではありません。「schema.prisma」で指定したUserテーブルとのリレーションを指す、リレーション名です。
これを使用することでUserテーブルに関連するクエリを表現することができます。

リレーション名には、whereに指定するオブジェクトと同じ形式のオブジェクトを指定できます。
ただ、ここでのポイントはリレーション先のテーブルに関連するオブジェクトになるということです。
authorはUserテーブルとのリレーションを表すため、指定できるのはUserテーブルに関連するカラムとなります。

実装例ではnameを指定しています。

取得対象の指定

includeは、リレーションのあるデータも一緒に取得する際に使用するオプションです。
「schema.prisma」で指定したリレーション名をオブジェクトのプロパティとして設定します。

実装例ではauthorを指定しています。

include: {
  author: {
    select: {
      id: true,
      name: true,
      email: true,
    },
  },
},

値としては、true、もしくはオブジェクトを設定します。
trueを指定した場合、リレーション先のテーブルのカラムをすべて取得します。

オブジェクトを設定する場合、select、またはincludeを指定できます。

selectの指定

selectを指定した場合、リレーション先のテーブルで取得したいカラムを絞ることができます。
取得したいカラムのプロパティに対して、trueを設定します。
実装例では、id、name、emailを取得対象としています。

includeの指定

includeで指定したリレーション先のテーブルがもつ、別のリレーションを参照することもできます。
PostテーブルはUserテーブルとのリレーションのみを持ちますが、UserテーブルはPostテーブルとProfileテーブルとのリレーションを持ちます。

Postテーブルに関連するUserに紐づくProfileを取得したい場合に、入れ子構造でincludeを指定することで、値を一気に取得することができます。

実際のSQL文を確認

ここまで複雑な設定を色々見てきましたが、実際にどのようなSQLが発行されているかを確認してみましょう。

以下のコマンドを実行することで、プログラムが起動します。

npx ts-node index.ts

以下のURLにアクセスしてください。

http://localhost:3000/search?title=Prisma&content=%E5%9F%BA%E6%9C%AC&published=true&startDate=2024-01-01T00:00:00Z&endDate=2024-03-31T23:59:59Z&authorName=Joint

次のようなデータが取得できるはずです。

次はコンソールを確認します。
SQLのログが出力されているはずです。

少し見づらいので整形したものが以下のとおりです。

-- 1つめのSQL
SELECT
    "public"."Post"."id",
    "public"."Post"."title",
    "public"."Post"."content",
    "public"."Post"."published",
    "public"."Post"."authorId",
    "public"."Post"."createdAt",
    "public"."Post"."updatedAt"
FROM
    "public"."Post"
LEFT JOIN 
    "public"."User" AS "j1" 
ON
    ("j1"."id") = ("public"."Post"."authorId")
WHERE
    (
        ("public"."Post"."title"::text LIKE $1 OR "public"."Post"."content"::text LIKE $2)
        AND "public"."Post"."createdAt" >= $3
        AND "public"."Post"."createdAt" <= $4
        AND ("j1"."name"::text LIKE $5 AND ("j1"."id" IS NOT NULL))
    ) 
OFFSET $6

/* 
1つめのSQLのパラメータ
Params:
["%Prisma%",
"%基本%",
"2024-01-01 00:00:00 UTC",
"2024-03-31 23:59:59 UTC",
"Joint%",
0]
*/

-- 2つめのSQL
SELECT
    "public"."User"."id",
    "public"."User"."name",
    "public"."User"."email"
FROM
    "public"."User"
WHERE
    "public"."User"."id" IN ($1)
OFFSET $2

/*
2つめのSQLのパラメータ
Params: [2, 0]
*/

includeでリレーション先のテーブルを指定したため、クエリが2件分発行されています。
検索条件はwhereに指定していたとおりのものになっていることがわかります。

このように、複雑で様々な条件をオブジェクトの形式で簡単に指定することができます。
特に、TypeScriptを使用している場合は、型の制約が入っているため、不正なプロパティや値の混入を抑止することができます。

まとめ

今回は様々な検索条件を設定する方法を確認しました。
この他にも、ソートやページネーション、集約の方法などを指定することができます。

設定できるオプションが数多くあるので混乱しがちですが、実際のSQL文をPrismaを使って表現するとどうなるのかを考えながら色々試してみることで、少しずつ理解が深まっていくかと思います。
まずは簡単なものから実際に動かしてみることをおすすめします。