Compare columns of the same table with raw queries
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.
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.
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
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
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
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()
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',
},
]