findFirst query shows irrelevant data in prisma 2

Question

I want to query a post with id, slug, username, userId params. at least one of the param value are exists in the queries. Not required all of one.

const post = await prisma.post.findFirst({
    where: {
      OR: [
        {
          AND: [
            { published: true },
            {
              OR: [
                { id },
                {
                  AND: [
                    { slug },
                    {
                      author: {
                        profile: {
                          username
                        }
                      }
                    }
                  ]
                }
              ]
            }
          ]
        },
        {
          authorId: userId || undefined
        }
      ]
    },
    ...select
  })

Database data (posts):

[{id: 1, published: false}, {id: 2, published: true}]

Query param is id: 1 but output is:

{id: 2, published: true}

Is there any wrong with my query?

Prisma Post model:

model Post {
  id           String     @id @default(cuid())
  title        String
  body         String
  slug         String
  published    Boolean
  draft        Boolean    @default(false)
  author       User       @relation(fields: [authorId], references: [id])
  authorId     String
}

User model:

model User {
  id            String         @id @default(cuid())
  name          String
  email         String         @unique
  password      String?
  posts         Post[]
  profile       Profile?
}

Profile model:

model Profile {
  id                 String         @id @default(cuid())
  bio                String?
  author             User?          @relation(fields: [authorId], references: [id])
  authorId           String?
  phone              String?
  username           String?
}

Could you clarify your requirements a bit more clearly, as it's a little ambiguous? Are you saying: 1. At least one of id, slug, username, userId variables are provided during running a query (and the rest of the variables are undefined/null)? 2. All of id, slug, username, userId variables are available during the query, but only one of them needs to match with an entry in the database? In case of 2 the return value makes sense as id may not have matched but another field may have ben matching. — Tasin Ishmam, Aug 16 '21 at 06:56
`const { id, slug, username, userId } = params;` 1. At least one of id, slug, username, userId variables are provided during running a query (and the rest of the variables are undefined/null). — Ikram Ud Daula, Aug 17 '21 at 07:19

score 0 · Answer 1 · answered Aug 15 '21 at 17:05

0

Nest the query filters inside an OR clause.

const { id, slug, username, userId  } = params;

const posts = await prisma.post.findFirst({
  where: {
    OR: [
        { id },
        { slug },
        { author: { profile: { username } } },
        { authorId: userId }
    ]
  }
});

answered Aug 15 '21 at 17:05

Pasindu Dilshan

366
1
15

nope, shows wrong data as described above – Ikram Ud Daula Aug 15 '21 at 17:28

score 0 · Answer 2 · answered Aug 17 '21 at 08:48

Problem

Based on the explanation you have provided in the comments, you want to:

Query a post with id, slug, username, userId one or more of which may be null or undefined for a single query. In case a field is null/undefined, that field ought to be ignored for the particular query.

Solution

Before I go onto the solution, you need to know that null and undefined have special meaning in Prisma.

null is a value
undefined means do nothing

So basically, if you provide undefined for any field, it is effectively ignored. More info about this here

Knowing this information, you need to convert null values in your paramter arguments to undefined, as you simply want to ignore the field in that particular query. You can set up your query as follows:



async function foo(params) {
    const { id, slug, username, userId } = params;

    const post = await prisma.post.findFirst({
        where: {
            id: id ? id : undefined,
            slug: slug ? slug : undefined,
            authorId: userId ? userId : undefined,
            author: username
                ? {
                      profile: {
                          username: username,
                      },
                  }
                : undefined,
        },
    });

}

I appreciate your effort, but it does not help me anything. I think `id: id || undefined` better than `id: id ? id : undefined` AND my queries expectation is: if provide `userId` then only match `authorId: userId || undefined` If provide `username` & `slug` both or just `id` except `userId` then match `username` & `slug` both or just `id` & `published: true` for both condition — Ikram Ud Daula, Aug 17 '21 at 13:14
If you have a really complex condition set, it would probably be better to split up the queries separately. For example, create a separate query for each of the conditions you provided and run the appropriate one based on which variables are available. It's generally easier to model complex conditions like that in the application code then the ORM. I'm sure you could come up with the right combination of ORs and ANDs in Prisma as well, but it would probably be hard to read/maintain. — Tasin Ishmam, Aug 17 '21 at 13:18

findFirst query shows irrelevant data in prisma 2

2 Answers2

Problem

Solution