How to compare columns from the same table in Prisma

Hi everyone, In this post, I am explaining how to compare columns from the same table.

we can compare the columns from the same table directly while using only non-unique filters

In the following steps, we must use raw queries:

• version earlier than 4.3.0
• unique filters, such as findUnique or findUniqueOrThrow
• compare a field with a unique constraint

To enable column comparisons in the same table, add the fieldReference feature flag to the generator block of your Prisma Schema as follows:

// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["fieldReference"]
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model User {
  id                           Int       @id @unique @default(autoincrement())
  first_name                   String    @db.VarChar(100)
  last_name                    String    @db.VarChar(100)
  email                        String    @db.VarChar(100)
  role                         Role      @default(VENDOR)
  User                         User?     @relation(name: "user", fields: [created_by], references: [id])
  created_by                   Int?      @db.Integer()
  created_at                   DateTime  @default(now())
  updated_at                   DateTime  @updatedAt
  deleted_at                   DateTime? @default(null)
  user                         User[]    @relation("user")

  @@map("users")
}

To compare columns in the same table, use the <model>.fields property. In the following example, the query returns all records where the value in the prisma.user.first_name field is equal to the prisma.user.last_name field.

import { PrismaClient } from '@prisma/client';
const prisma= new PrismaClient();

const userData= await prisma.user.findMany({
      where: {
        first_name: { equals: prisma.user.fields.last_name }
      })

fields is a special property of every model. It contains the list of that model’s fields.

Note:

• we could only compare fields of the same type. For example, the following causes an error:

import { PrismaClient } from '@prisma/client';
const prisma= new PrismaClient();

const userData= await prisma.user.findMany({
      where: {
        first_name: { equals: prisma.user.fields.created_by } //Type "String" is not assignable to type "Int"
      }
})

• If you use the groupBy model query with the having option, then you must put our referenced fields in the by argument.

import { PrismaClient } from '@prisma/client';
const prisma= new PrismaClient();

const userData= await prisma.user.groupBy({
      by:[ 'id', 'first_name' ],
      having: {
        id: { equals: prisma.user.fields.first_name } 
      }
})

• The following example does not work, because name is not in the by argument:

import { PrismaClient } from '@prisma/client';
const prisma= new PrismaClient();

const userData= await prisma.user.groupBy({
      by:[ 'id' ],
      having: {
        id: { equals: prisma.user.fields.first_name } 
      }
})

Conclusion

I hope, In this post, we have learned about comparing columns from the same table, Thank you for reading this post, See you soon.