About many to many query, Help !!!

[joisun]

I'm doing a blog system, And I'm trying to query a article post with tags on it , now I get only the tagid , but no name field. here are some related infomation:
** How I tried**

@Injectable()
export class BlogPostsService {
  constructor(private readonly prisma: PrismaService) {}
  async findMany(userid: string) {
    const posts = await this.prisma.article.findMany({
      where: {
        authorId: userid,
        visible: 1,
        status: 1, // 发布
      },
      include: {
        tags: {
          select: {
            tagid: true,
            name: true,
          },
        },
        category: true,
        column: true,
      },
      orderBy: {
        createdAt: 'desc',
      },
      take: 20,
    });
    return posts;
  }
}

and also below trying , with the same result:

@Injectable()
export class BlogPostsService {
  constructor(private readonly prisma: PrismaService) {}
  async findMany(userid: string) {
    const posts = await this.prisma.article.findMany({
      where: {
        authorId: userid,
        visible: 1,
        status: 1, // 发布
      },
      include: {
        tags: {
          include: {
            tag: true,
          },
        },
        category: true,
        column: true,
      },
      orderBy: {
        createdAt: 'desc',
      },
      take: 20,
    });
    return posts;
  }
}

** The actual problem**

** Related prisma.schema part:**

model Article {
  artid    String @id @default(uuid())
  sort     Int    @default(0)
  author   User   @relation(fields: [authorId], references: [userid])
  authorId String @map("author_id")

  title       String
  description String?
  content     String  @db.LongText

  keywords String @default("")
  status   Int    @default(0) //0: 草稿 1:发布 3：删除

  cover    String    @default("")
  visible  Int       @default(1) // 0: 不可见 1:可见
  password String? // 加密文章
  comments Comment[]

  allowComment Int       @default(1) @map("allow_comment") // 1: 允许评论 0: 禁止评论
  liking       Int       @default(0)
  readers      Int       @default(0)
  banner       Int       @default(0) //0: 不在 banner 区域展示， 1 banner 区域的顺序
  category     Category? @relation(fields: [catid], references: [catid]) // 分类关联
  catid        String?
  column       Column?   @relation(fields: [colid], references: [colid]) // 专栏关联
  colid        String?

  tags TagsOnArticles[] // 标签关联

  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @default(now()) @updatedAt @map("updated_at")

  @@map("t_articles")
}

// 文章和 Tag 是 many-to-many 的关系，一篇文章可以有多个tag, 一个tag 也可以存在于多篇文章，
model Tag {
  tagid       String           @id @default(uuid())
  name        String           @unique
  visible     Int              @default(1)
  relateCount Int?             @default(0) @map("relate_count")
  articles    TagsOnArticles[]
  User        User             @relation(fields: [userid], references: [userid])
  userid      String

  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @default(now()) @updatedAt @map("updated_at")

  @@map("t_tag")
}
model TagsOnArticles {
  // fields ：TagsOnArticles.artid reference:Article.artid
  article Article @relation(fields: [artid], references: [artid])
  artid   String
  tag     Tag     @relation(fields: [tagid], references: [tagid])
  tagid   String
  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @default(now()) @updatedAt @map("updated_at")
  @@id([artid, tagid])
  @@map("tags_on_articles")
}

And here is the generated tables' relation

Doc refered
https://www.prisma.io/docs/guides/other/troubleshooting-orm/help-articles/working-with-many-to-many-relations#explicit-relations

[ludralph]

Hi @jaycethanks 👋

Can you please share the relevant part of the code where you are creating the Articles and Tags?

[joisun]

hi, the related schema have already provided above, and below is the full prisma.schema file,
and here is the related repo, if needed. https://github.com/jaycethanks/zhongyi-blog-server/blob/master/prisma/schema.prisma
Thanks for your help !

// 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")
}

// 黑名单
model BlackList {
  id      String  @id @default(uuid())
  ip      String?
  email   String?
  keyword String?

  @@map("t_blacklist")
}

