Is there a way to optionally filter a hasOne relationship Sc Prisma #prisma-client

Is there a way to optionally filter a hasOne relat...

Yaakov

11/08/2021, 3:23 PM

Is there a way to optionally filter a hasOne relationship? Schema:

Copy code

model User {
  id    Int    @id @default(autoincrement())
  name  String
  posts Post[]
}

model Post {
  id       Int   @id @default(autoincrement())
  author   User? @relation(fields: [authorId], references: [id])
  authorId Int?
}

Example #1 Filters out all Posts with a null
authorId
if
req.query.authorName
is `undefined`:

Copy code

router.get('/posts', async (req, res, next) => {

  const posts = await prisma.post.findMany({
    where: {
      author: {
        is: {
          name: {
            in: req.query.authorName
          }
        }
      }
    },
    include: {
      author: true
    }
  });

  res.json({ data: posts });
});

Example #2 If
req.query.authorId
is
undefined
, it does not filter and returns everything:

Copy code

router.get('/posts', async (req, res, next) => {

  const posts = await prisma.post.findMany({
    where: {
      authorId: {
        in: req.query.authorId
      }
    },
    include: {
      author: true
    }
  });

  res.json({ data: posts });
});

Why is there inconsistent behavior between the 2 examples? In example #1, how can I assure that no filter is performed when

req.query.authorName

undefined

Ryan

11/09/2021, 6:00 AM

@Yaakov 👋

undefined

is interpreted as if the query is not present. The

has

checks if the condition is met and if not, returns the values based on the condition. When you pass

undefined

the condition becomes the following:

Copy code

const posts = await prisma.post.findMany({
    where: {
      author: {
        is: {},
      },
    },
    include: {
      author: true,
    },
  })

Which means that fetch all the posts that have an author. The second query for when

undefined

is passed looks like this:

Copy code

const posts = await prisma.post.findMany({
    where: {
      authorId: {},
    },
    include: {
      author: true,
    },
  })

Which has no condition so all users are fetched by default. To get the same behaviour, use the following:

Copy code

const posts = await prisma.post.findMany({
    where: {
      author: {
        is: { id: { in: authorId } },
      },
    },
    include: {
      author: true,
    },
  })

Yaakov

11/09/2021, 2:20 PM

@Ryan Your explanation is very clear and understandable. Thanks for your time! In my example #1, I don't want any filtering taking place if

req.query.authorName

undefined

. Is there a better method of accomplishing this, than the following?

Copy code

const posts = await prisma.post.findMany({
  where: {
    ...req.query.authorName && {
      author: {
        is: { name: { in: req.query.authorName } }
      }
    }
  },
  include: {
    author: true
  }
});

The above works, but ain't that pretty, especially if it has to be done for multiple filters... Thank you!

Ryan

11/11/2021, 5:53 AM

Unfortunately this is the only way for now. If you have complex filters, I would suggest creating a helper function for the filters and then passing those directly in the query after you add conditions to them.

👍 1

6 Views

Open in Slack

Previous Next