Gorm: Count doesn't work with Limit, Offset

[satouriko]

Hello! I am encountering some problems using the function Count() chained after Limit() and Offset(), here is the code

err := db.Limit(perPage).Offset((page - 1) * perPage).Find(&tasks).Count(&total).Error

In fact it works pretty well if page is 1, i.e. OFFSET 0, we can successfully get the tasks in page 1 and a count of the total entries of task. However, when it comes to page 2, it will throw an error sql: no rows in result set
Is the Count() function incompatible with Offset() function by designed or it is a bug? Although I have no idea about how the Count() function is implemented, I guess there shouldn't be a conflict between fetching data paginated and the total count of the data. (https://stackoverflow.com/questions/28888375/run-a-query-with-a-limit-offset-and-also-get-the-total-number-of-rows)
If it is designed like so, (maybe in purpose of avoiding ambiguity, etc), I'll be appreciated if you provide a mention in the document.

What version of Go are you using (go version)?

go version go1.9.3 linux/amd64

Which database and its version are you using?

mysql Ver 14.14 Distrib 5.5.58, for debian-linux-gnu (x86_64) using readline 6.3

What did you do?

Please provide a complete runnable program to reproduce your issue.

package main

import (
    "github.com/jinzhu/gorm"
    _ "github.com/jinzhu/gorm/dialects/mssql"
    _ "github.com/jinzhu/gorm/dialects/mysql"
    _ "github.com/jinzhu/gorm/dialects/postgres"
    _ "github.com/jinzhu/gorm/dialects/sqlite"
)

var db *gorm.DB

func init() {
    var err error
    db, err = gorm.Open("sqlite3", "test.db")
    // Please use below username, password as your database's account for the script.
    // db, err = gorm.Open("postgres", "user=gorm dbname=gorm sslmode=disable")
    // db, err = gorm.Open("mysql", "gorm:gorm@/dbname?charset=utf8&parseTime=True")
    // db, err = gorm.Open("mssql", "sqlserver://gorm:LoremIpsum86@localhost:1433?database=gorm")
    if err != nil {
        panic(err)
    }
    db.LogMode(true)
}

type Task struct {
    ID         uint      `gorm:"AUTO_INCREMENT"`
        Name       string
}

func main() {
        var perPage, page uint = 10, 1
        var tasks []Task
        var total uint
        // When we set page = 1 every thing works fine, however when we set page >= 2 it returns an empty `tasks` with error `sql: no rows in result set`
        db.Limit(perPage).Offset((page - 1) * perPage).Find(&tasks).Count(&total)
        // To make this work we can use this alternative code
        db.Limit(perPage).Offset((page - 1) * perPage).Find(&tasks)
        db.Model(&Task{}).Count(&total)
}

[jinzhu]

NOTE to myself, Count doesn't work with Offset.

[dzpt]

@jinzhu How do i get count to work with Offset?
While pagination i need to know total records and offset to limit the result.
db.Model(&Task{}).Count(&total) would count all records , not with the query

[malyusha]

@Tom29 Usually, for pagination you just need to know the number of page to show, or the limit and offset to select with. But for total count you don't need to set offset, it's just useless. All you need for counting pagination is select total count of entities with (for example) filter constraints, and then count total amount of pages. So count will perform an additional query. Just count number of your models with len

[yangjianhua]

Seems still get this error?

@malyusha Get the filtered result's total is necessary, I think.

tx := this.Context.DB.Where("name LIKE ?", "%"+name+"%")
tx.Offset(pageno).Limit(pagecount).Find(&users).Count(&totalcount)

This count just get the current page's total count, right? How to get the filtered result total count?

[huynguyenh]

i did overwrite Limit, Offset right before Count() to workaround it.

db = db.Offset((page - 1) * limit).Limit(limit)

return res, count, db.Find(&res).Offset(0).Limit(-1).Count(&count).Error

[firesh]

This is very common in data pagination case. Overwrite the limit and offset before Count() works well for me.

[Niondir]

Thanks a lot, the workaround from @huynguyenh works well. I had the same issue and would welcome a patch in future :)

Another workarround is fixing the order like this:

    db = db.Model(&model.ParsedData{}).
    Where(where).
    Order("created_at DESC").
    Count(&page.Total).
    Limit(page.Limit). // Do limit and offset after count, but you now need Model before.
    Offset(page.Offset).
    Find(&parsedData)

[dotungvp1994]

@Niondir: i tries using order->count->find but find is not select() it using select count(). I think callCallbacks(s.parent.callbacks.queries) replace select count() instead of select ().
I try using workaround from @huynguyenh and working done. Tks for tips. :D
Edit: I try v1.9.1 it's working when using order->count->find :) @Niondir

[emirb]

Closing as this seems to be fixed already and is not a library issue, but a client usage case.