model User {
  userid    String     @id @default(uuid())
  name      String
  nickname  String?
  phone     String     @unique
  email     String     @unique
  password  String
  avatar    String?
  articles  Article[]
  categorys Category[]
  columns   Column[]
  tags      Tag[]
  About     About[]

  @@map("t_user")
}

model Article {
  artid    String @id @default(uuid())
  sort     Int    @default(0)
  author   User   @relation(fields: [authorId], references: [userid])
  authorId String @map("author_id")

  title       String
  description String?
  content     String  @db.LongText

  keywords String @default("")
  status   Int    @default(0) //0: 草稿 1:发布 3：删除

  cover    String    @default("")
  visible  Int       @default(1) // 0: 不可见 1:可见
  password String? // 加密文章
  comments Comment[]

  allowComment Int       @default(1) @map("allow_comment") // 1: 允许评论 0: 禁止评论
  liking       Int       @default(0)
  readers      Int       @default(0)
  banner       Int       @default(0) //0: 不在 banner 区域展示， 1 banner 区域的顺序
  category     Category? @relation(fields: [catid], references: [catid]) // 分类关联
  catid        String?
  column       Column?   @relation(fields: [colid], references: [colid]) // 专栏关联
  colid        String?

  tags TagsOnArticles[] // 标签关联

  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @default(now()) @updatedAt @map("updated_at")

  @@map("t_articles")
}

// 文章和 Tag 是 many-to-many 的关系，一篇文章可以有多个tag, 一个tag 也可以存在于多篇文章，
model Tag {
  tagid       String           @id @default(uuid())
  name        String           @unique
  visible     Int              @default(1)
  relateCount Int?             @default(0) @map("relate_count")
  articles    TagsOnArticles[]
  User        User             @relation(fields: [userid], references: [userid])
  userid      String

  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @default(now()) @updatedAt @map("updated_at")

  @@map("t_tag")
}

model TagsOnArticles {
  // fields ：TagsOnArticles.artid reference:Article.artid
  article Article @relation(fields: [artid], references: [artid])
  artid   String
  tag     Tag     @relation(fields: [tagid], references: [tagid])
  tagid   String

  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @default(now()) @updatedAt @map("updated_at")

  @@id([artid, tagid])
  @@map("tags_on_articles")
}

// 文章和 Comment 是 one-to-many 的关系， 一个文章有多个评论，但是某条评论仅关联至一篇文章
model Comment {
  comid    String  @id @default(uuid())
  content  String  @db.TinyText
  nickname String?
  avatar   String?
  email    String?
  url      String?
  ip       String?
  agent    String?
  city     String?
  province String?

  sort   Int @default(0)
  liking Int @default(0)
  status Int @default(1) //0:隐藏 1:展示

  article            Article @relation(fields: [articleId], references: [artid])
  articleId          String  @map("article_id")
  articleTitle       String? @map("article_title")
  articleDescription String? @map("article_description")

  parent         String?
  parentId       String? @map("parent_id")
  parentNickname String? @map("parent_nickname")

  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @default(now()) @updatedAt @map("updated_at")

  @@map("t_comment")
}

// 文章和 Category 是 one-tp-many 的关系，一篇文章只能有有个分类，但是一个分类可以包含多篇文章
model Category {
  catid       String    @id @default(uuid())
  name        String    @unique
  description String?
  sort        Int?      @default(0)
  relateCount Int?      @default(0) @map("relate_count")
  articles    Article[]
  user        User      @relation(fields: [userid], references: [userid])
  userid      String
  visible     Int       @default(1)

  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @default(now()) @updatedAt @map("updated_at")

  @@map("t_category")
}

// 文章和 Column 是 one-tp-many 的关系，一篇文章只能有一个专栏，但是一个专栏可以包含多篇文章
model Column {
  colid       String    @id @default(uuid())
  name        String    @unique
  description String?
  cover       String?
  visible     Int       @default(1)
  relateCount Int?      @default(0) @map("relate_count")
  articles    Article[]
  user        User      @relation(fields: [userid], references: [userid])
  userid      String

  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @default(now()) @updatedAt @map("updated_at")

  @@map("t_column")
}

