typeorm: Can't use take and skip alongside order by in querybuilder with joined entities

Issue type:

[ ] question [x] bug report [ ] feature request [ ] documentation issue

Database system/driver:

[ ] cordova [ ] mongodb [ ] mssql [x] mysql / mariadb [ ] oracle [ ] postgres [ ] cockroachdb [ ] sqlite [ ] sqljs [ ] react-native [ ] expo

TypeORM version:

[x] latest [ ] @next [ ] 0.x.x (or put your version here)

Steps to reproduce or a small repository showing the problem:

I’m facing the same issue of #2771, #2912 and #3356. I noticed that in all the queries I try to do that involve any type of join, the orderBy statement can’t be used with take and/or skip methods. The query presented works if I delete the take/skip callings or alternatively the orderBy call. If I try to make a pagination and calling the orderBy method it’s thrown an error.

Since I log every query made to my mariadb database I came up with a workaround. It’s very dirty but at the moment I will go on with that. I hope in a fix soon.

Let’s start. My entities are: subscription, subscriptionType and subscriptionTypeFeatures. The relations are: 1 to many subscription - subscriptionType and 1 to many subscriptionType - subscriptionTypeFeatures.

I wrote this querybuilder:

getAll(args: {} = {}): Promise<Subscription[]> {
    let qb = this.subscriptionsRepository.createQueryBuilder('subscription')
        .leftJoinAndSelect('subscription.subscriptionType', 'subscriptionType')
        .leftJoinAndSelect('subscriptionType.features', 'feature')
        .take(args['take'])
        .skip(args['skip']);
    return qb.orderBy({
        date: "ASC",
        expirationDate: "ASC",
        ...args['orderBy'],
        "feature.priority": "DESC",
        "feature.feature": "ASC"
    }).getMany();
}

This produce the error [Nest] 40100 - 11/03/2020, 16:17:05 [ExceptionsHandler] ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ' , `distinctAlias`.`feature_priority`, `distinctAlias`.`feature_feature` FROM (S' at line 1 +2466ms QueryFailedError: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ' , `distinctAlias`.`feature_priority`, `distinctAlias`.`feature_feature` FROM (S' at line 1 at new QueryFailedError (node_modules\typeorm\error\QueryFailedError.js:11:28) at Query.<anonymous> (node_modules\typeorm\driver\mysql\MysqlQueryRunner.js:170:45) at Query.<anonymous> (node_modules\mysql\lib\Connection.js:526:10) at Query._callback (node_modules\mysql\lib\Connection.js:488:16) at Query.Sequence.end (node_modules\mysql\lib\protocol\sequences\Sequence.js:83:24) at Query.ErrorPacket (node_modules\mysql\lib\protocol\sequences\Query.js:92:8) at Protocol._parsePacket (node_modules\mysql\lib\protocol\Protocol.js:291:23) at Parser._parsePacket (node_modules\mysql\lib\protocol\Parser.js:433:10) at Parser.write (node_modules\mysql\lib\protocol\Parser.js:43:10) at Protocol.write (node_modules\mysql\lib\protocol\Protocol.js:38:16)

SQL analysis

If we take a look at the query executed there is SELECT DISTINCT `distinctAlias`.`subscription_id` as "ids_subscription_id", , , `distinctAlias`.`feature_priority`, `distinctAlias`.`feature_feature` FROM (SELECT `subscription`.`id` AS `subscription_id`, `subscription`.`date` AS `subscription_date`, `subscription`.`expirationDate` AS `subscription_expirationDate`, `subscription`.`subscriptionTypeId` AS `subscription_subscriptionTypeId`, `subscription`.`userId` AS `subscription_userId`, `subscriptionType`.`id` AS `subscriptionType_id`, `subscriptionType`.`name` AS `subscriptionType_name`, `subscriptionType`.`duration` AS `subscriptionType_duration`, `subscriptionType`.`price` AS `subscriptionType_price`, `subscriptionType`.`color` AS `subscriptionType_color`, `subscriptionType`.`description` AS `subscriptionType_description`, `subscriptionType`.`active` AS `subscriptionType_active`, `feature`.`id` AS `feature_id`, `feature`.`feature` AS `feature_feature`, `feature`.`priority` AS `feature_priority`, `feature`.`subscriptionTypeId` AS `feature_subscriptionTypeId` FROM `subscription` `subscription` LEFT JOIN `subscription_type` `subscriptionType` ON `subscriptionType`.`id`=`subscription`.`subscriptionTypeId` LEFT JOIN `subscription_type_feature` `feature` ON `feature`.`subscriptionTypeId`=`subscriptionType`.`id`) `distinctAlias` ORDER BY date ASC, expirationDate ASC, `distinctAlias`.`feature_priority` DESC, `distinctAlias`.`feature_feature` ASC, `subscription_id` ASC LIMIT 1 OFFSET 1

There are produced two errors: the first is the one reported SELECT DISTINCT `distinctAlias`.`subscription_id` as "ids_subscription_id", , , `distinctAlias`.`feature_priority`, `distinctAlias`.`feature_feature` that for some reasons there are empty fields.

The second it’s more important and it’s the reason why take/skip doesn’t work with order. The order statement is placed outside the main query [...] LEFT JOIN ... ON ...) `distinctAlias` ORDER BY date ASC, [... other order fields...], `subscription_id` ASC LIMIT 1 OFFSET 1 the closed parenthesis should be after the order clause and not before to be valid.

Workaround

Using the sql construct I came up with this workaround:

getAll(args: {} = {}): Promise<Subscription[]> {
    let qb = this.subscriptionsRepository.createQueryBuilder('subscription')
        .leftJoinAndSelect('subscription.subscriptionType', 'subscriptionType')
        .leftJoinAndSelect('subscriptionType.features', 'feature');
    qb = qb.orderBy({
        date: "ASC",
        expirationDate: "ASC",
        ...args['orderBy'],
        "feature.priority": "DESC",
        "feature.feature": "ASC"
    });
    if (args['take'] || args['skip']) {
        qb = qb.select('subscription.id').distinct(true);
        if (args['take']) qb.limit(args['take']);
        if (args['skip']) qb.offset(args['skip']);
        qb = this.subscriptionsRepository.createQueryBuilder('subscription')
            .innerJoin(`(${qb.getSql()})`, "filteredSubscriptions", "filteredSubscriptions.subscription_id = subscription.id")
            .leftJoinAndSelect('subscription.subscriptionType', 'subscriptionType')
            .leftJoinAndSelect('subscriptionType.features', 'feature')
            .orderBy({
                date: "ASC",
                expirationDate: "ASC",
                ...args['orderBy'],
                "feature.priority": "DESC",
                "feature.feature": "ASC"
            });
    }
    return qb.getMany();
}

So, I don’t use anymore take and skip, instead I create a subquery in which I use limit and offset and I select only the ids of my main entity.

In the main query I will have to do the joins, the ordering and a where statement in which I filter only the ids limited in the subquery (since I am using mariadb that doesn’t support limit in where in I have to use the inner join, they are both equivalent for the result).

To generalize the concept, if we have also a where statement to do, we have to put it only in the subquery that’s the one which do all the work. The main one doesn’t have to filter anything except the ids.

I hope my explanation was clear enough. If you need more code, like the entities, I can provide them but I think it’s pretty clear what it’s happening. I tested the queries on multiple cases and the behavior of the errors and the queries is always the same.