将同一表的列与原始查询进行比较

比较同一个表中的不同列是开发者遇到的常见情况。一些示例包括比较同一个表中的两个数值或比较同一个表中的两个日期。现有的 GitHub 问题也与此相关。

¥Comparing different columns from the same table is a common scenario that developers encounter. Some examples include comparing two numeric values in the same table or comparing two dates in a same table. There's an existing GitHub Issue regarding the same.

警告

从版本 4.3.0 开始，你不需要使用原始查询来比较同一表中的列。你可以将 <model>.fields 属性用于比较列。

¥From version 4.3.0, you do not need to use raw queries to compare columns in the same table. You can use the <model>.fields property to compare the columns.

以下信息保留以便与 4.3.0 之前的 Prisma ORM 版本向后兼容。

¥The below information is kept for backwards compatibility with Prisma ORM versions prior to 4.3.0.

解决方法

¥Workaround

可以使用原始查询来比较同一个表中两列的值。

¥Comparing values from two columns in the same table can be achieved by using raw queries.

比较数值

¥Comparing numeric values

info

从版本 4.3.0 开始，你不需要使用原始查询来比较同一表中的列。你可以使用 <model>.fields 属性来比较列。了解更多

¥From version 4.3.0, you do not need to use raw queries to compare columns in the same table. You can use the <model>.fields property to compare the columns. Learn more

比较不同列中的值的一种用例是检索评论多于点赞的帖子；在这种情况下，你需要比较 commentsCount 和 likesCount 的值。

¥One use case for comparing values from different columns would be retrieving posts that have more comments than likes; in this case, you need to compare the values of commentsCount and 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
}

查询（取决于哪个数据库）可能类似于：

¥Queries (depending upon which database) could look something like:

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()

运行上述查询（取决于数据库）将过滤与评论相比点赞数较少的帖子。

¥Running the above queries (depending upon the database) would filter posts that has fewer likes compared to comments.

查询响应

¥Query Response

;[
  {
    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,
  },
]

比较日期值

¥Comparing date values

info

从版本 4.3.0 开始，你不需要使用原始查询来比较同一表中的列。你可以使用 <model>.fields 属性来比较列。了解更多

¥From version 4.3.0, you do not need to use raw queries to compare columns in the same table. You can use the <model>.fields property to compare the columns. Learn more

同样，如果你需要比较日期，你也可以使用原始查询来实现相同的目的。

¥Similarly, if you need to compare dates, you could also achieve the same thing using raw queries.

例如，一个用例可能是在截止日期后完成所有项目。

¥For example, a use case could be to get all projects completed after the due date.

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())
}

查询（取决于数据库）可能类似于：

¥Queries (depending upon the database) could look something like:

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 之后的项目。

¥Running the above query would fetch projects where completedDate is after the dueDate.

查询响应

¥Query Response

;[
  {
    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',
  },
]

解决方法​

比较数值​

比较日期值​

解决方法

比较数值

比较日期值