英文:
Postgresql + gorm complex pagination with groups
问题
我有一个带有GroupId列的表，可能有大量的行。有没有一种按组分页获取数据的方法？例如，先获取前10个组，然后获取接下来的10个组，以此类推？
表的结构如下（默认未排序）-对于格式不好的问题，抱歉，由于某种原因它没有起作用，但我认为它是可见的：
| Col1 | Col2 | GroupId |
|---|---|---|
| data A | foo1 | 1 |
| data B | foo2 | 1 |
| data C | foo3 | 2 |
...
那么有没有一种高效地获取前10个组、接下来的10个组等的行的方法，每个组的行数不固定？假设有数百万行和任意数量的组。
英文:
I have table with GroupId column, and potentially huge number of rows. Is there a way to get that data paginated by groups? For example, get first 10 groups, then next 10 groups etc?
Table would have this structure (not sorted by default) - sorry for bad formatting, for some reason it did not work, but i think it is visible
| Col1 | Col2 | GroupId |
|---|---|---|
| data A | foo1 | 1 |
| data B | foo2 | 1 |
| data C | foo3 | 2 |
...
So is there a way to get rows efficiently of fist 10 groups, then next 10 groups etc, with not fixed number of rows per group? Let's say there are millions of rows and groups can be any number.
答案1
得分: 1
这段代码的作用是根据给定的分组ID（GroupId）查询数据，而不是真正的分页。如果你知道分组ID是连续的，可以像这样操作。这段代码使用纯Go编写，你可以很容易地将gorm与其结合使用。
queries函数会打印出应该运行的每个查询，以获取每个分组中的所有结果。例如：
SELECT * FROM MyTable WHERE GroupId >= 0 AND GroupId < 10
SELECT * FROM MyTable WHERE GroupId >= 10 AND GroupId < 20
SELECT * FROM MyTable WHERE GroupId >= 20 AND GroupId < 30
SELECT * FROM MyTable WHERE GroupId >= 30 AND GroupId < 40
SELECT * FROM MyTable WHERE GroupId >= 40 AND GroupId < 50
SELECT * FROM MyTable WHERE GroupId >= 50 AND GroupId < 60
SELECT * FROM MyTable WHERE GroupId >= 60 AND GroupId < 70
SELECT * FROM MyTable WHERE GroupId >= 70 AND GroupId < 80
SELECT * FROM MyTable WHERE GroupId >= 80 AND GroupId < 90
SELECT * FROM MyTable WHERE GroupId >= 90 AND GroupId < 100
到目前为止，我们假设你已经准确地知道要查询的“分组桶”。如果我们假设我们知道有多少个连续的分组（n）和每个查询中要包含的分组数（桶大小 s），我们可以轻松地创建一个函数来给出应该查询的桶。
你可以在go playground上查看第二段代码的示例。
英文:
It's not really paginated as much as you're just querying for data by a bounded identifier (GroupId). If you know your groups IDs and they are sequential, then you could do something like this. This is done using pure Go, you could easily incorporate gorm with this.
package main
import (
"fmt"
)
func main() {
for _, q := range queries() {
fmt.Println(q)
}
}
func queries() (out []string) {
groups := []int{10, 20, 30, 40, 50, 60, 70, 80, 90, 100}
for i := 0; i < len(groups); i++ {
var lower, upper int = 0, 0
if i != 0 {
lower = groups[i-1]
}
upper = groups[i]
q := fmt.Sprintf("SELECT * FROM MyTable WHERE GroupId >= %v AND GroupId < %v", lower, upper)
out = append(out, q)
}
return
}
A call to queries prints out each of the queries that should be run to get all of the results in each group. For example:
SELECT * FROM MyTable WHERE GroupId >= 0 AND GroupId < 10
SELECT * FROM MyTable WHERE GroupId >= 10 AND GroupId < 20
SELECT * FROM MyTable WHERE GroupId >= 20 AND GroupId < 30
SELECT * FROM MyTable WHERE GroupId >= 30 AND GroupId < 40
SELECT * FROM MyTable WHERE GroupId >= 40 AND GroupId < 50
SELECT * FROM MyTable WHERE GroupId >= 50 AND GroupId < 60
SELECT * FROM MyTable WHERE GroupId >= 60 AND GroupId < 70
SELECT * FROM MyTable WHERE GroupId >= 70 AND GroupId < 80
SELECT * FROM MyTable WHERE GroupId >= 80 AND GroupId < 90
SELECT * FROM MyTable WHERE GroupId >= 90 AND GroupId < 100
So far, we've made the assumption that you already know exactly what 'group buckets' you want to query. If instead we assume that we know how many sequential groups there are (n) and how many groups we want per query (the bucket size s), we can easily create a function to give us the buckets we should query on.
func groups(n, s int) (out []int) {
for i := 0; i <= n; i++ {
if i == 0 {
continue
}
if i%s == 0 || i == n {
out = append(out, i)
}
}
return
}
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论