Comments (2)
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.
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 backuser
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)
- VSCode Autocomplete Not Functioning When Extending Prisma Schema HOT 2
- Prisma Migrate Drops Custom Migrations HOT 5
- Types broken on void clients (no models) HOT 2
- RustPanic: RuntimeError HOT 1
- internal error: entered unreachable code HOT 1
- I'm using using prisma in auth.config.ts and Its giving vercel edge errors
- When using `pnpm` and deploying Prisma Client to Heroku, it fails at runtime with `Cannot find module '.prisma/client'` HOT 2
- Install fail! Error: [[email protected] › @prisma/[email protected]] Can't find package @prisma/engines's version: 5.14.0
- Error seeding DB via Prisma command, possible invocation error
- Bug: Migrations not compatible with D1 HOT 2
- Query optimization is not compatible with PostgreSQL citext fields HOT 2
- PrismaClientInitializationError HOT 3
- resetting database data
- I want to use FK as id(as a composite key) HOT 3
- Add model inheritance HOT 3
- Remove validate call after format in CLI
- called `Result::unwrap()` on an `Err` value: Any { .. } HOT 1
- Split out the "fix" functionality of `prisma format`
- internal error: entered unreachable code HOT 1
- Migration is created for the `@default(dbgenerated(...))` value on `DateTime` although already migrated HOT 1
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
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.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from prisma.