eclass / sequelize-paginate Goto Github PK

View Code? Open in Web Editor NEW

70.0 4.0 19.0 1.72 MB

Sequelize model plugin for add paginate method

License: MIT License

JavaScript 100.00%

sequelize pagination eclass

sequelize-paginate's Introduction

sequelize-paginate

Sequelize model plugin for add paginate method

Installation

npm i sequelize-paginate

Use

// model.js
const sequelizePaginate = require('sequelize-paginate')

module.exports = (sequelize, DataTypes) => {
  const MyModel = sequelize.define(
    'MyModel',
    {
      name: { type: DataTypes.STRING(255) }
    }
  )
  sequelizePaginate.paginate(MyModel)
  return MyModel
}

// controller.js
const { Op } = db.sequelize
// Default page = 1 and paginate = 25
const { docs, pages, total } = await db.MyModel.paginate()
// Or with extra options
const options = {
  attributes: ['id', 'name'],
  page: 1, // Default 1
  paginate: 25, // Default 25
  order: [['name', 'DESC']],
  where: { name: { [Op.like]: `%elliot%` } }
}
const { docs, pages, total } = await db.MyModel.paginate(options)

NOTE: If options include limit or offset are ignored.

License

MIT

sequelize-paginate's People

Contributors

Stargazers

Watchers

Forkers

rodrigoespinozadev vandaimer dsmackie mrofia citius novatics bodasia inventae leandrofinger fredckl abelovq haxploiteable alexanderc leguass7 orso-jp jasonliao anayanirasanms noelzappy

sequelize-paginate's Issues

custom name for 'docs' property

Hi,
it's possible to change the property docs to a custom name?
I would have put "rows", it seems more appropriate.

When use the "order" options, duplicates values

When I try to paginate using the order options, it is duplicating some results and doesn't show others...

How to retrieve a page containing a particular item (in the middle of a result set)

Thanks for making this library! I've found it super useful :)

Here's my use case:
A user clicks to a video in the middle of a playlist of thousands of videos. I only want to send back the page that includes their video. Then, as they scroll up the playlist, I'll send back previous pages, and as they scroll down I'll send later pages.

Not sure if an option like that exists in this project, here's how I would imagine something like that working:

  const videoId = ARBITRARY_POSITION_IN_PLAYLIST
  const options = {  
    where: { playlistId }, 
    // some option like:
    page: { includes: { id: videoId} }
  };
  const { docs, pages, total, currentPage } = await Videos.paginate(options);
  // also needs to get the "currentPage" of the item, 
  // so the client knows how to grab prev/next item

Is there any type of api for something like this^^? Or will I have to write something custom?

Thanks!
-Joe

Count query breaks when using nested property in where clause

As the property include is removed for the count query, it breaks when we use a nested property in where clause.

I've tried to create a Pull Request, but I didn't get the correct commit message format. Follow my commit link: leandrofinger@e74f00a

Count includes records regardless of include

When a query has an include with a where condition, the result set matches the query but the total record count includes all available records.

let articles = Articles.paginate({
  where: {
    active: true
  },
  include: {
    model: Organisations,
    where: {
      id: 1
    }
  }
});

The above will return the correct records, but count ignores the include where criteria and returns the count of all records.

Line https://github.com/eclass/sequelize-paginate/blob/master/src/index.js#L50 appears to be responsible.

Doesn't factor in grouping / relations

If I have a grouping/relations clause in my SQL, the pagination shows a total based on raw rows fetched, not final items returned.

It looks like the count doesn't factor in the grouping clauses that are made by Sequelize in this instance.

so if I have 10 events each with 3 different dates assigned to them, for example:

const { Availability } = connection.models
  return {
    model: Availability,
    as: 'availability'
  }

I end up with 30 total and 3 pages of 10, rather than 10 total and 1 page.

Dependency Dashboard

This issue lists Renovate updates and detected dependencies. Read the Dependency Dashboard docs to learn more.

Rate-Limited

These updates are currently rate-limited. Click on a checkbox below to force their creation now.

Edited/Blocked

These updates have been manually edited so Renovate will no longer make changes. To discard all commits and start over, click on a checkbox.

Open

