I'm trying to filter papers dynamically, either using title or using publication_date or department. Or either just a single query. For example this my URL, so it means I must filter name with test that starts from 1999 to 2020.

http://localhost:3000/api/filter-papers?title=test&yearStart=1999&yearEnd=2020&department=CAHS

this is my api.

export async function GET(req: Request, res: NextResponse) {
    try {
        const { searchParams } = new URL(req.url)
        const searchTitle = searchParams.get('title')
        const yearStart = searchParams.get('year_start')
        const yearEnd = searchParams.get('year_end')
        const department = searchParams.get('department')


        const papers = await db.publish_Papers.findMany({
            where: {
                OR: {
                    title: {
                        contains: searchTitle ?? ""
                    },
                    publication_date: {
                        gte: yearStart,
                        lte: yearEnd
                    },
                    department: department
                   
                }
            }
        })

        console.log(papers)

        return NextResponse.json(papers, { status: 200 })
    } catch (error) {
        return NextResponse.json({ error: 'failed to load data' }, { status: 500 })
    }
}

This is my Schema.

model Publish_Papers {
  id                 String   @id @default(uuid())
  title              String
  description        String
  categories         String
  publication_source String
  publication_date   DateTime
  url                String
  createdAt          DateTime @default(now())
  updatedAt          DateTime @updatedAt

  authorId String
  Authors  Authors @relation(fields: [authorId], references: [id])
}

so when I only run findMany without any filter. I get this JSON

data.json

[
 {
        "id": "4b20caba-65cd-4db6-b1e2-ecfe247b7dc4",
        "title": "publish test",
        "description": "http://localhost:3000/admin-resource/new",
        "categories": "CASE",
        "publication_source": "idk",
        "publication_date": "2016-03-10T01:37:21.562Z",
        "url": "http://localhost:3000/admin-resource/new",
        "createdAt": "2025-03-18T01:37:35.054Z",
        "updatedAt": "2025-03-18T01:37:35.054Z",
        "authorId": "d7b32e45-b25a-41b9-bc7b-178991409af0",
    },
  
]

I'm trying to filter papers dynamically, either using title or using publication_date or department. Or either just a single query. For example this my URL, so it means I must filter name with test that starts from 1999 to 2020.

http://localhost:3000/api/filter-papers?title=test&yearStart=1999&yearEnd=2020&department=CAHS

this is my api.

export async function GET(req: Request, res: NextResponse) {
    try {
        const { searchParams } = new URL(req.url)
        const searchTitle = searchParams.get('title')
        const yearStart = searchParams.get('year_start')
        const yearEnd = searchParams.get('year_end')
        const department = searchParams.get('department')


        const papers = await db.publish_Papers.findMany({
            where: {
                OR: {
                    title: {
                        contains: searchTitle ?? ""
                    },
                    publication_date: {
                        gte: yearStart,
                        lte: yearEnd
                    },
                    department: department
                   
                }
            }
        })

        console.log(papers)

        return NextResponse.json(papers, { status: 200 })
    } catch (error) {
        return NextResponse.json({ error: 'failed to load data' }, { status: 500 })
    }
}

This is my Schema.

model Publish_Papers {
  id                 String   @id @default(uuid())
  title              String
  description        String
  categories         String
  publication_source String
  publication_date   DateTime
  url                String
  createdAt          DateTime @default(now())
  updatedAt          DateTime @updatedAt

  authorId String
  Authors  Authors @relation(fields: [authorId], references: [id])
}

so when I only run findMany without any filter. I get this JSON

data.json

[
 {
        "id": "4b20caba-65cd-4db6-b1e2-ecfe247b7dc4",
        "title": "publish test",
        "description": "http://localhost:3000/admin-resource/new",
        "categories": "CASE",
        "publication_source": "idk",
        "publication_date": "2016-03-10T01:37:21.562Z",
        "url": "http://localhost:3000/admin-resource/new",
        "createdAt": "2025-03-18T01:37:35.054Z",
        "updatedAt": "2025-03-18T01:37:35.054Z",
        "authorId": "d7b32e45-b25a-41b9-bc7b-178991409af0",
    },
  
]

• javascript
• json
• next.js
• prisma

1 Answer 1

I checked a few things at your structured are incorrect- your problem is using OR wrong.

That's not how OR works in Prisma. Also, your URL has "yearStart" and "yearEnd" but your code looks for "year_start" and "year_end". Its case-sensitive. Try this approach instead:

// ADD THIS AFTER FIRST TRY
            const { searchParams } = new URL(req.url)
            const searchTitle = searchParams.get('title')
            const yearStart = searchParams.get('yearStart')
            const yearEnd = searchParams.get('yearEnd')
            const department = searchParams.get('department')
    
            // Make an empty object for our conditions
            const whereClause: any = {}
            
            // Add something to it if the params exist
            if (searchTitle) {
                whereClause.title = {
                    contains: searchTitle,
                    mode: 'insensitive'
                }
            }
            
            if (yearStart || yearEnd) {
                whereClause.publication_date = {}
                
                if (yearStart) {
                    whereClause.publication_date.gte = new Date(`${yearStart}-01-01T00:00:00Z`)
                }
                
                if (yearEnd) {
                    whereClause.publication_date.lte = new Date(`${yearEnd}-12-31T23:59:59Z`)
                }
            }
            
            if (department) {
                whereClause.categories = department
            }
    
            const papers = await db.publish_Papers.findMany({
                where: whereClause
            })
    
            // then just return with NextResponse.json(papers, { status: 200 })
        // catch (error) like your already doing

This way it only adds filters for params that actually exist in the URL. I suggest that you try implementing this approach and see if it works.