How do I filter in an array in a related model using Prisma

[hrh]

Apologies for the noob question, I would like to filter based on the roles values equal to “Candidate” in my User model, how do I go about it?

User Model

model User {
  id        Int       @id @default(autoincrement())
  email     String    @unique
  password  String
  name      String?
  roles     Role[]    @default([Candidate])
  createdAt DateTime  @default(now())
  updatedAt DateTime  @updatedAt
  profile   Profile?
  employer  Employer?
}

Profile Model

model Profile {
  id             Int           @id @default(autoincrement())
  userId         Int           @unique
  user           User          @relation(fields: [userId], references: [id])
  isOpentoWork   Boolean       @default(true)
  resumeUrl      String?
  coverLetterUrl String?
  skills         Skill[]       @relation("ProfileToSkill")
  createdAt      DateTime      @default(now())
  updatedAt      DateTime      @updatedAt
  applications   Application[]
  savedJobs      SavedJob[]
  pastSearches   PastSearch[]
}

const getAllCandidates = async (req, res) => {
  try {
    const profiles = await prisma.Profile.findMany({
      where: {
        isOpentoWork: true,
      },
      include: {
        user: true,
      },
      AND:[
        {
          roles:{
            contains: 'Candidate'
          }
        }
      ]
    });

    if (profiles.length === 0) {
      return res.status(200).json({ message: "No candidates found" });
    }

    res.status(200).json(profiles);
  } catch (e) {
    console.error(e);
    res
      .status(500)
      .json({ error: "An error occurred while fetching candidates" });
  }
};```

[MartinLoera]

HI! When you want to filter by roles, you need to add the condition inside the ‘where’ clause, but the ‘where’ clause of the ‘user’ not the ‘profile’.
However, the roles field in the User model is defined as an array of Role, and Prisma does not support filtering by array fields directly, especially when using the enum type.

The are a few solutions:

• Change the database schemma: Instead of using an array field you could change your db schema to use a junction table to represent the many to many relationship between users and roles, this would allow you to query the relationship table directly papu.
• Filter in memory fetching the data: You could fetch all the profiles where ‘isOpentoWork’ is true and then filter in memory yto keep only the profiles where the user’s roles include ‘Candidate’

const getAllCandidates = async (req, res) => {
  try {
    const profiles = await prisma.Profile.findMany({
      where: {
        isOpentoWork: true,
      },
      include: {
        user: true,
      },
    });

    const candidateProfiles = profiles.filter(profile => profile.user.roles.includes('Candidate'));

    if (candidateProfiles.length === 0) {
      return res.status(200).json({ message: "No candidates found" });
    }

    res.status(200).json(candidateProfiles);
  } catch (e) {
    console.error(e);
    res
      .status(500)
      .json({ error: "An error occurred while fetching candidates" });
  }
};

Remember the first solution is the best in long term because it leveranges the db for what it does best " querying data", I hope to help u I’m not a native English speaker so sorry for my writing skills

[hrh]

Hi @MartinLoera , appreciate reverting the question. No worries on the writing skills, I am able to understand what you are writing.

I have the same thought as you regarding the second solution. I am going to give the first solution a try and see how it goes. Thanks anyway!