These updates have all been created already. Click a checkbox below to force a retry/rebase of any.

chore(deps): update dependency sequelize to v6 [security]
chore(deps): update commitlint monorepo to v8.3.6 (@commitlint/cli, @commitlint/config-conventional, @commitlint/prompt-cli)
chore(deps): update dependency @types/sequelize to v4.28.20
chore(deps): update commitlint monorepo to v19 (major) (@commitlint/cli, @commitlint/config-conventional, @commitlint/prompt-cli)
chore(deps): update semantic-release monorepo (major) (@semantic-release/changelog, @semantic-release/git, @semantic-release/github, @semantic-release/npm)
Click on this checkbox to rebase all open PRs at once

Detected dependencies

docker-compose

docker-compose.yml

mysql 5.7.34

npm

package.json

@commitlint/cli 8.3.5

@commitlint/config-conventional 8.3.4

@commitlint/prompt-cli 8.3.5

@semantic-release/changelog 3.0.6

@semantic-release/git 7.0.18

@semantic-release/github 5.5.8

@semantic-release/npm 5.3.5

@types/sequelize 4.28.9

chai 4.3.4

eslint 5.16.0

eslint-config-jsdoc-strict 2.0.0

eslint-config-prettier 6.15.0

eslint-config-standard 12.0.0

eslint-plugin-import 2.22.1

eslint-plugin-jsdoc 4.8.4

eslint-plugin-node 10.0.0

eslint-plugin-promise 4.3.1

eslint-plugin-security 1.4.0

eslint-plugin-standard 4.1.0

husky 3.1.0

jsdoc 3.6.6

lint-staged 8.2.1

lodash.range 3.2.0

mocha 6.2.3

mysql2 1.7.0

npm-github-config 2.0.1

nyc 14.1.1

nyc-config-common 1.0.1

prettier-standard 15.0.1

promise-sequential 1.1.1

semantic-release 15.14.0

sequelize 5.22.4

travis-deploy-once 5.0.11

tsd-jsdoc 2.5.0

tslint 5.20.1

tslint-config-prettier 1.18.0

tslint-config-standard 8.0.1

typescript 3.9.9

node >=8

nvm

.nvmrc

node v10

travis

.travis.yml

node 10

node 10

node 8

Check this box to trigger a request for Renovate to run again on this repository

Fails to acknowledge scope

Pagination is forced to the default model. Any attempt to scope prior to pagination is ignored.

See PR #45

Action Required: Fix Renovate Configuration

There is an error with this repository's Renovate configuration that needs to be fixed. As a precaution, Renovate will stop PRs until it is resolved.

Location: package.json
Error type: The renovate configuration file contains some invalid settings
Message: Configuration option 'packageRules[0].node' should be a json object, Invalid configuration option: author, Invalid configuration option: bugs, Invalid configuration option: commitlint, Invalid configuration option: eslintConfig, Invalid configuration option: eslintIgnore, Invalid configuration option: homepage, Invalid configuration option: husky, Invalid configuration option: keywords, Invalid configuration option: license, Invalid configuration option: lint-staged, Invalid configuration option: main, Invalid configuration option: name, Invalid configuration option: nyc, Invalid configuration option: packageRules[2].@commitlint/cli, Invalid configuration option: packageRules[2].@commitlint/config-conventional, Invalid configuration option: packageRules[2].@commitlint/prompt-cli, Invalid configuration option: packageRules[2].@semantic-release/changelog, Invalid configuration option: packageRules[2].@semantic-release/git, Invalid configuration option: packageRules[2].@semantic-release/github, Invalid configuration option: packageRules[2].@semantic-release/npm, Invalid configuration option: packageRules[2].@types/sequelize, Invalid configuration option: packageRules[2].chai, Invalid configuration option: packageRules[2].eslint, Invalid configuration option: packageRules[2].eslint-config-jsdoc-strict, Invalid configuration option: packageRules[2].eslint-config-prettier, Invalid configuration option: packageRules[2].eslint-config-standard, Invalid configuration option: packageRules[2].eslint-plugin-import, Invalid configuration option: packageRules[2].eslint-plugin-jsdoc, Invalid configuration option: packageRules[2].eslint-plugin-node, Invalid configuration option: packageRules[2].eslint-plugin-promise, Invalid configuration option: packageRules[2].eslint-plugin-security, Invalid configuration option: packageRules[2].eslint-plugin-standard, Invalid configuration option: packageRules[2].husky, Invalid configuration option: packageRules[2].jsdoc, Invalid configuration option: packageRules[2].lint-staged, Invalid configuration option: packageRules[2].lodash.range, Invalid configuration option: packageRules[2].mocha, Invalid configuration option: packageRules[2].mysql2, Invalid configuration option: packageRules[2].npm-github-config, Invalid configuration option: packageRules[2].nyc, Invalid configuration option: packageRules[2].nyc-config-common, Invalid configuration option: packageRules[2].prettier-standard, Invalid configuration option: packageRules[2].promise-sequential, Invalid configuration option: packageRules[2].semantic-release, Invalid configuration option: packageRules[2].sequelize, Invalid configuration option: packageRules[2].travis-deploy-once, Invalid configuration option: packageRules[2].tsd-jsdoc, Invalid configuration option: packageRules[2].tslint, Invalid configuration option: packageRules[2].tslint-config-prettier, Invalid configuration option: packageRules[2].tslint-config-standard, Invalid configuration option: packageRules[2].typescript, Invalid configuration option: release, Invalid configuration option: renovate, Invalid configuration option: scripts, Invalid configuration option: types, Invalid configuration option: version, The "node" object can only be configured at the top level of a config but was found inside "packageRules[0]"

