P
Prisma•12mo ago
smykes24

Programatic where clause based off query parameters.

I'm using fastify although I doubt that matters. Attached is pseudoish code. I am looking for a way to programmatically add to the where clause based off query parameters. If there are no query parameters, just do a select * from {table} I have searched the documentation pretty well, and searched blog entries etc, couldn't find an answer: Here is my code with comments in the broken where https://gist.github.com/smykes/deeffc91002340057e107803aa9e58f8 Thank you in advance.
Gist
programatic where clause?
programatic where clause? GitHub Gist: instantly share code, notes, and snippets.
6 Replies
Nurul
Nurul•12mo ago
Hello @smykes 👋 To programmatically add conditions to the where clause based on query parameters, you can create a dynamic where object. Can you try something like this? 
app.get<{
  Querystring: IBookQueryString;
}>("/api/books", async (req, res) => {
  const { month, sort, rating, year } = req?.query;
  
  const where: Prisma.BookWhereInput = {};

  if (year) {
    where.year_read = parseInt(year, 10);
  }

  if (month) {
    where.month_read = parseInt(month, 10);
  }

  if (rating) {
    where.rating = parseInt(rating, 10);
  }

  const books = await prisma.book.findMany({
    orderBy: {
      date_read: "desc",
    },
    where,
    select: {
      author: true,
      title: true,
    },
  });
  return books;
});
app.get<{
  Querystring: IBookQueryString;
}>("/api/books", async (req, res) => {
  const { month, sort, rating, year } = req?.query;
  
  const where: Prisma.BookWhereInput = {};

  if (year) {
    where.year_read = parseInt(year, 10);
  }

  if (month) {
    where.month_read = parseInt(month, 10);
  }

  if (rating) {
    where.rating = parseInt(rating, 10);
  }

  const books = await prisma.book.findMany({
    orderBy: {
      date_read: "desc",
    },
    where,
    select: {
      author: true,
      title: true,
    },
  });
  return books;
});
smykes24
smykes24OP•12mo ago
That worked, you are a gentle person and a scholar. I forgot how to mark this complete Would order_by be similar?
smykes24
smykes24OP•12mo ago
This is the issue I am running into now Trying to get "desc" to be the default, but as you can see the if is not happy. If I change that to a string then it is happy.
No description
Nurul
Nurul•12mo ago
What is the value of sort? Have you manually defined it to 'desc'?
smykes24
smykes24OP•11mo ago
sort in query paramters are supposed to be "asc" or "desc" or not there A little frustrating @Nurul (Prisma) how do I handle this? Am I using this right? It's a fairly straight forward sort, I just have been banging my head
Nurul
Nurul•11mo ago
Does something like this work? 
function getBooks(sort: 'asc' | 'desc') {
  const orderBy: Prisma.BookOrderByWithRelationInput = {};
  if (sort) {
    orderBy.date_read = sort; 
  } else {
    orderBy.date_read = 'desc'; 
  }
  // ...
}
function getBooks(sort: 'asc' | 'desc') {
  const orderBy: Prisma.BookOrderByWithRelationInput = {};
  if (sort) {
    orderBy.date_read = sort; 
  } else {
    orderBy.date_read = 'desc'; 
  }
  // ...
}

Did you find this page helpful?