Dynamic Multi Column and Relational Filtering With Prisma ORM
Tags: prisma, typescript, javascript, backend
Published: 2023-07-24 21:36:03

If you already use Prisma, then you already know all about the awesomeness, except maybe the migration or schema part when you are working in a big team.

But anyway, my blog today will be about how we can use Prisma Query to achieve dynamic filtering!

To give you a little bit more context, what I am talking about is building one function that can apply to all your sql table, with fully customizeable : 

- Aplly Multi-Filtering a table by `any column` and `it's relation` too!
- Search by `any column` and `it's relation` too !
- Ordering by any column



## How is this possible? 

Maybe some of you guys have already kinda guess it right, it's because, prisma use JS / TS most precious thing (Object) in order to execute any query, especially the `findMany` query.

```javascript 
 
const blogs = await prisma.blogs.findMany({
    where:{
       title: "A blog"
    },
    orderBy:{
        createdAt:'desc'
    }
})

```

See how the `WHERE`clause and `ORDER BY` is basically formed by an object? 

That's what we'll experiment with as we go. 
In this part 1, we'll go with the `WHERE` clause for data filtering first, other things will hopefully covered in next parts of this series ! :D 

## Enough Talk, Lets Get Started 

### Setting Up 

First things first, let's set up our mini server with just some several dependencies 

> npm init -y 

> npm install express prisma @prisma/client 

Don't forget to initialize the prisma folders too !

> npx prisma init


Alright, now we basically have something like this in our project  

- node_modules 
- prisma 
  - schema.prisma 

If you don't want to go the hassle of setting-it-up , you can clone the repository [here](https://github.com/rizqyep/prisma-dynamic-filtering)

### Database Config and  Prisma Schema 

If you're kinda new, prisma schema also contains the database url, which actually can be overrided later on the code when you initialize prisma client instance.

All you have to do is just fill in your Database Connection URI in the .env file (generated from `npx prisma init`) and choose your provider, i'll be using `mysql` for this tutorial. But I think there'll be no difference in postgres too !

For this tutorial, we'll keep it simple, we'll just do it with two entities 

- Blogs 
- User 

and i'll just use enum for the blog category.

The schema will look like this : 


```prisma
// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "mysql"
  url      = env("DATABASE_URL")
}

enum BlogCategory {
  BACKEND
  FRONTEND
  MOBILE
}

model Blogs {
  id        String       @id @default(uuid())
  title     String
  content   String
  category  BlogCategory
  writer    User         @relation(fields: [writerId], references: [id])
  writerId  String
  createdAt DateTime     @default(now())
  updatedAt DateTime     @updatedAt
}

model User {
  id       String  @id @default(uuid())
  username String  @unique
  fullName String
  blogs    Blogs[]
}


```


And we're done with schema, now we just have to migrate it with this command : 

> npx prisma migrate dev --name migrationName



## Implementation 

### Folder Structure 

Basically, i will just split the code into some parts 

- server.js (Our Express based webserver to do request and response)

