Get data from secondary table in Prisma2

Question

I have a User table that contains user data. I also have a Relationship table that contains a parent_id and a child_id as well as the incrementing id. Each Parent can have many Children and I am trying to retrieve the array of children using the parent_id. I am currently getting an array, but the array only contains the Relationship table data.

How do I get the data from the User table? I was under the impression that because there is a relationship the following should work, but it doesnt.

query {
  getChildren {
    user{
      id
      name
      email      
    }
  }
}

The relevant code is as follows:
Query:

export const getChildren = queryField('getChildren', {
  type: 'User',
  list: true,
  resolve: async (_parent, {}, ctx) => {
    const userId = getUserId(ctx);
    if (!userId) {
      // TODO -think I might need to throw an error here
      return;
    }
    const children = await ctx.prisma.relationship.findMany({
      where: {
        parent_id: userId,
      },
    });
    return children;
  },
});

schema.prisma:

model Relationship {
  child_id            Int
  id                  Int  @default(autoincrement()) @id
  parent_id           Int
  child               User @relation("Relationship_child_idToUser", fields: [child_id], references: [id])
  parent              User @relation("Relationship_parent_idToUser", fields: [parent_id], references: [id])
}

model User {
  created_at          DateTime       @default(now())
  email               String?        @unique
  id                  Int            @default(autoincrement()) @id
  ischild             Boolean        @default(false)
  name                String?
  password            String
  children            Relationship[] @relation("Relationship_child_idToUser")
  parents             Relationship[] @relation("Relationship_parent_idToUser")
}

score 1 · Answer 1 · answered May 26 '20 at 10:19

1

The query below should do it

  prisma.relationship.findMany({
    where: {
      parent_id: user_id,
    },
    include: {
      child: true,
    },
  })

You need to specify the include param explicitly to include the relationship in the output.

answered May 26 '20 at 10:19

Ryan

5,229
1
20
31

Im still getting the same result when I add the include. No problems from the linter though, so we are on the right track – Tristan May 26 '20 at 10:49
I'm getting the data mate. This is the query used: `const data = await prisma.relationship.findMany({ where: { parent_id: 1, }, include: { child: true, }, })` And this is the response: `[ { child_id: 2, id: 1, parent_id: 1, child: { created_at: 2020-05-26T15:33:13.171Z, email: null, id: 2, ischild: true, name: 'child', password: 'child' } } ]` Have you added the relations correctly? – Ryan May 26 '20 at 15:35
This is the query Im using `query { getChildren { id name } }` is that similar to yours? – Tristan May 27 '20 at 08:19
Ive checked the DB and the tables are all linked correctly – Tristan May 27 '20 at 08:20
I have just checked and the prisma 'findMany` query is returning the correct values, but the values are not showing in the Playground. Any ideas why that might be? – Tristan May 27 '20 at 08:26
The query should be `query { getChildren { child { name } } }` and the nexus query should be `export const getChildren = queryField('getChildren', { list: true, type: 'Relationship', resolve: (_root, _args, ctx) => { return ctx.prisma.relationship.findMany({ where: { parent_id: 1, }, include: { child: true, }, }) }, })` – Ryan May 27 '20 at 12:41

Get data from secondary table in Prisma2

1 Answers1