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

1.14

Which database and its version are you using?

msyql

Please provide a complete runnable program to reproduce your issue. IMPORTANT

package main

import (
    "fmt"
    "github.com/jinzhu/gorm"
    _ "github.com/jinzhu/gorm/dialects/mysql"
)

var (
    Dbo *gorm.DB
)

type User struct {
    Id   int64  `gorm:"type:bigint(11);column:id;primary_key;AUTO_INCREMENT;not null;"` //id
    Name string `gorm:"type:varchar(50);column:name;not null;"`                         //name
}
func main() {
    Dbo, _ = gorm.Open("mysql", "root:123456@(127.0.0.1:3306)/test?charset=utf8&parseTime=True&loc=Local")
    Dbo.LogMode(true)
    //query user
    queryUser()
}
func queryUser() {
    count := 0
    users := make([]User, 0)
    pageNo, pageSize := 1, 10 //page
    err := Dbo.Table("user").
        Where("id > 0").
        Select("id,name").
        Offset((pageNo - 1) * pageSize).
        Limit(pageSize).
        Order(" id asc ").Find(&users).Count(&count).Error
    if err != nil {
        panic(err)
    }
    fmt.Println(users)
    fmt.Println(count)
}

when pageNo=1
SELECT id,name FROM user WHERE (id > 0) ORDER BY id asc LIMIT 10 OFFSET 0 10 rows affected or returned

SELECT count(*) FROM user WHERE (id > 0) LIMIT 10 OFFSET 0

when pageNo=2 SELECT id,name FROM user WHERE (id > 0) ORDER BY id asc LIMIT 10 OFFSET 10 2 rows affected or returned

SELECT count(*) FROMuserWHERE (id > 0) LIMIT 10 OFFSET 10 panic: sql: no rows in result set

Correct count sql statement is SELECT count(*) FROMuserWHERE (id > 0)

Comment From: nickchou

it can be solved using the Limit(-1)，but is looks weird, can it be fixed the nex version of the framework?

err := Dbo.Table("user").
        Where("id > ?",0).
        Select("id,name").
        Offset((pageNo - 1) * pageSize).
        Limit(pageSize).
        Order(" id asc ").Find(&users).
        Limit(-1).
        Count(&count).Error

Comment From: valebedu

I've got the same error:

var result []*someModel
var count *int64

if offset < 0 {
    return nil, nil, errors.Errorf("offset: offset cannot be smaller than 0")
}
if limit < 1 || limit > 100 {
    return nil, nil, errors.Errorf("limit: limit cannot be smaller than 1 or greater than 100")
}
tx := db.Offset(offset).Limit(limit)
if order != nil {
    tx = tx.Order(*order)
}
if param != nil {
    tx = tx.Where("param = ?", *param)
}

dbError := tx.Find(&result).Count(&count).Error
if dbError != nil {
    return nil, nil, errors.Trace(dbError)
}

return result, count, nil

and obtain sql: no rows in result set when I set an offset greater than 0

Comment From: github-actions[bot]

This issue will be automatically closed because it is marked as GORM V1 issue, we have released the public testing GORM V2 release and its documents https://v2.gorm.io/docs/ already, the testing release has been used in some production services for a while, and going to release the final version in following weeks, we are still actively collecting feedback before it, please open a new issue for any suggestion or problem, thank you

Also check out https://github.com/go-gorm/gorm/wiki/GORM-V2-Release-Note-Draft for how to use the public testing version and its changelog

Comment From: Satan3

it can be solved using the Limit(-1)，but is looks weird, can it be fixed the nex version of the framework?

err := Dbo.Table("user"). Where("id > ?",0). Select("id,name"). Offset((pageNo - 1) * pageSize). Limit(pageSize). Order(" id asc ").Find(&users). Limit(-1). Count(&count).Error

You can get count without limit and then set your limit and get users : err := Dbo.Table("user").

  Where("id > ?",0).
  Select("id,name").
         Count(&count).
  Offset((pageNo - 1) * pageSize).
  Limit(pageSize).
  Order(" id asc ").Find(&users)

Comment From: dreamer2q

The problem still exists, why leave it closed?

Comment From: XHPJ

