Hi there, I'm facing a challenge while developing an application using NestJS/Prisma.
The task at hand is to extract unique results from a table in order to display them in a filter on the front-end.
Let me give you an overview of my table structure. It's called People, and it holds various details about individuals like their name, country, preferred language, profile type, department, etc.
My goal is to perform a database query that fetches distinct values from specific fields.
Currently, I am achieving this with the following query:
const contentFromDatabase = await this.prisma.people.groupBy({
by: ['profileType', 'preferredLanguage', 'department'],
});
This query returns an array similar to the one shown below:
[
{
"profileType": "professor",
"preferredLanguage": "en",
"department": "Automotive"
},
...
]
However, what I aim for is a structured output as follows:
{
"profileType": [
"business", "professor", "student"
],
"preferredLanguage": [
"en", "fr"
],
"department": [
"Automotive", "Books", "Garden", and more...
]
}
Since accomplishing this solely through database queries seems impractical, I attempted to create a custom function but faced difficulties.
Key Points to Consider:
- I currently use single arrays but might transition to associative arrays in the future.
- In the example above, I group by 3 fields, but this may expand dynamically based on requirements.
- The purpose of this function is to populate filters with existing data within the database, catering to client preferences.
Your insights and assistance are greatly appreciated!