Prisma 2 查询仅返回与所有提供的标签 ID 关联的记录 [英] Prisma 2 query to return records only that are associated with ALL of the provided tag IDs

问题描述

我有表格原则和标签.它们之间存在多对多关系(隐式加入).

I have tables Principles and Tags. And there is a many-to-many relation between them (joined implicitly).

不使用 prisma.raw，如何运行以下查询?

Without using prisma.raw, how can I run the following query?

SELECT p.id, p.title, p.description, p.createdAt, p.modifiedAt
    FROM principle p
   WHERE EXISTS (SELECT NULL
                   FROM _PrincipleToTag pt
                  WHERE pt.B IN (${tagIds.join(',')})
                    AND pt.A = p.id
               GROUP BY pt.A
                 HAVING COUNT(DISTINCT pt.B) = ${tagIds.length})

如何更新此 Prisma 2 查询，以便返回的原则只是与所有提供的 tagId 相关联的原则?

export const principles = ({ tagIds }) => {
  const payload = {
    where: {
      //TODO filter based on tagIds
    },
  }
  return db.principle.findMany(payload)
}

文档 提到了 contains 和 in 和 every，但我找不到我想要做的事情的例子.

The docs mention contains and in and every, but I can't find examples of what I'm trying to do.

我使用的是 RedwoodJs、Prisma 2、Apollo、GraphQL.

I'm using RedwoodJs, Prisma 2, Apollo, GraphQL.

更新以回应评论:这是 SDL:

Update in response to comment: here is the SDL:

input CreatePrincipleInput {
  title: String!
  description: String
}

input CreatePrincipleWithTagsInput {
  title: String!
  description: String
  tagIdsJson: String
}

input CreateTagInput {
  title: String!
  description: String
}

# A date string, such as 2007-12-03, compliant with the `full-date` format
# outlined in section 5.6 of the RFC 3339 profile of the ISO 8601 standard for
# representation of dates and times using the Gregorian calendar.
scalar Date

# A date-time string at UTC, such as 2007-12-03T10:15:30Z, compliant with the
# `date-time` format outlined in section 5.6 of the RFC 3339 profile of the ISO
# 8601 standard for representation of dates and times using the Gregorian calendar.
scalar DateTime

type Mutation {
  createPrinciple(input: CreatePrincipleInput!): Principle
  createPrincipleWithTags(input: CreatePrincipleWithTagsInput!): Principle
  updatePrinciple(id: Int!, input: UpdatePrincipleInput!): Principle!
  deletePrinciple(id: Int!): Principle!
  createTag(input: CreateTagInput!): Tag!
  updateTag(id: Int!, input: UpdateTagInput!): Tag!
  deleteTag(id: Int!): Tag!
}

type Principle {
  id: Int!
  title: String!
  description: String!
  tags: [Tag]
  createdAt: DateTime!
  modifiedAt: DateTime!
}

type Query {
  redwood: Redwood
  principles(searchQuery: String, tagIds: [Int]): [Principle!]!
  tags: [Tag!]!
  tagsByLabel(searchTerm: String): [TagCount!]!
  tag(id: Int!): Tag!
}

type Redwood {
  version: String
}

type Tag {
  id: Int!
  title: String!
  principles: [Principle]
  description: String
  createdAt: DateTime!
  modifiedAt: DateTime!
}

type TagCount {
  id: Int!
  title: String!
  count: Int!
  principles: [Principle]
  description: String
  createdAt: DateTime!
  modifiedAt: DateTime!
}

# A time string at UTC, such as 10:15:30Z, compliant with the `full-time` format
# outlined in section 5.6 of the RFC 3339profile of the ISO 8601 standard for
# representation of dates and times using the Gregorian calendar.
scalar Time

input UpdatePrincipleInput {
  title: String
  description: String
}

input UpdateTagInput {
  title: String
  description: String
}

推荐答案

看起来您不是在使用 Prisma 2.Prisma 2 使用模型(而不是类型)并且将数组归类为原则 [] 与 [原则].也许 Redwood 会进行转换(从未使用过).

It doesn't look like you are using prisma 2. Prisma 2 uses models (not types) and has arrays classified like Principles[] vs [Principles]. Maybe Redwood does the conversion(Never used it).

我在 Prisma 2 中创建了您的模型，并使用以下命令获取了一个具有两个关联标签的原理.请记住，其中的 ID 来自我的测试数据集.希望您可以将其修改为您的代码.如果没有，请用最少的代码创建一个沙箱/游乐场供我们测试.

I created your model in Prisma 2 and used the following command to get a single principle that has the two tags associated with it. Keep in mind the IDs in there are from my test dataset. Hopefully, you can modify this to your code. If not, please create a sandbox/playground with minimal code for us to test.

export const principles = async ({ searchQuery, tagIds }) => {      
  const payload = {
    where: {
      OR: [
        { title: { contains: searchQuery } },
        { description: { contains: searchQuery } },
      ],
      userId: userIdFromSession,
    },
  }
  if (tagIds.length) {
    const whereAnd = []
    tagIds.forEach((tagId) => {
      whereAnd.push({
        tags: { some: { id: tagId } },
      })
    })
    payload.where.AND = whereAnd
  }
  const result = await db.principle.findMany(payload)
  return result
}

这篇关于Prisma 2 查询仅返回与所有提供的标签 ID 关联的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！