problem when including a one-to-many relationship.

For example, say I have defined one Author - many Books.
when I do

return Author.paginate({
      include: [
        {
          model: Book,
          attributes: ['Title', 'Description'],
          where: // where object here
        }
      ],
      attributes: ['id', 'Name'],
      page: req.query.page,
      paginate: 25,
      order: [['id', 'DESC']],
      where: // where object here
    })
      .then(...)

It doesn't return the correct # of pages. Say in this case the returned pages = 100,
But when I actually go to /api/author?page=100, it will either error out or return empty.

Originally posted by @broodfusion in https://github.com/_render_node/MDEyOklzc3VlQ29tbWVudDQ0MzA5ODg5OQ==/timeline/issue_comment#issuecomment-443098899

Typescript usage

How can I use this package with TS?

Thanks.

"total" shows the total records in the table instead of the total records returned after "where" clause

Please consider the code below:

const options = {
            page: req.query.page || 1,
            paginate: 10,
            order: [['id', 'DESC']],
            where: { course_id:  2}
        }

        const { docs, pages, total } = await studentModel.paginate(options)

When I console log total, it returns 90, instead it should return the record count 25 which is the count after i apply the where clause.

Is it a bug? How do I fix this?

Probably needs a warning that `limit` and `offset` are blatted

Thanks for the plugin!

I think it would be really useful if using limit/offset caused a warning if set. The fact they are overridden without warning can cause some unpredictability for an end user.

how to get next page url

I am wondering how to implement this pagination on the front end by retrieving the next page url to be called when someone click on next from the frontend

broken query on using aggregate function