- services/blogService.js ( we'll do logics here)
- services/buildFilterQuery.js (we'll apply the filtering before passing it to the query here)
- prisma-instance.js (prisma instance will be stored here)

It'll look basically like this  :

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/lqx20izqmfw4w3dxm5ug.png)



### Initalizing Server and Prisma Instance

On this part, we'll create one route, and then inside it, we'll check whatever filters that is provided. 

server.js
```javascript

const express = require("express");
const { getAll } = require("./blogs/blogService");


const app = express();



const checkFilteringQuery = (req) => {
    const enabledFilters = {};

    if (req.query.searchKey) {
        enabledFilters.searchKey = req.query.searchKey;
    }

    if (req.query.searchValue) {
        enabledFilters.searchValue = req.query.searchValue;
    }

    if (req.query.filters) {
        // This needs to be parsed into object first, because we are retrieving this from a request query, which is basically just a string.
        enabledFilters.filters = JSON.parse(req.query.filters);
    }


    if (req.query.orderKey) {
        enabledFilters.orderKey = req.query.orderKey;
    }

    if (req.query.orderRule) {
        enabledFilters.orderRule = req.query.orderRule;
    }


    if (req.query.page) {
        enabledFilters.page = req.query.page;
    }

    if (req.query.rows) {
        enabledFilters.rows = req.query.rows;
    }

    return enabledFilters
}



app.get("/blogs", async (req, res) => {
    const enabledFilters = checkFilteringQuery(req);

    const result = await getAll(enabledFilters);


    return res.status(200).json({
        data: result
    })
})

app.listen(3000, () => {
    console.log("[INFO] READY!!!")
})
```

If you are a bit confused, i'll explain it shortly, basically in this server.js we already did : 

1. Create a simple express app

2. Create one GET routes to retrieve blogs data

3. And we'll only use combination of filters that client / user choose to pass via request. We are doing this by simply checking it inside the `checkFilteringQuery` function and then pass it to the blogService's getAll function (will be explained later).



And next up, it's just a simple task, we'll create a prisma instance in a separate file (just to get close into real-world scenario here :p)

prisma-instance.js
```
const { PrismaClient } = require("@prisma/client")

const prisma = new PrismaClient();


module.exports = {
    prisma
}
```


### Building The Query and Service 


Okay, so now, this is where the magic happens , we'll try to manipulate prisma's findMany query, to be more flexible, matching our end-user requirements of dynamic filtering. 

services/buildQuery.js

```javascript
const buildFilterQuery = (enabledFilters) => {
    const usedFilter = {
        where: {
            AND: []
        }
    };


    if (enabledFilters.filters) {

        /*
                Filters would look like this : 

                {
                    "filter1":["a","b"],
                    "filter2":["a"]
                }
            */


        // First, we'll loop through the key inside enabledFilters . filters

        // IT'S IMPORTANT THAT key of these filter object, reflect to the column in our database !
        for (const key in enabledFilters.filters) {

            // We'll store the filter values (array of value that we want to be inside where clause, to make the code more readable)
            const filterValues = enabledFilters.filters[key];



            /* In this part we'll include relational filter as well, such as user.fullName or user.username
                It's because, prisma support this, the where clause will looks like : 

                where:{
                    relation:{
                        column : "value"
                    }
                }
            
            */
            if (key.includes(".")) {
                let [relation, column] = key.split(".");

                /*If values array length is just 1 , we'll just filter for exact match 

                 Pretty much like : 
                
                    prisma.blog.findMany({
                        where:{
                            relation:{
                                column: "value"
                            }
                        }
                    })

                */

                if (filterValues.length === 1) {
                    usedFilter.where.AND.push({
                        [`${relation}`]: {
                            [`${column}`]: filterValues,
                        },
                    });
                }

                /*But if values array length is more than 1 , we'll filter with `in` operator

                 Pretty much like : 
                
                    prisma.blog.findMany({
                        where:{
                            relation:{
                                column: {
                                    in : ["value1", "value2"]
                                }
                            }
                        }
                    })

                */
                else {
                    usedFilter.where.AND.push({
                        [`${relation}`]: {
                            [`${column}`]: { in: filterValues,
                            },
                        },
                    });
                }
            }
            /* This next part, is for filtering column that is available in our table 

                where:{
                    column:"value"
                }
            
            */
            else {
                if (filterValues.length === 1) {
                    usedFilter.where.AND.push({
                        [`${key}`]: filterValues[0]
                    })
                } else {
                    usedFilter.where.AND.push({
                        [`${key}`]: { in: filterValues,
                        },
                    });
                }
            }
        }
    }



    if (enabledFilters.searchKey && enabledFilters.searchValue) {
        /*
            Same logic as filter applied here, if the searchKey include ".", then it means we are searching based on relation
        */
        if (enabledFilters.searchKey.includes(".")) {
            let [relation, column] = enabledFilters.searchKey.split(".");
            usedFilter.where.AND.push({
                [`${relation}`]: {
                    [`${column}`]: {
                        contains: `${enabledFilters.searchValue}`,
                    },
                },
            });
        } else {
            usedFilter.where.AND.push({
                [`${enabledFilters.searchKey}`]: {
                    contains: `${enabledFilters.searchValue}`,
                },
            });
        }
    }



    return usedFilter;
}
```

Right now, the code may looks a bit crazy, but for now let's just try to understand the code based on comments that I already put there, and later on, during the testing part, I'll break it down about how the filter is applied in various cases. 

Now we're done building the query, we'll call prisma's find many and from our blogService , 

services/blogService.js 

```javascript

const { prisma } = require("../prisma-instance");
const { buildFilterQuery } = require("./buildQuery");

const getAll = async(filters) => {

    const usedFilter = buildFilterQuery(filters);

    const blogs = await prisma.blogs.findMany({
        ...usedFilter,
        include: {
            writer: true
        }
    })

    return blogs
}


module.exports = {
    getAll
}
```


## Tests and Explanation 

Okay, i've promised you that the crazy looking code from some minutes ago, is going to be explained here as we test the functionality right 

> Data shown below is inserted by seeding, you can find it on repository I have listed for you back on setup section !

#### Test 1 : Filter by Blog's Category 

- Filtering by One Category 

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5t4baxizljiqtzdia91n.png)

