Gorm FindInBatches over Offset instead of Primary Key increments

Actually find in Batches works using the primary key as an increment, so it assumes that the Batch is sorted with the PrimaryKey, which means that it will fail if the query contains any other Sort attribute.

I suggest to use the "offset" clause in order to iterate. Here is a suggestion of such a replacement function (I could do a Pull Request but I'm not comfortable with it on my current environment) :

// FindInBatches find records in batches
func (db *gorm.DB) FindInBatch(dest interface{}, batchSize int, fc func(tx *gorm.DB, batch int) error) *gorm.DB {
    var (
        tx = db.Session(&gorm.Session{})
        queryDB      = tx
        rowsAffected int64
        batch        int
    )

    for {
        result := queryDB.Limit(batchSize).Offset(batch*batchSize).Find(dest)
        rowsAffected += result.RowsAffected
        batch++

        if result.Error == nil && result.RowsAffected != 0 {
            tx.AddError(fc(result, batch))
        } else if result.Error != nil {
            tx.AddError(result.Error)
        }

        if tx.Error != nil || int(result.RowsAffected) < batchSize {
            break
        } 
    }

    tx.RowsAffected = rowsAffected
    return tx
} 

The motivation is to get a more universal (and more simple function), but I probably miss out something (like Offset clause not being available on all SQL databases?).

Comment From: github-actions[bot]

The issue has been automatically marked as stale as it missing playground pull request link, which is important to help others understand your issue effectively and make sure the issue hasn't been fixed on latest master, checkout https://github.com/go-gorm/playground for details. it will be closed in 2 days if no further activity occurs. if you are asking question, please use the Question template, most likely your question already answered https://github.com/go-gorm/gorm/issues or described in the document https://gorm.io ✨ Search Before Asking ✨

Comment From: jeffphp

I ended up doing a PR :)

Comment From: ngotchac

I 100% agree. It is counter-intuitive, and not trivial to debug in large datasets. It should at least be explicite in the docs.

Comment From: adamqazi

i see that the PR was closed and the changes weren't merged. is there a way to override the function and not use the primary key in the order by clause?