I have a DB with posts and tags. I want to find posts that only contain 1 tag with a specific tagId and no other tags at all. The code below works, but it requires the server to filter the results manually which will make pagination difficult. Does anyone have a direct query that can do this?

我有一个带有帖子和标签的数据库。我想找到的帖子，只包含一个标签与一个特定的TagID，根本没有其他标签。下面的代码可以工作，但它需要服务器手动过滤结果，这将使分页变得困难。有谁有可以做到这一点的直接查询吗？

I tried a groupBy on postId in PostTag, but the issue is when I search for the tagId in a where clause then all entries on the PostTag only have 1 entry which makes counting impossible.

我在PostTag中尝试了postID上的groupBy，但问题是，当我在WHERE子句中搜索TagID时，PostTag上的所有条目都只有一个条目，这使得计数变得不可能。

I am using Prisma as a wrapper for a PostgresQL DB.

我正在使用Prisma作为PostgreSQL数据库的包装器。

        const query = await prisma.post.findMany({
            where: {
                tags: {
                    some: {
                        tagId: Number(tagId),
                    }
                },
            },
            select: {
                id: true,
                tags: {
                    select: {
                        tagId: true,
                    }
                }
            },
        })
        const tagIds = query
            .filter(item => item.tags.length === 1)
            .map(item => ({ id: item.id }));

Models

模型

model Post {
    id                     Int                         @id @default(autoincrement())
    tags                   PostTag[]
}

//Pivot Table
model PostTag {
    id           Int               @id @default(autoincrement())
    post         Post              @relation(fields: [postId], references: [id], onDelete: Cascade)
    postId       Int
    tag          Tag               @relation(fields: [tagId], references: [id])
    tag    Id    Int
}

model Factiii {
    id              Int                  @id @default(autoincrement())
    tags            PostTag[]
}

I think the keyword here is having which unfortunately Prisma doesn't yet support in where clauses.

我认为这里的关键字是HAVING，不幸的是，Prisma在WHERE子句中还不支持。

They support having in the groupBy function, but that doesn't allow you to check based on the count.

它们支持在groupBy函数中包含，但这不允许您基于计数进行检查。

You can try this, which feels like a workaround but could work in your case:

你可以试试这个，这感觉像是一种变通办法，但在你的情况下可能会奏效：

const query = await prisma.post.findMany({
            where: {
                tags: {
                    every: { //this is the change
                        tagId: Number(tagId),
                    }
                },
            },
            select: {
                id: true,
                tags: {
                    select: {
                        tagId: true,
                    }
                }
            },
        })

The idea here is that since a post can contain a tag only once, if you provide only 1 value for comparison (Number(tagId) in this case) it can only match posts with one tag, which is the tag you care about. if my logic is wrong, please kindly let me know

这里的想法是，由于一个帖子只能包含一个标记一次，如果您只提供一个值进行比较(在本例中为number(TagID))，则它只能将帖子与一个标记匹配，这是您关心的标记。如果我的逻辑有误，请让我知道

Alternatively, you can consider doing a raw query with Prisma where you supply your own SQL code directly.

或者，您可以考虑使用Prisma执行原始查询，在那里您可以直接提供自己的SQL代码。

If you're doing that, BE CAREFUL! Read more about raw queries with Prisma to be safe against SQL injections.

如果你要这么做，要小心！阅读有关使用PRISMA进行原始查询的更多信息，以确保不受SQL注入的影响。

Here is the SQL code you can use for this purpose:

以下是可用于此目的的SQL代码：

SELECT p.*
FROM Post p
JOIN (
  SELECT pt.post_id
  FROM PostTag pt
  JOIN Tag t ON pt.tag_id = t.id
  WHERE t.id = 1 -- Replace with your specific tagId
  GROUP BY pt.post_id
  HAVING COUNT(*) = 1
) subquery ON p.id = subquery.post_id;

you could try this from Rayan answer on this question

你可以试试Rayan对这个问题的回答

await prisma.post.findMany({ 

where: { tags: { some: { tag: { id: 1 } } } }
})

其中：{标签：{一些：{标签：{id：1}}})