err := Dbo.Table("user").

In my case I cannot use the Limit(-1) hack with Scopes, which make scopes and the advantages not useful for me anymore, which again is a pity.

err := models.DB.
        Preload("association").
        Scopes(models.Paginate(c)).
        Find(&model).
        Limit(-1).
        Count(&count).
        Error

Am I missing something in my case? 🤔

Comment From: fatelei

The problem still exists, why leave it closed?

yes, i meet this error, because statement not clone, reuse the stat select x from y offset z limit size

Comment From: wyudong

Try Offset(-1) to reset offset.

Comment From: jcconnell

err := Dbo.Table("user").

In my case I cannot use the Limit(-1) hack with Scopes, which make scopes and the advantages not useful for me anymore, which again is a pity.

err := models.DB. Preload("association"). Scopes(models.Paginate(c)). Find(&model). Limit(-1). Count(&count). Error

Am I missing something in my case? 🤔

I'm seeing the same behavior. If I use Scopes, Count fails. If instead I add the logic from the scopes as Where and Order, the query succeeds.

// this fails
result := db.Model(&models.Transaction{}).
    Where("account_number = ?", actNum).
    Scopes(
        afterDate(startD),
        beforeDate(endD),
        orderBy("transaction_date", DESCENDING),
    ).
    Count(&count).
    Limit(PAGE_SIZE). // limit must come after count in order to get a total
    Find(&ts)

With error: `ERROR: column "transactions.transaction_date" must appear in the GROUP BY clause or be used in an aggregate function (SQLSTATE 42803)

// this succeeds
result := db.Model(&models.Transaction{}).
    Where("account_number = ?", actNum).
    Where(dateAfterQuery, startD.In(dbconfig.TimeZoneHST)).
    Where(dateBeforeQuery, endD.In(dbconfig.TimeZoneHST)).
    Order(clause.OrderByColumn{
        Column: clause.Column{Name: "transaction_date"},
        Desc:   DESCENDING,
    }).
    Count(&count).
    Limit(PAGE_SIZE). // limit must come after count in order to get a total
    Find(&ts)

Comment From: prazian

err := Dbo.Table("user").

In my case I cannot use the Limit(-1) hack with Scopes, which make scopes and the advantages not useful for me anymore, which again is a pity.

err := models.DB. Preload("association"). Scopes(models.Paginate(c)). Find(&model). Limit(-1). Count(&count). Error

Am I missing something in my case? 🤔

Today, I faced the same issue with pagination through Scopes and then wanting to reset (for counting) using .Limit(-1).Offset(-1).

Did you find any solution for it?

This is my code:

func CountResults(db *gorm.DB, model interface{}, scopes *[]DBScope) (int64, error) {
    var count int64
    res := db.Model(model)
    if scopes != nil {
        for _, scope := range *scopes {
            res = res.Scopes(scope)
        }
    }
    // Reset limit and offset to count everything <- this doesn't work as expected!
    res.Limit(-1).Offset(-1)
    res.Count(&count)
    if res.Error != nil {
        return 0, res.Error
    }
    return count, nil
}

But the final query still has the limit and offset!

Comment From: fernandofleury

@prazian I've completely decoupled the pagination from the scope usage to "fix it":

func (p *Pagination[T]) Paginate(db *gorm.DB) *gorm.DB {
    var total int64

    db.Count(&total)

    p.CountResults = int(total)
    p.CountPages = p.SafePages(int(total), p.SafeLimit())

    return db.Offset(p.Offset()).Limit(p.SafeLimit())
}


func (r *repo) SampleQuery(ctx context.Context, pagination *database.Pagination[SampleType]) (*database.Pagination[SampleType], error) {
    var results []SampleType

    query := repo.db.
        WithContext(ctx).
                // notice the Model being chained here even tho Find already has it. We need it since Find would only happen after counting
        Model(&results).
        // key point to notice here is that we count right before executing the query so it contains all the filters
    if err := pagination.Paginate(query).Find(&results).Error; err != nil {
        return nil, err
    }

    pagination.Results = results

    return pagination, nil
}

Comment From: prazian

Thanks, @fernandofleury, for sharing. I also ended up taking a similar approach.