model About {
  id        String   @id @default(uuid())
  msg       String   @db.LongText
  links     String?   @db.LongText
  user      User     @relation(fields: [userid], references: [userid])
  userid    String   @unique
  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @default(now()) @updatedAt @map("updated_at")
  
  @@map("t_about")
}

// ! deplicated 多对多
// model CategoriesOnArticles {
//   article  Article  @relation(fields: [artid], references: [artid])
//   artid    String
//   category Category @relation(fields: [catid], references: [catid])
//   catid    String

//   @@id([artid, catid])
//   @@map("categories_on_articles")
// }

// ! deplicated 多对多
// model ColumnsOnArticles {
//   article Article @relation(fields: [artid], references: [artid])
//   artid   String
//   column  Column  @relation(fields: [colid], references: [colid])
//   colid   String

//   @@id([artid, colid])
//   @@map("columns_on_articles")
// }

[ludralph]

Hi @jaycethanks, Thank you for sharing the requested information. I'm taking a look now.

[ludralph]

Can you please verify what is being stored for this line?

[joisun]

@ludralph Yeah, Of Course, the data shap of the param tags received from front end gonna be like this:

  'newTag01',
  'newTag02',
  'c5ff8423-3571-464d-8cd7-2ca5c0e3710d',
  'c3f4dc5a-3f19-4b66-8c3f-fb6f4e5113d8'
]

you can see the item memeber in the tags may not looks the same, the "newTag01","newTag02", is the new added "tag" (which means "label" literally that attach to a blog post), they are not exist in my t_tag mysql table. and for about another two id type ('c5ff8423-3571-464d-8cd7-2ca5c0e3710d', 'c3f4dc5a-3f19-4b66-8c3f-fb6f4e5113d8'), they are existed in the t_tag table. so code snippets around you point out line, means, connect to the t_tag table's certain tag with the id received, or create new tag with the value as tag name .

current t_tag table may like this:

If you'd like to try these or debug for some info , you can run the blog on your machine:

1. git clone git@github.com:jaycethanks/zhongyi-blog-admin.git (the font-admin-side)

2. git clone git@github.com:jaycethanks/zhongyi-blog-server.git

3. prepare the server side:

   # run docker container
   cd zhongyi-blog-server
   pnpm install
   chmod 700 run_mysql_docker.sh migrate_prisma.sh  fix_prisma_type.sh
   
   ./run_mysql_docker.sh # boot the docker container 
   ./migrate_prisma.sh # migrate prisma schema
   ./fix_prisma_type.sh # prisma type may hint error the first time boot , so try inital it here by fix
   
   # you may need create a temp user which need run the /create-a-user.ts script by ts-node
   pnpm install -g ts-node
   ts-node ./create-a-user
   
   pnpm run start:dev # start the nestjs server

4. prepare the front side:

   cd zhongyi-blog-admin
   pnpm install
   pnpm run dev

   log into the front-admin side

   (so sorry there's only chinese 🙈)

   1. create a category with anything

3. create a article and publish

the no.5 step is add the tags, and you can now try debug in need.

Thanks again for you time, And this problem have been stacked me for a long time , I have no way to solve it by meself ; (

[ludralph]

Hi there,

To keep our discussions organized and focused on the most relevant topics, we’re reviewing and tidying up our backlog. As part of this process, we’re closing discussions that haven’t had any recent activity and appear to be outdated.

If this discussion is still important to you or unresolved, we’d love to hear from you! Feel free to reopen it or start a new one with updated details.

For more details about our priorities and vision for the future of Prisma ORM, check out our latest blog post: https://www.prisma.io/blog/prisma-orm-manifesto.

Thank you for your understanding and being part of the community!