Code Monkey home page Code Monkey logo

Comments (2)

sdgluck avatar sdgluck commented on May 25, 2024 1

Thanks @jkomyno for your detailed response. Looks like I was thrown by the generated SQL and the fact that Prisma seems to do some optimisations based on the search term. I can't produce an example that produces results different than expected, so I will close the issue!

from prisma.

jkomyno avatar jkomyno commented on May 25, 2024

Hi @sdgluck, I've tried reproducing this issue with [email protected].

There is no OR condition in the SQL searching both columns independently, as declared in the query object

  • By the results I'm looking at, the OR filter is applied: I'm getting back user entries whose firstname or lastname contain at least one of the terms in ['jon', 'doe', 'foo', 'bar'].

The order of the concatenation is the reverse of the order of the columns in the query object

  • True, but doesn't seem to affect the results?

I think only the first search term is being used in the query ($1), unless Prisma is doing something under-the-hood to merge them in some way?

  • The provided term (containing the | symbol) is used as is

Can you please provide an example where the output provided by the Prisma-generated query is different than what you'd expect?

Prisma Schema

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

datasource db {
  provider = "postgres"
  url      = env("TEST_POSTGRES_URI")
}

model User {
  id        String @id @default(cuid()) @db.VarChar(30)
  firstName String
  lastName  String
}

TypeScript Snippet

Note: this snippet uses deleteMany to clear the user table, please don't run it in production.

import { PrismaClient } from '@prisma/client'

async function main() {
  const prisma = new PrismaClient({
    log: [
      {
        emit: 'event',
        level: 'query',
      },
    ]
  })
  
  await prisma.user.deleteMany({ })

  await prisma.user.createMany({
    data: [
      {
        firstName: 'Mary',
        lastName: '---',
      },
      {
        firstName: 'Jon',
        lastName: '---'
      },
      {
        firstName: 'Jon',
        lastName: 'Doe',
      },
      {
        firstName: 'Food',
        lastName: 'Bars',
      },
      {
        firstName: '---',
        lastName: 'Bar',
      },
    ]
  })

  prisma.$on('query', ({ query, params }) => {
    console.log('[query]', query)
    console.log('[params]', params)
  })

  const terms = ['jon', 'doe', 'foo', 'bar']

  const users = await prisma.user.findMany({
    where: {
      OR: [
        {
          firstName: {
            search: terms.join(' | '),
          },
        },
        {
          lastName: {
            search: terms.join(' | '),
          },
        },
      ],
    },
  })

  console.log('[users]')
  console.log(users)

  const usersRaw = await prisma.$queryRaw`
    SELECT "public"."User"."id", "public"."User"."firstName", "public"."User"."lastName"
    FROM "public"."User"
    WHERE to_tsvector("public"."User"."firstName") @@ to_tsquery(${terms.join(' | ')})
      OR to_tsvector("public"."User"."lastName") @@ to_tsquery(${terms.join(' | ')})
    ORDER BY "public"."User"."id" ASC;
  `

  console.log('[usersRaw]')
  console.log(usersRaw)
}

void main().catch((e) => {
  console.log(e.message)
  process.exit(1)
})

Stdout

[query] SELECT "public"."User"."id", "public"."User"."firstName", "public"."User"."lastName" FROM "public"."User" WHERE to_tsvector(concat_ws(' ', "public"."User"."lastName","public"."User"."firstName")) @@ to_tsquery($1) OFFSET $2
[params] ["jon | doe | foo | bar",0]
[users]
[
  {
    id: 'clsitcr060001h88w89vdg60w',
    firstName: 'Jon',
    lastName: '---'
  },
  {
    id: 'clsitcr060002h88wiswmk9qn',
    firstName: 'Jon',
    lastName: 'Doe'
  },
  {
    id: 'clsitcr060003h88wykrp0vyu',
    firstName: 'Food',
    lastName: 'Bars'
  },
  {
    id: 'clsitcr060004h88w39b7p0jk',
    firstName: '---',
    lastName: 'Bar'
  }
]
[query] 
    SELECT "public"."User"."id", "public"."User"."firstName", "public"."User"."lastName"
    FROM "public"."User"
    WHERE to_tsvector("public"."User"."firstName") @@ to_tsquery($1)
      OR to_tsvector("public"."User"."lastName") @@ to_tsquery($2)
    ORDER BY "public"."User"."id" ASC;
  
[params] ["jon | doe | foo | bar","jon | doe | foo | bar"]
[usersRaw]
[
  {
    id: 'clsitcr060001h88w89vdg60w',
    firstName: 'Jon',
    lastName: '---'
  },
  {
    id: 'clsitcr060002h88wiswmk9qn',
    firstName: 'Jon',
    lastName: 'Doe'
  },
  {
    id: 'clsitcr060003h88wykrp0vyu',
    firstName: 'Food',
    lastName: 'Bars'
  },
  {
    id: 'clsitcr060004h88w39b7p0jk',
    firstName: '---',
    lastName: 'Bar'
  }
]

from prisma.

Related Issues (20)

Recommend Projects

  • React photo React

    A declarative, efficient, and flexible JavaScript library for building user interfaces.

  • Vue.js photo Vue.js

    🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.

  • Typescript photo Typescript

    TypeScript is a superset of JavaScript that compiles to clean JavaScript output.

  • TensorFlow photo TensorFlow

    An Open Source Machine Learning Framework for Everyone

  • Django photo Django

    The Web framework for perfectionists with deadlines.

  • D3 photo D3

    Bring data to life with SVG, Canvas and HTML. 📊📈🎉

Recommend Topics

  • javascript

    JavaScript (JS) is a lightweight interpreted programming language with first-class functions.

  • web

    Some thing interesting about web. New door for the world.

  • server

    A server is a program made to process requests and deliver data to clients.

  • Machine learning

    Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.

  • Game

    Some thing interesting about game, make everyone happy.

Recommend Org

  • Facebook photo Facebook

    We are working to build community through open source technology. NB: members must have two-factor auth.

  • Microsoft photo Microsoft

    Open source projects and samples from Microsoft.

  • Google photo Google

    Google ❤️ Open Source for everyone.

  • D3 photo D3

    Data-Driven Documents codes.