Skip to main content
Prisma Documentation Docs

Search documentation

Type to search this documentation.

On this pageOverview

Raw SQL comparisons (Prisma ORM v7) (/docs/orm/v7/more/troubleshooting/raw-sql-comparisons)

For the complete Prisma documentation index, see llms.txt. A markdown version of any docs page is available by appending .md to its URL.

Compare columns of the same table with raw queries in Prisma ORM

Location: ORM > v7 > More > Troubleshooting > Raw SQL comparisons

Comparing different columns from the same table is a common scenario. This page shows how to achieve this using raw queries for Prisma ORM versions prior to 4.3.0.

[!WARNING] 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.

Comparing numeric values

Section titled “Comparing numeric values”

Example: retrieving posts that have more comments than likes.

prisma
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

Section titled “PostgreSQL / CockroachDB”
JavaScript
const response =
  await prisma.$queryRaw`SELECT * FROM "public"."Post" WHERE "likesCount" < "commentsCount";`;

MySQL

Section titled “MySQL”
JavaScript
const response =
  await prisma.$queryRaw`SELECT * FROM \`public\`.\`Post\` WHERE \`likesCount\` < \`commentsCount\`;`;

SQLite

Section titled “SQLite”
JavaScript
const response =
  await prisma.$queryRaw`SELECT * FROM "Post" WHERE "likesCount" < "commentsCount";`;

Comparing date values

Section titled “Comparing date values”

Example: get all projects completed after the due date.

prisma
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

Section titled “PostgreSQL / CockroachDB”
JavaScript
const response =
  await prisma.$queryRaw`SELECT * FROM "public"."Project" WHERE "completedDate" > "dueDate";`;

MySQL

Section titled “MySQL”
JavaScript
const response =
  await prisma.$queryRaw`SELECT * FROM \`public\`.\`Project\` WHERE \`completedDate\` > \`dueDate\`;`;

SQLite

Section titled “SQLite”
JavaScript
const response =
  await prisma.$queryRaw`SELECT * FROM "Project" WHERE "completedDate" > "dueDate";`;
Section titled “Related pages”
  • Bundler issues: Solve ENOENT package error with vercel/pkg and other bundlers
  • Check constraints: Learn how to configure CHECK constraints for data validation with Prisma ORM and PostgreSQL
  • GraphQL autocompletion: Get autocompletion for Prisma Client queries in GraphQL resolvers with plain JavaScript
  • Many-to-many relations: Learn how to model, query, and convert many-to-many relations with Prisma ORM
  • Next.js: Best practices and troubleshooting for using Prisma ORM with Next.js applications
Suggest an edit

Propose a replacement for this page. The site team reviews it before applying any changes.

Export
Documentation menu