Vincit / Vincit/objection.js

Postgres Pagination page() fetch one duplicate field if created_at value are the same

Open
#2,575 0 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Dominant language
JavaScript
Stars
7.3k
Forks
637
PR merge metrics
No merged PRs in 30d

Description

Objection js Version: "3.1.3"

It seems this error happen when there are more than 50 rows of field created_at with the same value when I use page(). And it only fetched one duplicate field between page 0 and page 1 with page_size 10, and using created_at('created_at', 'desc | asc'). I'm not sure how to host this reproducible code but here's the code

/**
 *
 * install:
 *    npm install objection knex 
 *    npm install pg --save
 *
 * run:
 *    node reproduction-template
 */

let Model;

try {
  Model = require('./').Model;
} catch (err) {
  Model = require('objection').Model;
}

const Knex = require('knex');

async function main() {
  await createSchema();

  ///////////////////////////////////////////////////////////////
  // Your reproduction
  ///////////////////////////////////////////////////////////////

  const structure = () => {
    return {
      name: 'test',
    };
  };
  const rise = generateData(structure, 50);
  await RiseModel.query().insert(rise)

  for(let i =0; i<3; i++){
    const data = await getData(i);
    console.log(data.results.map(e=>e.id));
  }
  
}

async function getData(page){
  return await RiseModel.query().orderBy("created_at", "desc").page(page, 10);
}

function generateData(
  structure,
  amount
){
  const data = [];
  for (let i = 0; i < amount; i++) {
    
    data.push(structure());
  }
  return data;
}

///////////////////////////////////////////////////////////////
// Database
///////////////////////////////////////////////////////////////

const knex = Knex({
  client: "pg",
  connection: {
      host: "127.0.0.1",
      database: "Rise",
      user: // YOUR USERNAME,
      password: // YOUR PASSWORD,
  },
  useNullAsDefault: true,
  debug: false,
});

Model.knex(knex);

///////////////////////////////////////////////////////////////
// Models
///////////////////////////////////////////////////////////////

class RiseModel extends Model {
  static get tableName() {
    return "rise";
  }
}
///////////////////////////////////////////////////////////////
// Schema
///////////////////////////////////////////////////////////////

async function createSchema() {
  await knex.schema
    .dropTableIfExists('rise');
    

  await knex.schema
    .createTable('rise', table => {
      table.increments('id').primary();
      table.string('name');
      table.timestamps(true, true);
    })
}

main()
  .then(async () => {
    console.log('success');
    await knex.schema.dropTable('rise');
    return knex.destroy();
  })
  .catch(err => {
    console.error(err);
    return knex.destroy();
  });

Here's the result of id fetched:

[
  2, 3, 4,  5, 6,
  7, 8, 9, 10, 1
]
[
  12, 13, 14, 15, 16,
  17, 18, 19, 20,  1
]
[
  21, 22, 23, 24, 25,
  26, 27, 28, 29, 30
]
success

Contributor guide

No contributing guide indexed for this repository

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start at the page() pagination implementation and run the supplied PostgreSQL reproduction with equal created_at values. Trace how consecutive pages are ordered, then add a regression test showing that page 0, page 1, and later pages contain no duplicate rows when timestamps tie.

Written by the indexing model from the issue text.

Assessment

Tech stack
javascript, node.js, postgresql
Domain
backend, database
Issue type
Bug
Difficulty
3/5
Estimated time
1-2 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
45/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.