Okay, so what is happening here is: 

1. We provide a request query of : 

`{"category":["BACKEND"]`

which our `buildFilterQuery` function translates into : 

```javascript 
where:{
    AND:[
       {category:"BACKEND"}
    ]
}
```

combine this with the full prisma query in `blogService.js`, it will actually looks like this : 


```javascript 
prisma.blogs.findMany({
   where:{
    AND:[
       {category:"BACKEND"}
    ]
   },
   include:{
     writer:true
   }
})

```

What if we want to filter blog with category of `BACKEND` and `FRONTEND`? 

Easy! we'll just pass this into the request query : 

`{"category":["BACKEND","FRONTEND"]`


and it'll translate to this : 

```javascript 
where:{
    AND:[
       {category: {in:["BACKEND","FRONTEND"]}
    ]
}
```

combine this with the full prisma query in `blogService.js`, it will actually looks like this : 


```javascript 
prisma.blogs.findMany({
   where:{
    AND:[
        {category: {in:["BACKEND","FRONTEND"]}
    ]
   },
   include:{
     writer:true
   }
})

```

Notice that if we give multiple argument to it, it will need to use the 'in' operator. 

#### Test 2 : Filtering by Category + Searching 

The Request Will Look Like this : 
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/c6rxjo2imm06tmb6xnsy.png)


And then, our `buildFilterQuery` function, will simply translate this into : 

```javascript 
where:{
    AND:[
       {category: "BACKEND"},
       {title:{
           contains : "Part 2"
         }
       }
    ]
}
```

And the full query will be : 

```javascript 
prisma.blogs.findMany({
   where:{
     AND:[
       {category: "BACKEND"},
       {title:{
           contains : "Part 2"
         }
       }
    ]
   },
   include:{
     writer:true
   }
})

```


#### Test 3 (Bonus) Filter by Category and Search By Writer's Name 

Oh yes, we can do that ! 
If you look at filter query, for filtering and searching, we have if condition that will check if the filter key or the `searchKey` contains "." inside of it, 

to use it we'll just simply do : 


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ercm77ksifwtjgq7hogy.png)


and our `buildFilterQuery` translate this into : 


```javascript 
where:{
    AND:[
       {category: "BACKEND"},
       {
         writer:{
           fullName:{
              contains : "Writer 2"
           }
         }
       }
    ]
}
```

And the full query will be : 

```javascript 
prisma.blogs.findMany({
  where:{
    AND:[
       {category: "BACKEND"},
       {
         writer:{
           fullName:{
              contains : "Writer 2"
           }
         }
       }
     ]
   },
   include:{
     writer:true
   }
})

```

So at last, we got an Object Relation Mapper that works really-well with Object Manipulaton Right ! 

With this, you can even have many more endpoints and will just have to apply this one filtering function for all different entities in your application. 

Stay tuned for the next parts where we will go through more with things like ordering, paginating (with cursor and limit-offset). 

Cheers ! 


Github Repository: [here](https://github.com/rizqyep/prisma-dynamic-filtering)

Connect With Me : 
[LinkedIn](https://linkedin.com/in/rizqyep)