Prisma•5mo ago

What's the proper way for me to define tsvector field in my schema file?

I defined a trigger function that will update the search_data column. I'm not sure how I should define the prisma schema in this case. I'm getting weird result with filtering. For example I have these entries: Apple, Banana, Orange. When I define "data_search" @@ to_tsquery('english', ${searchTerm}) with searchTerm=Orange it returns Orange correctly. But when I'm not doing any where clause, Orange is not returned. Here's the actually model definition:

model DrawingSession {
  id          String                   @id @default(cuid())
  name        String
  description String?
  userId      String
  user        User                     @relation(fields: [userId], references: [id], onDelete: Cascade)
  data        Json
  isPublished Boolean                  @default(false)
  data_search Unsupported("tsvector")?

  createdAt DateTime @default(now())
  updatedAt DateTime @default(now()) @updatedAt
}

model DrawingSession {
  id          String                   @id @default(cuid())
  name        String
  description String?
  userId      String
  user        User                     @relation(fields: [userId], references: [id], onDelete: Cascade)
  data        Json
  isPublished Boolean                  @default(false)
  data_search Unsupported("tsvector")?

  createdAt DateTime @default(now())
  updatedAt DateTime @default(now()) @updatedAt
}

And here's my query

  const drawingsResult = await prisma.$queryRaw`
    SELECT "id", "name", "description", "data"
    FROM public."DrawingSession"
    WHERE "isPublished" = true
    AND "data_search" @@ to_tsquery('english', ${searchTerm})
    ORDER BY "id" ASC
    LIMIT ${take} OFFSET ${skip}
  `;

  const drawingsResult = await prisma.$queryRaw`
    SELECT "id", "name", "description", "data"
    FROM public."DrawingSession"
    WHERE "isPublished" = true
    AND "data_search" @@ to_tsquery('english', ${searchTerm})
    ORDER BY "id" ASC
    LIMIT ${take} OFFSET ${skip}
  `;

I have an if/else case to determine to use the above or the below without tsquery

const drawingsResult = await prisma.$queryRaw`
    SELECT "id", "name", "description", "data"
    FROM public."DrawingSession"
    WHERE "isPublished" = true
    ORDER BY "id" ASC
    LIMIT ${take} OFFSET ${skip}
  `;

const drawingsResult = await prisma.$queryRaw`
    SELECT "id", "name", "description", "data"
    FROM public."DrawingSession"
    WHERE "isPublished" = true
    ORDER BY "id" ASC
    LIMIT ${take} OFFSET ${skip}
  `;

Also take suggestion if there's better than if/else for with searchTerm vs. not. Thanks.

1 Reply

Dancing CrowOP•5mo ago

I queried the DB directory with SQL, the result is correct. So I expect is somthing in Prisma ORM doing something weird.

Gaming

Programming

What's the proper way for me to define tsvector field in my schema file?