Subquery filtering using Prisma doesn't work on the graphql's api side

Get Help and Help Others
1

Hello,

Following is part of my schema:

model BudgetCategoryGroup {
  id        String @id @default(uuid())
  name      String
  sortOrder Int

  budgetCategories               BudgetCategory[]
  monthlyCategoryGroupActivities MonthlyCategoryGroupActivity[]
}

model BudgetCategory {
  id        String @id @default(uuid())
  name      String
  sortOrder Int

  budgetCategoryGroup   BudgetCategoryGroup @relation(fields: [budgetCategoryGroupId], references: [id])
  budgetCategoryGroupId String
}

Here is my service to query a budgetCategory and a budgetCategoryGroup.

export const testBudget: QueryResolvers['testBudget'] = async () => {
  const test = await db.budget.findMany({
    include: {
      budgetCategoryGroups: {
        where: {
          id: '6866c300-2af3-4a48-9f33-101e64e0d6cb',
        },
      },
    },
    where: {
      id: '0209a1b0-4718-4275-a073-cb29a9f8b625',
    },
  })
  console.log(test[0].budgetCategoryGroups[0])
  return test
}

And when I try to query the result using GraphQL, the result that I got from the API is different with the result I got directly from Prisma (using the console.log in the service above).
Server’s Log:

api | {
api |   id: '6866c300-2af3-4a48-9f33-101e64e0d6cb',
api |   name: 'Kids',
api |   sortOrder: 1221,
api |   budgetId: '0209a1b0-4718-4275-a073-cb29a9f8b625'
api | }

image

I am wondering whether I did something wrong or it is just a bug.

2

And this is the query result from graphql:

{
  "data": {
    "testBudget": [
      {
        "budgetCategoryGroups": [
          {
            "id": "6866c300-2af3-4a48-9f33-101e64e0d6cb",
            "name": "Kids",
            "sortOrder": 1221,
            "budgetId": "0209a1b0-4718-4275-a073-cb29a9f8b625"
          },
          {
            "id": "12af6765-f621-45c5-a53f-39403f7d8a92",
            "name": "Games",
            "sortOrder": 1417,
            "budgetId": "0209a1b0-4718-4275-a073-cb29a9f8b625"
          }
        ]
      }
    ]
  },
  "extensions": {}
}

image
image1104Ă—534 42.1 KB

3

The GraphQL API determines the shape of your request (ie inputs) and the shape of the response (the data coming back) but it doesn’t “tell Prisma” or your service how to fetch the data – and how to sort or order it … or limit or filter the results…

You need to sort that as part of your findMany. See: Filtering and Sorting (Concepts)

You want to add an orderBy and sortOrder with ascending or descending order.

If you need to sort by the category (ie, a nested relation), then see: Filtering and Sorting (Concepts).

In addition, you may need to add that sort order to your relation resolvers – that is the code that fetches the categories for its parent.

The include is happening, but your relation resolver is still fetching the way it is coded.

You can optimize to check if the root already exists and let the include eager load instead.

If you share your SDL and service I can point out where.

1 Like
4

Hi, thanks for the explanation.

Here is my relation resolver:

export const Budget: BudgetRelationResolvers = {
  user: (_obj, { root }) => {
    return db.budget.findUnique({ where: { id: root?.id } }).user()
  },
  accounts: (_obj, { root }) => {
    return db.budget.findUnique({ where: { id: root?.id } }).accounts()
  },
  budgetCategoryGroups: (_obj, { root }) => {
    return db.budget
      .findUnique({ where: { id: root?.id } })
      .budgetCategoryGroups()
  },
}

The schema, that I posted previously, was incomplete, I didn’t include some unnecessary relations (relation to user and account).

Here is my SDL:

export const schema = gql`
  type Budget {
    id: String!
    name: String!
    user: User!
    userId: String!
    accounts: [Account]!
    budgetCategoryGroups: [BudgetCategoryGroup]!
  }

  type Query {
    budgetsByUser(userId: String!): [Budget!]! @requireAuth
    budget(id: String!): Budget @requireAuth
    testBudget: [Budget!]! @requireAuth
  }

  input CreateBudgetInput {
    name: String!
    userId: String!
  }

  input UpdateBudgetInput {
    name: String
    userId: String
  }

  type Mutation {
    createBudget(input: CreateBudgetInput!): Budget! @requireAuth
    updateBudget(id: String!, input: UpdateBudgetInput!): Budget! @requireAuth
    deleteBudget(id: String!): Budget! @requireAuth
  }
`