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

Question

I have logic that is meant for pagination, and it works perfectly fine as is. This question is meant for optimization/improvement in regards to how I'm doing something.

I am running two queries to get a result set. The first query gets all items by limit and offset, the second query gets the total count. I need this total to do math for pagination links in the presentation layer.

I want to avoid running two queries, and combine them into one if possible.

For example, if there is 100 items in the database and I run the query below with the LimitStartIndex at 1 and LimitStopIndex at 20, then the query should return 20 results and a count of 100. I currently achieve this in two separate queries just fine, but again I want to do it in one query.

Here is my current setup (using 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,
}

I tried something like this:

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 ?,?

However I receive this error:

Error 1140: In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'mytable.item.id'; this is incompatible with sql_mode=only_full_group_by

Answer 1

Ensure to add 'group by' in the query. Hope this helps. Thanks.

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 ?, ?