MongoDB / mgo query that supports efficient pagination

minquery

Build Status GoDoc Go Report Card codecov

MongoDB / mgo query that supports efficient pagination (cursors to continue listing documents where we left off).

Note: Only MongoDB 3.2 and newer versions support the feature used by this package.

Note #2: minquery v1.0.0 uses the gopkg.in/mgo.v2 mgo driver which has gone unmaintained for a long time now. minquery v2.0.0 (tip of master) uses the new, community supported fork github.com/globalsign/mgo. It is highly recommended to switch over to globalsign/mgo. If you can't or don't want to, you may continue to use the v1.0.0 release with gopkg.in/mgo.v2.

Introduction

Let's say we have a users collection in MongoDB modeled with this Go struct:

type User struct {
    ID      bson.ObjectId `bson:"_id"`
    Name    string        `bson:"name"`
    Country string        `bson:"country"`
}

To achieve paging of the results of some query, MongoDB and the mgo driver package has built-in support in the form of Query.Skip() and Query.Limit(), e.g.:

session, err := mgo.Dial(url) // Acquire Mongo session, handle error!

c := session.DB("").C("users")
q := c.Find(bson.M{"country" : "USA"}).Sort("name", "_id").Limit(10)

// To get the nth page:
q = q.Skip((n-1)*10)

var users []*User
err = q.All(&users)

This however becomes slow if the page number increases, as MongoDB can't just "magically" jump to the xth document in the result, it has to iterate over all the result documents and omit (not return) the first x that need to be skipped.

MongoDB provides the right solution: If the query operates on an index (it has to work on an index), cursor.min() can be used to specify the first index entry to start listing results from.

This Stack Overflow answer shows how it can be done using a mongo client: How to do pagination using range queries in MongoDB?

Note: the required index for the above query would be:

db.users.createIndex(
    {
        country: 1,
        name: 1,
        _id: 1
    }
)

There is one problem though: the mgo package has no support specifying this min().

Introducing minquery

Unfortunately the mgo driver does not provide API calls to specify cursor.min().

But there is a solution. The mgo.Database type provides a Database.Run() method to run any MongoDB commands. The available commands and their documentation can be found here: Database commands

Starting with MongoDB 3.2, a new find command is available which can be used to execute queries, and it supports specifying the min argument that denotes the first index entry to start listing results from.

Good. What we need to do is after each batch (documents of a page) generate the min document from the last document of the query result, which must contain the values of the index entry that was used to execute the query, and then the next batch (the documents of the next page) can be acquired by setting this min index entry prior to executing the query.

This index entry –let's call it cursor from now on– may be encoded to a string and sent to the client along with the results, and when the client wants the next page, he sends back the cursor saying he wants results starting after this cursor.

And this is where minquery comes into the picture. It provides a wrapper to configure and execute a MongoDB find command, allowing you to specify a cursor, and after executing the query, it gives you back the new cursor to be used to query the next batch of results. The wrapper is the MinQuery type which is very similar to mgo.Query but it supports specifying MongoDB's min via the MinQuery.Cursor() method.

The above solution using minquery looks like this:

q := minquery.New(session.DB(""), "users", bson.M{"country" : "USA"}).
    Sort("name", "_id").Limit(10)
// If this is not the first page, set cursor:
// getLastCursor() represents your logic how you acquire the last cursor.
if cursor := getLastCursor(); cursor != "" {
    q = q.Cursor(cursor)
}

var users []*User
newCursor, err := q.All(&users, "country", "name", "_id")

And that's all. newCursor is the cursor to be used to fetch the next batch.

Note #1: When calling MinQuery.All(), you have to provide the names of the cursor fields, this will be used to build the cursor data (and ultimately the cursor string) from.

Note #2: If you're retrieving partial results (by using MinQuery.Select()), you have to include all the fields that are part of the cursor (the index entry) even if you don't intend to use them directly, else MinQuery.All() will not have all the values of the cursor fields, and so it will not be able to create the proper cursor value.

