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

问题描述

我有一些表Principles and Tags.它们之间存在多对多关系(

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

docs 提到了包含和 in 以及每个，但是我找不到我想做的事的例子.

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
}

推荐答案

看起来好像您在使用prisma2.Prisma2使用模型(不是类型)，并且具有分类为Principles [] vs [Principles]的数组.也许雷德伍德做了转换(从未使用过).

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屋！