Paginate does not work together with PostgreSQL queries containing "DISTINCT ON"

[schuerg]

Example TypeORM Query with working expected results:

// Getting all Res1 records for a user with ${username} but only for the latest series identified with `seriesId`
        const typeormQuery = getRepository(Res1)
            .createQueryBuilder('res')
            .distinctOn(['res.seriesId'])
            .where('res.username = :username', { username })
            .orderBy({
                'res.seriesId': 'DESC',
                'res.created_at': 'DESC',
            })

Running the paginate() function of this lib with this query results in the following error:

QueryFailedError: column "res.seriesId" must appear in the GROUP BY clause or be used in an aggregate function

It seems paginate constructs GROUP BY clause conflicting with DISTINCT ON

[YuriyLyukov]

Example TypeORM Query with working expected results:

// Getting all Res1 records for a user with ${username} but only for the latest series identified with `seriesId`
        const typeormQuery = getRepository(Res1)
            .createQueryBuilder('res')
            .distinctOn(['res.seriesId'])
            .where('res.username = :username', { username })
            .orderBy({
                'res.seriesId'': 'DESC',
                'res.created_at': 'DESC',
            })

Running the paginate() function of this lib with this query results in the following error:

QueryFailedError: column "res.seriesId" must appear in the GROUP BY clause or be used in an aggregate function

It seems paginate constructs GROUP BY clause conflicting with DISTINCT ON

Not sure if it will help you, but as i see, you have a redundant single quote symbol (') near "res.seriesId".