Owner
András Belicza
Earn $100 and help me by using my DigitalOcean ref link: https://m.do.co/c/7bdc66c1fe73
András Belicza
Comments
  • setting value : 1 skips result between sets

    setting value : 1 skips result between sets

    If I have documents 1, 2, 3, 4, 5 and I set a NewCursor global variable. Each time minquery.All is called with say, a limit of 2, the way the NewCursors is being set, it displays 1, 2 then skips to 4 and 5. Document 3 is missing. Setting the Skip Value back to 0 corrects this.

  • Supports max query in descending order, and supports returning paged query information - if there are more results

    Supports max query in descending order, and supports returning paged query information - if there are more results

    1. Fixed a bug that no result is given when Limit() hasn't been called (though it may seem strange to use min query without limit).
    2. Add another return value 'hasMore' for callers to determine if there are more results in db, in paged query situations.
    3. Supports max query when sorting descending, for example, querying latest events or messages in descending order.
  • add Collation functionality

    add Collation functionality

    Not sure if this is something worth adding back to core, but I ran into a use case where I would need to use / create a Collation for when querying data.

    If this is not really something that we believe should make it back to core, feel free to close the PR :).

  • Reverse Sort

    Reverse Sort

    Hi,

    My React app works fine until I do a reverse sort, I get the first page, but once I query the second page it crashes with:

    Cannot load value: could not find symbol value for Message.

    I can query hundreds of records ten at a time backwards, forwards, upside down and sideways, add the minus BOOM.

    Disclaimer: Please note I exaggerated about certain directions in the previous statement.

    Any thoughts

    Pierre

  • Added hint to support mongo 4.2.X

    Added hint to support mongo 4.2.X

    Starting in MongoDB 4.2, you must explicitly specify the particular index with the hint() method to run min() with the following exception: you do not need to hint if the find() query is an equality condition on the _id field { _id: }.

    In previous versions, you could run min() with or without explicitly hinting the index. If run without the hint in 4.0 and earlier, MongoDB selects the index using the fields in the indexBounds; however, if multiple indexes exist on same fields with different sort orders, the selection of the index may be ambiguous.

  • fix bug: when there is descending sorting, min query the second page does not work

    fix bug: when there is descending sorting, min query the second page does not work

    My case:

    db.msgs.createIndex({"read_flag": 1, "timestamp": -1, "_id": 1}) q := minquery.New(p.db, p.colNameMsgs, ff).Sort("read_flag", "-timestamp", "_id").Limit(int(req.MaxNum)) newCursor, err := q.All(&dbMsgs, "read_flag", "-timestamp", "_id")

    However, now result returned for the second page, where the first page is correct. Without "-timestamp" as condition and index, everything was right.

    After debugging the code, I found that in the 'All' function, the returned data stored in 'doc' variable does not contain any field named '-timestamp' but 'timestamp', so I guess it may be a fix to remove the first letter.

    Please check if this is reasonable. Thanks again.

  • allowDiskUse option

    allowDiskUse option

    dear icza: if select a large table, error may be occured like this:

    q.err:Executor error during find command :: caused by :: Sort excee ded memory limit of 104857600 bytes, but did not opt in to external sorting. Aborting operation. Pass allowDiskUsello wDiskUse:true to opt in.

    after deeply studying, i add the following code after minquery.go:166: {Name: "allowDiskUse", Value: true}, // added by runique, 20210310 then everything is ok!

    so i'd like to suggest you append the line as default option. thank you very much!

  • bug, index error

    bug, index error

    when I used index like “create_time.seconds” as a cursor field to query data, the program can't get the correct next page token, see the source code:

    cursorData[i] = bson.DocElem{Name: cf, Value: doc[cf]}
    

    It just assign "create_index.seconds" to doc and matching doc map, I think it should be look like this:

    if cf == "create_time.seconds" {
        sonM, _ := doc["create_time"].(bson.M)
        cursorData[i] = bson.DocElem{Name: cf, Value: sonM["seconds"]}
    } else {
        cursorData[i] = bson.DocElem{Name: cf, Value: doc[cf]}
    }
    

    Just a example~

  • Return Page Count and Current Page

    Return Page Count and Current Page

    There may already be a way to deduce this from the cursor, I'm not sure, but it would be really nice if the minquery.All function would also return some meta-data about the result set such as the total number of pages matching the query criteria along with the current page that is represented in the returned slice of results. This can be accomplished currently by running a second mgo.Find to simply count the data set, and to pass the current page number back and forth with the client, but that seems "ugly". Maybe there's already a better way to do that, I don't know.

Mongo Go Models (mgm) is a fast and simple MongoDB ODM for Go (based on official Mongo Go Driver)
Mongo Go Models (mgm) is a fast and simple MongoDB ODM for Go (based on official Mongo Go Driver)

Mongo Go Models Important Note: We changed package name from github.com/Kamva/mgm/v3(uppercase Kamva) to github.com/kamva/mgm/v3(lowercase kamva) in v

Jan 2, 2023
The MongoDB driver for Go

The MongoDB driver for Go This fork has had a few improvements by ourselves as well as several PR's merged from the original mgo repo that are current

Jan 8, 2023
The Go driver for MongoDB
The Go driver for MongoDB

MongoDB Go Driver The MongoDB supported driver for Go. Requirements Installation Usage Bugs / Feature Reporting Testing / Development Continuous Integ

Dec 31, 2022
Simple key-value store abstraction and implementations for Go (Redis, Consul, etcd, bbolt, BadgerDB, LevelDB, Memcached, DynamoDB, S3, PostgreSQL, MongoDB, CockroachDB and many more)

