PostgreSQL:返回已筛选的行以及匹配条件的总行数

Question

假设我有一个表posts，我可以在客户端应用程序上查询和筛选它。每个帖子都有一个相关的类型，我希望能够在客户端上看到筛选后的帖子，以及在面板上按类型匹配过滤器的总行数。显然，我希望在一个查询中完成此操作。同样重要的是要注意，我正在对数据进行分页，这样我就不能只在一些后端逻辑中使用filter(...).length，因为可能有100000个帖子，但只有10个返回到客户端。

以下是我正确过滤数据的查询：

knex('posts').select('id', 'created_at', 'content', 'type').modify((builder) => (
filterPosts(builder, filters)
)).paginate({currentPage: offset, perPage: limit})

我想知道是否有某种方法可以计算与过滤器匹配的帖子数量(按类型(，并在我现有的查询中返回这些计数。

例如，我的结果目前如下：

[
{
id: 123,
created_at: "Jan 1, 2022",
content: "Lorem ipsum",
type: "Type 1"
},
{
id: 456,
created_at: "Feb 1, 2022",
content: "Ipsum dolor",
type: "Type 2"
}
...
]

我想要这样的东西：

[
{
id: 123,
created_at: "Jan 1, 2022",
content: "Lorem ipsum",
type: "Type 1"
countType1: 3, // Total rows where type = "Type 1" that match the filters
countType2: 6 // Total rows where type = "Type 2" that match the filters
},
{
id: 456,
created_at: "Feb 1, 2022",
content: "Ipsum dolor",
type: "Type 2",
countType1: 3, 
countType2: 6
}
...
]

我尝试过使用窗口函数，但到目前为止只能获得当前行类型的帖子数量，而不是所有类型的

knex('posts').select(
'id', 
'created_at', 
'content', 
'type',
'count(*) over (partition by posts.type)' // If I could add a WHERE clause here I'd be golden
).modify((builder) => (
filterPosts(builder, filters)
)).paginate({currentPage: offset, perPage: limit})

以上给出：

[
{
id: 123,
created_at: "Jan 1, 2022",
content: "Lorem ipsum",
type: "Type 1"
count: 3,
},
{
id: 456,
created_at: "Feb 1, 2022",
content: "Ipsum dolor",
type: "Type 2",
count: 6
}
...
]

这不是最佳的，因为由于分页，可能会向客户端返回10个仅类型为2的帖子，使客户端认为有0个类型为1的帖子。

欢迎就如何改进这一点提出建议，任何帮助都将不胜感激！

这篇文章似乎是在正确的轨道上，但我不知道如何让它为我的场景工作

Answer 1

我能够通过构建我链接的帖子来解决这个问题：

knex
.with(
'posts',
knex
.from(
knex('posts').select(
{ id: 'posts.id' },
{ created_at: 'posts.created_at' },
{ content: 'posts.content' },
{ type: 'posts.type' },
)
)
.modify((builder) => filterPosts(builder, filters)) // Contains joins and where clauses
)
.from('posts')
.rightJoin(
knex.raw(
"(select count(1) filter(where posts.type = 'Type 1'), count(1) filter(where posts.type = 'Type 2') from posts) c(type_1_count, type_2_count) on true"
)
)
.select(
'posts.*',
'type_1_count',
'type_2_count'
);