英文:
Golang SQLBoiler returns nothing when running orderby
问题
我正在使用Go语言的SqlBoiler库向PostgreSQL数据库发送请求，但是当尝试按照某个字段对数据库进行排序时，返回的结果集为空。
首先，我运行了一个计数查询（如下所示），如果计数返回的行数大于等于1，则查询数据库获取所有结果。
这是返回正确的行数计数：
res, _ := models.MT(
Where("(mt_mas = ? or mt_mem like ?) and mt_group = ?", uint(uid), `%"`+strconv.Itoa(uid)+`"%`, bool(mt_group_bool)),
).Count(CTX, DB)
这是返回空结果集的查询，尽管查询参数完全相同：
res, _ := models.MT(
Where("(mt_mas = ? or mt_mem like ?) and mt_group = ?", uint(uid), `%"`+strconv.Itoa(uid)+`"%`, bool(mt_group_bool)),
OrderBy("mt_mas"),
).Count(CTX, DB)
这是在检查行数计数后获取所有行的方法：
res, err := models.MT(
Where("(mt_mas = ? or mt_mem like ?) and mt_group = ?", uint(uid), `%"`+strconv.Itoa(uid)+`"%`, bool(mt_group_bool)),
OrderBy("mt_mas"),
).All(CTX, DB)
当从上述请求中读取错误时，它打印出<nil>，一切似乎都正常。
数据库是PostgreSQL（版本为 PostgreSQL 13.3），列如下：
- mt_mas（整数）
该列保存此行所有者的uid。 - mt_mem（字符变长[1000]）
该列保存用户成员uid的JSON列表。 - mt_group（布尔值）
该列显示此行是否为组。
数据库行的示例：
| mt_mas | mt_mem | mt_group |
|---|---|---|
| 1 | {"1", "2"} | false |
英文:
I'm using SqlBoiler on Go to send requests to a PostgreSQL db, but when trying to order the database by one of the fields it returns 0 rows.
At first i run a count (like detailed below) and if the count returns more than or equal to one row then i query the database for all the results.
This returns the correct row count:
res, _ := models.MT(
Where("(mt_mas = ? or mt_mem like ?) and mt_group = ?", uint(uid), `%"`+strconv.Itoa(uid)+`"%`, bool(mt_group_bool)),
).Count(CTX, DB)
This returns no rows, despite the query params being exactly the same:
res, _ := models.MT(
Where("(mt_mas = ? or mt_mem like ?) and mt_group = ?", uint(uid), `%"`+strconv.Itoa(uid)+`"%`, bool(mt_group_bool)),
OrderBy(`mt_mas`),
).Count(CTX, DB)
This is how I get all the rows after checking the row count:
res, err := models.MT(
Where("(mt_mas = ? or mt_mem like ?) and mt_group = ?", uint(uid), `%"`+strconv.Itoa(uid)+`"%`, bool(mt_group_bool)),
OrderBy(`mt_mas`),
).All(CTX, DB)
When reading the error from the request above, it prints out <nil> and everything seems fine.
The database is, as mentioned Postgres (version PostgreSQL 13.3), and the columns are as follows:
- mt_mas (integer)
This column holds the uid of the owner of this row. - mt_mem (character varying [1000])
This column holds a JSON list of user members uid:s. - mt_group (boolean)
This column shows of this row is a group of not.
Example of database row:
| mt_mas | mt_mem | mt_group |
|---|---|---|
| 1 | {"1", "2"} | false |
答案1
得分: 2
好的，以下是翻译好的内容：
好的，解决方案比预期的简单。实际上，@Gari Singh（https://stackoverflow.com/users/5529712/gari-singh）在上面的评论中指出了这一点，但我觉得我会在这里写下来，这样问题就会被标记为已解决。
解决方案就是在计算结果的查询中不排序。所以正确的计数代码应该只是：
res, _ := models.MT(
Where("(mt_mas = ? or mt_mem like ?) and mt_group = ?", uint(uid), `%"`+strconv.Itoa(uid)+`"%`, bool(mt_group_bool)),
).Count(CTX, DB)
然后运行带有排序的查询来获取实际的行，像这样：
res, err := models.MT(
Where("(mt_mas = ? or mt_mem like ?) and mt_group = ?", uint(uid), `%"`+strconv.Itoa(uid)+`"%`, bool(mt_group_bool)),
OrderBy("mt_mas"),
).All(CTX, DB)
谢谢你的帮助！ 
英文:
Okay, the solution was simpler than expected. @Gari Singh (https://stackoverflow.com/users/5529712/gari-singh) actually pointed this out in the comments above, but i figured i would write it here so the question registers as solved.
The solution is to simply not order in the query that counts the results. So the correct code for counting should only be:
res, _ := models.MT(
Where("(mt_mas = ? or mt_mem like ?) and mt_group = ?", uint(uid), `%"`+strconv.Itoa(uid)+`"%`, bool(mt_group_bool)),
).Count(CTX, DB)
And then running the query to get the actual rows with the order, like this:
res, err := models.MT(
Where("(mt_mas = ? or mt_mem like ?) and mt_group = ?", uint(uid), `%"`+strconv.Itoa(uid)+`"%`, bool(mt_group_bool)),
OrderBy("mt_mas"),
).All(CTX, DB)
Thank you for your help!
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论