gokv Simple key-value store abstraction and implementations for Go Contents Features Simple interface Implementations Value types Marshal formats Road

Dec 24, 2022
Data access layer for PostgreSQL, CockroachDB, MySQL, SQLite and MongoDB with ORM-like features.
Data access layer for PostgreSQL, CockroachDB, MySQL, SQLite and MongoDB with ORM-like features.

upper/db is a productive data access layer (DAL) for Go that provides agnostic tools to work with different data sources

Jan 3, 2023
💲 Golang, Go Fiber, RabbitMQ, MongoDB, Docker, Kubernetes, GitHub Actions
💲 Golang, Go Fiber, RabbitMQ, MongoDB, Docker, Kubernetes, GitHub Actions

Bank Projeto para simular empréstimos financeiros em um banco para clientes Tecnologias Utilizadas Golang MongoDB RabbitMQ Github Actions Docker Hub D

Dec 9, 2022
Examples and code to assign a name to your MongoDB, MySQL, PostgreSQL, RabbitMQ, and redis connection.
Examples and code to assign a name to your MongoDB, MySQL, PostgreSQL, RabbitMQ, and redis connection.

your connection deserves a name ?? When your app interacts with an external system, assign a name to the connection. An external system in this contex

Dec 14, 2022
A MongoDB compatible embeddable database and toolkit for Go.
A MongoDB compatible embeddable database and toolkit for Go.

lungo A MongoDB compatible embeddable database and toolkit for Go. Installation Example Motivation Architecture Features License Installation To get s

Jan 3, 2023
Go-odm, a Golang Object Document Mapping for MongoDB.
Go-odm, a Golang Object Document Mapping for MongoDB.

A project of SENROK Open Source Go ODM Go-odm, a Golang Object Document Mapping for MongoDB. Table of contents Features Installation Get started Docum

Nov 4, 2022
Golang MongoDB Integration Examples

Get Program Get a copy of the program: git clone https://github.com/hmdhszd/Go

Feb 1, 2022
Redis client Mock Provide mock test for redis query

Redis client Mock Provide mock test for redis query, Compatible with github.com/go-redis/redis/v8 Install Confirm that you are using redis.Client the

Dec 27, 2022
pogo is a lightweight Go PostgreSQL internal state query engine.

pogo is a lightweight Go PostgreSQL internal state query engine. It focuses on the data that are highly dynamic in nature, and provides some conv

Sep 19, 2021
Blog-mongodb - this repository for educational purpose, learn how to use mongodb and use mongodb with go

ENDPOINT ENDPOINT METHOD ACCESS /register POST all /login POST all /articles GET all /articles POST all /articles/{articleId} GET all /articles/{artic

Jan 4, 2022
It is a clone of the CRUD operations on Instagram which can create, get, create posts and get the post along with pagination
It is a clone of the CRUD operations on Instagram which can create, get, create posts and get the post along with pagination

Instagram-API-Clone It is a basic version of a RESTful API based on Instagram where we can create user, get the users, create post and get post and ge

Jan 25, 2022
[WIP] Basic Echo CRUD template (no pagination)

echo-crud-template [WIP] Basic Echo CRUD template (no pagination) Overview Based on https://github.com/xesina/golang-echo-realworld-example-app. Echo

Jan 11, 2022
This codebase was created to demonstrate a fully fledged fullstack application built with Golang/Echo including CRUD operations, authentication, routing, pagination, and more.
This codebase was created to demonstrate a fully fledged fullstack application built with Golang/Echo including CRUD operations, authentication, routing, pagination, and more.

This codebase was created to demonstrate a fully fledged fullstack application built with Golang/Echo including CRUD operations, authentication, routing, pagination, and more.

Jan 6, 2023
Go-mongodb - Practice Go with MongoDB because why not

Practice Mongo DB with Go Because why not. Dependencies gin-gonic go mongodb dri

Jan 5, 2022
A tool to run queries in defined frequency and expose the count as prometheus metrics. Supports MongoDB and SQL
A tool to run queries in defined frequency and expose the count as prometheus metrics. Supports MongoDB and SQL

query2metric A tool to run db queries in defined frequency and expose the count as prometheus metrics. Why ? Product metrics play an important role in

Jul 1, 2022
Mux is a simple and efficient route distributor that supports the net/http interface of the standard library.

Mux Mux is a simple and efficient route distributor that supports the net/http interface of the standard library. Routing data is stored in the prefix

Dec 12, 2022
Simple query builder for MongoDB

?? greenleaf - simple, type safe and easy to use query builder for MongoDB Installation To install use: go get github.com/slavabobik/greenleaf Quick

Nov 27, 2022