react-apollo - Prisma 2 查询仅返回与所有提供的标签 ID 关联的记录

Question

我有表 Principles 和 Tags。并且它们之间存在多对多关系(joined implicitly)。

在不使用 prisma.raw 的情况下，如何运行以下查询？

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

The docs提到 contains 和 in 和 every，但我找不到我正在尝试做的事情的例子。

我正在使用 RedwoodJs、Prisma 2、Apollo、GraphQL。

---

更新以回应评论:这里是 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
}

Answer 1

看起来您使用的不是 Prisma 2。Prisma 2 使用模型(而不是类型)并且数组分类为 Principles[] 与 [Principles]。也许 Redwood 会进行转换(从未使用过)。

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

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
}