Basically I need the pagination to still work just like when using .findAll
However the query behaved erratically when using .paginate instead (The joins are moved over subquery, ordering options duplicated, as well as the grouping getting out of scope.

with the .paginate commented, The query defined below works okay:

const model = require("../models");

module.exports = async (user, options) => {
    //console.log("get all courses");
    try {

        let query = {
            include: [
                {
                    model: model.Category,
                    nested: true,
                    attributes: ['id', 'name', [Sequelize.fn('count', Sequelize.col('AcademyCourseCategory->ac2->Enrollments.id')), 'countEnrollCategory']],
                    include: [
                        {
                            model: model.Course,
                            as: 'ac2',
                            attributes: [],
                            include: [
                                {
                                    as: 'Enrollments',
                                    model: model.Enrollment,
                                    attributes: [],
                                    where: {}
                                }
                            ]
                        }
                    ]
                },
                {
                    as: 'CreatorUser',
                    model: model.User,
                    nested: true,
                    attributes: [
                        'id', 'name', 'email', 'biography', 'avatar', 'status'
                    ]
                }
            ],
            where: {
                //TO DO: EXCLUDE taken courses
            },
            order: [
                //[Sequelize.fn('count', Sequelize.col('AcademyCourseCategory->ac2->Enrollments.id')), 'DESC'],
                ['count_enrollment', 'DESC']
            ],
            group: ['AcademyCourse.id', 'AcademyCourseCategory.id', 'CreatorUser.id']
        };

        if (options.dtStart && options.dtEnd) {
            query.where.dateAdded = {
                [Sequelize.Op.gte]: new Date(options.dtStart),
                [Sequelize.Op.lte]: new Date(options.dtEnd)
            };
        }

        if (user) {
            if (user.role === 'Admin') {
                //do nothing? don't filter
            } else {
                //FILTER BY LOGGED IN USER ID
                query.include[0].include[0].include[0].where.userId = user.id;
            }
        }


        if (options.orders) {
            for (let i = 0; i < options.orders.length; i++) {

                let order = [options.orders[i][0], options.orders[i][1]];
                query.order.push(order);
            }
        }

        if (options.limit) {
            options.limit = (options.limit > 100) ? 100 : options.limit;
            query.paginate = options.limit;
        }

        if (options.page) query.page = options.page;
        //const {docs, pages, total} = await model.Course.paginate(query);

        const pages=1;
        const total=12;

        const docs = await model.Course.findAll(query);
        return {
            pages: pages,
            total: total,
            data: docs
        };

    } catch (err) {
        throw err;

    }

};

The query produced using paginate:

SELECT "AcademyCourse".*,
       "AcademyCourseCategory"."id"                          AS "AcademyCourseCategory.id",
       "AcademyCourseCategory"."name"                        AS "AcademyCourseCategory.name",
       count("AcademyCourseCategory->ac2->Enrollments"."id") AS "AcademyCourseCategory.countEnrollCategory",
       "CreatorUser"."id"                                    AS "CreatorUser.id",
       "CreatorUser"."name"                                  AS "CreatorUser.name",
       "CreatorUser"."email"                                 AS "CreatorUser.email",
       "CreatorUser"."biography"                             AS "CreatorUser.biography",
       "CreatorUser"."avatar"                                AS "CreatorUser.avatar",
       "CreatorUser"."status"                                AS "CreatorUser.status"
FROM (SELECT "AcademyCourse"."id",
             "AcademyCourse"."title",
             "AcademyCourse"."slug",
             "AcademyCourse"."category",
             "AcademyCourse"."type",
             "AcademyCourse"."subtitle",
             "AcademyCourse"."description",
             "AcademyCourse"."goals",
             "AcademyCourse"."requirements",
             "AcademyCourse"."target_audience"    AS "targetAudience",
             "AcademyCourse"."topic",
             "AcademyCourse"."level",
             "AcademyCourse"."thumbnail",
             "AcademyCourse"."video",
             "AcademyCourse"."language",
             "AcademyCourse"."creator",
             "AcademyCourse"."organization_id"    AS "organizationId",
             "AcademyCourse"."count_section"      AS "countSection",
             "AcademyCourse"."count_enrollment"   AS "countEnrollment",
             "AcademyCourse"."count_lesson"       AS "countLesson",
             "AcademyCourse"."average_rating"     AS "averageRating",
             "AcademyCourse"."price_tier"         AS "priceTier",
             "AcademyCourse"."active_marketing"   AS "activeMarketing",
             "AcademyCourse"."estimated_duration" AS "estimatedDuration",
             "AcademyCourse"."status",
             "AcademyCourse"."date_added"         AS "dateAdded",
             "AcademyCourse"."last_modified"      AS "lastModified",
             "AcademyCourse"."active_marketing"   AS "PromoId",
             "AcademyCourse"."active_marketing"   AS "AcademyPromotionId"
      FROM "public"."academy_courses" AS "AcademyCourse"
      GROUP BY "AcademyCourse"."id", "AcademyCourse"."category", "AcademyCourse"."creator"
      ORDER BY "AcademyCourse"."count_enrollment" DESC
      LIMIT 25 OFFSET 0) AS "AcademyCourse"
       LEFT OUTER JOIN "public"."academy_course_categories" AS "AcademyCourseCategory"
                       ON "AcademyCourse"."category" = "AcademyCourseCategory"."id"
       LEFT OUTER JOIN ( "public"."academy_courses" AS "AcademyCourseCategory->ac2" INNER JOIN "public"."academy_course_enrollments" AS "AcademyCourseCategory->ac2->Enrollments" ON
    "AcademyCourseCategory->ac2"."id" = "AcademyCourseCategory->ac2->Enrollments"."course_id" )
                       ON "AcademyCourseCategory"."id" = "AcademyCourseCategory->ac2"."category"
       LEFT OUTER JOIN "public"."global_users" AS "CreatorUser" ON "AcademyCourse"."creator" = "CreatorUser"."id"
ORDER BY "countEnrollment" DESC;

The query produced using .findAll:

SELECT "AcademyCourse"."id",
       "AcademyCourse"."title",
       "AcademyCourse"."slug",
       "AcademyCourse"."category",
       "AcademyCourse"."type",
       "AcademyCourse"."subtitle",
       "AcademyCourse"."description",
       "AcademyCourse"."goals",
       "AcademyCourse"."requirements",
       "AcademyCourse"."target_audience"                     AS "targetAudience",
       "AcademyCourse"."topic",
       "AcademyCourse"."level",
       "AcademyCourse"."thumbnail",
       "AcademyCourse"."video",
       "AcademyCourse"."language",
       "AcademyCourse"."creator",
       "AcademyCourse"."organization_id"                     AS "organizationId",
       "AcademyCourse"."count_section"                       AS "countSection",
       "AcademyCourse"."count_enrollment"                    AS "countEnrollment",
       "AcademyCourse"."count_lesson"                        AS "countLesson",
       "AcademyCourse"."average_rating"                      AS "averageRating",
       "AcademyCourse"."price_tier"                          AS "priceTier",
       "AcademyCourse"."active_marketing"                    AS "activeMarketing",
       "AcademyCourse"."estimated_duration"                  AS "estimatedDuration",
       "AcademyCourse"."status",
       "AcademyCourse"."date_added"                          AS "dateAdded",
       "AcademyCourse"."last_modified"                       AS "lastModified",
       "AcademyCourse"."active_marketing"                    AS "PromoId",
       "AcademyCourse"."active_marketing"                    AS "AcademyPromotionId",
       "AcademyCourseCategory"."id"                          AS "AcademyCourseCategory.id",
       "AcademyCourseCategory"."name"                        AS "AcademyCourseCategory.name",
       count("AcademyCourseCategory->ac2->Enrollments"."id") AS "AcademyCourseCategory.countEnrollCategory",
       "CreatorUser"."id"                                    AS "CreatorUser.id",
       "CreatorUser"."name"                                  AS "CreatorUser.name",
       "CreatorUser"."email"                                 AS "CreatorUser.email",
       "CreatorUser"."biography"                             AS "CreatorUser.biography",
       "CreatorUser"."avatar"                                AS "CreatorUser.avatar",
       "CreatorUser"."status"                                AS "CreatorUser.status"
FROM "public"."academy_courses" AS "AcademyCourse"
       LEFT OUTER JOIN "public"."academy_course_categories" AS "AcademyCourseCategory"
                       ON "AcademyCourse"."category" = "AcademyCourseCategory"."id"
       LEFT OUTER JOIN ( "public"."academy_courses" AS "AcademyCourseCategory->ac2" INNER JOIN "public"."academy_course_enrollments" AS "AcademyCourseCategory->ac2->Enrollments" ON
    "AcademyCourseCategory->ac2"."id" = "AcademyCourseCategory->ac2->Enrollments"."course_id" )
                       ON "AcademyCourseCategory"."id" = "AcademyCourseCategory->ac2"."category"
       LEFT OUTER JOIN "public"."global_users" AS "CreatorUser" ON "AcademyCourse"."creator" = "CreatorUser"."id"
GROUP BY "AcademyCourse"."id", "AcademyCourseCategory"."id", "CreatorUser"."id"
ORDER BY "AcademyCourse"."count_enrollment" DESC;

The models are defined below:
models.zip

Relationships are defined in this file:
index.zip