同じテーブルの列を生クエリで比較

同じテーブルの異なる列を比較することは、開発者が遭遇する一般的なシナリオです。例としては、同じテーブル内の2つの数値を比較したり、同じテーブル内の2つの日付を比較したりすることが挙げられます。これについては、既存のGitHub Issueがあります。

warning

バージョン4.3.0以降では、同じテーブル内の列を比較するために生クエリを使用する必要はありません。<model>.fieldsプロパティを使用して列を比較することができます。

以下の情報は、4.3.0より前のPrisma ORMバージョンとの下位互換性のために保持されています。

回避策

同じテーブル内の2つの列の値を比較するには、生クエリを使用することで実現できます。

数値の比較

info

バージョン4.3.0以降では、同じテーブル内の列を比較するために生クエリを使用する必要はありません。<model>.fieldsプロパティを使用して列を比較できます。詳細はこちら

異なる列の値を比較するユースケースの1つは、いいね数よりもコメント数が多い投稿を取得することです。この場合、commentsCountとlikesCountの値を比較する必要があります。

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

クエリ（データベースによって異なる場合があります）は、次のようになります。

PostgreSQL / CockroachDB

import { PrismaClient } from '@prisma/client'

const prisma = new PrismaClient()

async function initiateNumbersComparisonRawQuery() {
  const response =
    await prisma.$queryRaw`SELECT * FROM "public"."Post" WHERE "likesCount" < "commentsCount";`

  console.log(response)
}

await initiateNumbersComparisonRawQuery()

MySQL

import { PrismaClient } from '@prisma/client'

const prisma = new PrismaClient()

async function initiateNumbersComparisonRawQuery() {
  const response =
    await prisma.$queryRaw`SELECT * FROM \`public\`.\`Post\` WHERE \`likesCount\` < \`commentsCount\`;`

  console.log(response)
}

await initiateNumbersComparisonRawQuery()

Sqlite

import { PrismaClient } from '@prisma/client'

const prisma = new PrismaClient()

async function initiateNumbersComparisonRawQuery() {
  const response =
    await prisma.$queryRaw`SELECT * FROM "Post" WHERE "likesCount" < "commentsCount";`

  console.log(response)
}

await initiateNumbersComparisonRawQuery()

上記のクエリを実行すると（データベースによって異なります）、コメントと比較していいね数が少ない投稿がフィルタリングされます。

クエリのレスポンス

;[
  {
    id: 1,
    createdAt: '2022-03-03T12:08:11.421+00:00',
    updatedAt: '2022-03-03T12:08:11.422+00:00',
    title: 'Hello World',
    content: 'This is my first post',
    published: false,
    authorId: 1,
    likesCount: 50,
    commentsCount: 100,
  },
]

日付の比較

info

同様に、日付を比較する必要がある場合も、生クエリを使用して同じことを実現できます。

たとえば、ユースケースとして、期日後に完了したすべてのプロジェクトを取得することが考えられます。

model Project {
  id            Int      @id @default(autoincrement())
  title         String
  author        User     @relation(fields: [authorId], references: [id])
  authorId      Int
  dueDate       DateTime
  completedDate DateTime
  createdAt     DateTime @default(now())
}

クエリ（データベースによって異なる場合があります）は、次のようになります。

PostgreSQL / CockroachDB

import { PrismaClient } from '@prisma/client'

const prisma = new PrismaClient()

async function initiateDatesComparisonRawQuery() {
  const response =
    await prisma.$queryRaw`SELECT * FROM "public"."Project" WHERE "completedDate" > "dueDate";`

  console.log(response)
}

await initiateDatesComparisonRawQuery()

MySQL

import { PrismaClient } from '@prisma/client'

const prisma = new PrismaClient()

async function initiateDatesComparisonRawQuery() {
  const response =
    await prisma.$queryRaw`SELECT * FROM \`public\`.\`Project\` WHERE \`completedDate\` > \`dueDate\`;`

  console.log(response)
}

await initiateDatesComparisonRawQuery()

Sqlite

import { PrismaClient } from '@prisma/client'

const prisma = new PrismaClient()

async function initiateDatesComparisonRawQuery() {
  const response =
    await prisma.$queryRaw`SELECT * FROM "Project" WHERE "completedDate" > "dueDate";`

  console.log(response)
}

await initiateDatesComparisonRawQuery()

上記のクエリを実行すると、completedDateがdueDateより後のプロジェクトがフェッチされます。

クエリのレスポンス

;[
  {
    id: 1,
    title: 'Project 1',
    authorId: 1,
    dueDate: '2022-03-10T00:00:00+00:00',
    completedDate: '2022-03-12T00:00:00+00:00',
    createdAt: '2022-03-03T12:08:11.421+00:00',
  },
]

回避策​

数値の比較​

日付の比較​

回避策

数値の比較

日付の比較