获取结果列表，并在一个查询中获取总数

Question

我有用于分页的逻辑，它工作得非常好。这个问题是为了优化/改进我是如何做某事的。

我正在运行两个查询以获得结果集。第一个查询按限制和偏移量获取所有项，第二个查询获取总计数。我需要这个总数来计算表示层中的分页链接。

我想避免运行两个查询，如果可能的话，将它们合并成一个。

例如，如果数据库中有100项，并且我运行下面的查询时LimitStartIndex为1，LimitStopIndex为20，那么查询应该返回20个结果和100个计数。我目前在两个单独的查询中实现了这一点，但我又想在一个查询中完成。

以下是我当前的设置（使用Golang）：

var items []*Item
err := r.db.Select(&items, `
    SELECT item.*
    FROM item
    JOIN user
        ON user.username = ?
    JOIN user_item
        ON user_item.item_id = item.id
        AND user_item.user_id = user.id
    ORDER BY item.id DESC
    LIMIT ?,?
`, username, pagination.LimitStartIndex, pagination.LimitStopIndex)
if err != nil {
    // ...
}

var total int
err = r.db.Get(&total, `
    SELECT COUNT(*)
    FROM item
    JOIN user
        ON user.username = ?
    JOIN user_item
        ON user_item.item_id = item.id
        AND user_item.user_id = user.id
`, username)
if err != nil {
    // ...
}

result := &domain.PaginationResult{
    Items: items,
    Total: total,
}

我试过这样的方法：

SELECT item.*, COUNT(DISTINCT item.id) AS _count
FROM item
JOIN user
    ON user.username = ?
JOIN user_item
    ON user_item.item_id = item.id
    AND user_item.user_id = user.id
ORDER BY item.id DESC
LIMIT ?,?

但是，我收到此错误：

错误1140：在没有GROUP BY的聚合查询中，SELECT列表的表达式#1包含非聚合列mytable.item.id；这与sql_mode=only_full_group_by

Answer 1

确保在查询中添加'group by'。希望这能有所帮助。谢谢。

SELECT item.*, COUNT(*) AS _count
FROM item
JOIN user
    ON user.username = ?
JOIN user_item
    ON user_item.item_id = item.id
    AND user_item.user_id = user.id
GROUP BY item.id        
ORDER BY item.id DESC
LIMIT ?, ?