typeOrmOrderByPaginationBug.ts

import { Column, createConnection as createTypeOrmConnection, DefaultNamingStrategy, Entity, getConnection, ManyToOne, MigrationInterface, OneToMany, PrimaryGeneratedColumn, QueryRunner } from "typeorm";

function snakeCase(str: string) {
    return str.replace(/(?:([a-z])([A-Z]))|(?:((?!^)[A-Z])([a-z]))/g, '$1_$3$2$4').toLowerCase();
}

export class SnakeCaseNamingStrategy extends DefaultNamingStrategy {

    tableName(targetName: string, userSpecifiedName: string) {
        return userSpecifiedName ? userSpecifiedName : snakeCase(targetName);
    }

    columnName(propertyName: string, customName: string, embeddedPrefixes: any) {
        return snakeCase(embeddedPrefixes.concat(customName ? customName : propertyName).join('_'));
    }

    columnNameCustomized(customName: string) {
        return customName;
    }

    relationName(propertyName: string) {
        return snakeCase(propertyName);
    }
}


@Entity('authors')
export class Author {

    @PrimaryGeneratedColumn()
    id: number;

    @Column()
    name: string;

    @OneToMany(type => Post, post => post.author)
    posts: Post[];
}

@Entity('posts')
export class Post {

    @PrimaryGeneratedColumn()
    id: number;

    @Column({ name: 'title' })
    title: string;

    @Column()
    text: string;

    @ManyToOne(type => Author, author => author.posts)
    author: Author;

}

export class DataFilling1577087002356 implements MigrationInterface {

    async up(queryRunner: QueryRunner): Promise<any> {
        await queryRunner.query(`INSERT INTO "authors" (id,name) 
                                      VALUES 
                                        (1,'Ursula Manning'),
                                        (2,'Carly Butler'),
                                        (3,'Maggy Juarez'),
                                        (4,'Colette Murray'),
                                        (5,'Emmanuel Salinas')`);
        await queryRunner.query(`INSERT INTO "posts" (id,title,text,author_id) 
                                      VALUES 
                                        (1,'Ultricies Sem Ltd','quam. Curabitur vel lectus. Cum',1),
                                        (2,'Dolor Consulting','mauris ipsum porta elit, a',2),
                                        (3,'Feugiat Associates','tincidunt. Donec vitae erat',3),
                                        (4,'Ipsum Phasellus LLC','ut eros non enim commodo hendrerit. Donec porttitor',4),
                                        (5,'Habitant Morbi Tristique Corp.','Suspendisse',5),
                                        (6,'Nunc Interdum Feugiat LLC','elit, pellentesque a, facilisis non, bibendum sed, est.',1),
                                        (7,'Sapien Industries','dictum. Proin eget',2),
                                        (8,'In Hendrerit Consectetuer Foundation','vulputate, nisi sem semper erat,',3),
                                        (9,'Odio Aliquam Vulputate Foundation','velit eget',4),
                                        (10,'Cursus Ltd','ac mattis ornare, lectus ante dictum mi, ac mattis velit',5)`);
    }

    async down(queryRunner: QueryRunner): Promise<any> { }
}

function createConnection(entities: any[] = [], migrations: any[] = [], dbConfig: any) {
    const entitiesArray = []
        .concat(entities);
    const migrationsArray = ['migrations/*.js'].concat(migrations);

    return new Promise((resolve, reject) => {
        try {
            const conf = {
                migrationsTableName: '_migrations',
                migrations: migrationsArray,
                autoSchemaSync: true,
                synchronize: true,
                migrationsRun: true,
                entities: entitiesArray,
                namingStrategy: new SnakeCaseNamingStrategy(),

                ...dbConfig,
            };
            createTypeOrmConnection(conf).then((connection) => {
                resolve(connection);
            }).catch((e) => {
                reject(e);
            });

        } catch (e) {
            return reject(e);
        }
    });
}

(async function () {

    await createConnection(
        [
            Author,
            Post
        ],
        [DataFilling1577087002356],
        {
            type: "sqlite",
            database: "db.db",
            logging: true,
            synchronize: true
        });


    const postRepo = getConnection().getRepository(Post)
    const queryBuilder = postRepo.createQueryBuilder('Post')
    let data = await queryBuilder
        .select(['Post.id'])
        .leftJoinAndSelect('Post.author', 'author31', '1=1', {})
        .addOrderBy('Post.title', 'ASC')
        .skip(3)
        .take(3)
        .getMany()

    console.log(data)
})()