typeorm: QueryBuilder produces wrong SQL with order by + nested fields, breaks pagination-related queries with skip() and take()

Issue type:

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

Database system/driver:

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

TypeORM version:

[ ] latest [ ] @next [x] 0.2.25

Steps to reproduce or a small repository showing the problem:

I am encountering unexpected behaviour with pagination-related skip() and take() methods using the query builder. It occurs when I add an order by clause to a joined field.

Suppose I have a query that returns 3 items with a given :accountId:

const query = this.createQueryBuilder('invoice')
  .innerJoin('invoice.account', 'account', 'account.id = :accountId', { accountId: user.account.id })
  .leftJoinAndSelect('invoice.items', 'items')
  .leftJoinAndSelect('invoice.client', 'client')
  .where('invoice.active = :active', { active: true })
  .addOrderBy('invoice.dueDate', 'ASC')

If I add skip(0) and take(2) I get the expected 2 items. If I query with getManyAndCount(), the count is the expected value of 3.

If I modify my query to sort the invoice line items based on a sort field, then skip() and take() no longer work as expected. See modified query below:

const query = this.createQueryBuilder('invoice')
  .innerJoin('invoice.account', 'account', 'account.id = :accountId', { accountId: user.account.id })
  .leftJoinAndSelect('invoice.items', 'items')
  .leftJoinAndSelect('invoice.client', 'client')
  .where('invoice.active = :active', { active: true })
  .addOrderBy('invoice.dueDate', 'ASC')
  .addOrderBy('items.sort', 'ASC') // new offending line

Note that the above order-by clause produces the desired result: the invoice line items are correctly sorted within each invoice.

However, if I add skip(0) and take(2) I now get an unexpected 1 item in the result. If I query with getManyAndCount(), the count is the expected value of 3.

If I use take(3) and take(4) I still get the unexpected 1 item in the result, again with the count showing the expected value of 3.

If I try take(5) I will get 2 items in the result. If I try take(6) I will finally get all 3 items.

I’m not too sure what’s going on or why this particular order-by clause is causing this issue. I’m relatively inexperienced with typeorm so there is a definite chance that I’m missing something.

About this issue

  • Original URL
  • State: open
  • Created 6 years ago
  • Reactions: 14
  • Comments: 26 (2 by maintainers)

Commits related to this issue

Most upvoted comments

Adding my take to the discussion:

  1. The simplest reproduction case:
await this.mainEntityRepository
      .createQueryBuilder('mainEntity')
      .leftJoin('mainEntity.joinedEntity', 'joinedEntity')
      .orderBy('joinedEntity.order', 'ASC')
      .take(<N>)
      .getMany();

(with multiple joinedEntitys per mainEntity)

  1. Analysing current query plan:
SELECT DISTINCT
	"distinctAlias"."mainEntity_id" as "ids_mainEntity_id",
    "distinctAlias"."joinedEntity_order"
FROM (
	SELECT "mainEntity"."id" AS "mainEntity_id", "joinedEntity"."order" AS "joinedEntity_order"
    FROM "mainEntity"
    LEFT JOIN "joinedEntity" ON "joinedEntity"."mainEntityId"="mainEntity"."id"
) "distinctAlias"
ORDER BY "distinctAlias"."joinedEntity_order" ASC, "mainEntity_id" ASC
LIMIT <N>;

SELECT "mainEntity".*, "joinedEntity".*
FROM "mainEntity" LEFT JOIN "joinedEntity" ON "joinedEntity"."recipeId"="mainEntity"."id"
WHERE "mainEntity"."id" IN (<N ids from previous query>)
ORDER BY "joinedEntity"."order" ASC;
  1. Suggestion of query plan:
SELECT "distinctAlias"."mainEntity_id" as "ids_mainEntity_id"
FROM (
	SELECT "mainEntity"."id" AS "mainEntity_id", "joinedEntity"."order" AS "joinedEntity_order"
    FROM "mainEntity"
    LEFT JOIN "joinedEntity" ON "joinedEntity"."mainEntityId"="mainEntity"."id"
) "distinctAlias"
GROUP BY "distinctAlias"."mainEntity_id"
ORDER BY min("distinctAlias"."joinedEntity_order") ASC, "mainEntity_id" ASC
LIMIT <N>;

SELECT "mainEntity".*, "joinedEntity".*
FROM "mainEntity" LEFT JOIN "joinedEntity" ON "joinedEntity"."recipeId"="mainEntity"."id"
WHERE "mainEntity"."id" IN (<N ids from previous query>)
ORDER BY "joinedEntity"."order" ASC;

Basically, replacing SELECT DISTINCT id, <other orderBy fields> FROM everything ORDER BY <all orderBy fields> by SELECT id FROM everything GROUP BY id ORDER BY <min of all orderBy fields>

  1. Discussion

I would be happy to submit a PR, but it would be awesome having feedbacks from typeorm core contributor(s) beforehand!

Thanks for the reading 😉

For anyone coming to this issue like the rest of us, after prolonged research (thanks typeorm for collectively wasting few months of my life research your idiosyncracies!) -here’s a detaile of the issue:

The issue seems to be using nested fields + joins + order by, more specifically it is related to this sequence:

  • Typeorm generates a distinctAlias query, this query uses the model/table primarykey/id for ordering (this is in the background and unchangeable)
  • When trying to order on a nested field, the select distinctAlias step only includes the id, along with a default order by that id
  • Therefore, when adding a nested field ordering, this renders the nested field ineffective

I just encountered this issue using findAndCount using typeorm@0.3.17. I wanted to sort joined entities by their primary key as they appear to be unsorted by default.

// example object graph
[
  {
    "id": 1,
    // nested is unsorted
    "nested": [{ "id": 5 }, { "id": 1 }, { "id": 3 }]
  }
]

When adding a nested sort order I no longer got the expected entries returned. In my test data set I got 7 entries whereas I was expecting 10.

// example call
findAndCount({
  order: { nested: { id: "ASC" } },
  skip: 0,
  take: 10,
  relations: {
    nested: true,
  },
})

If there is another way to sort nested relations when using findAndCount please let me know 😸

I inspected the generated SQL and believe I’ve spotted the issue.

-- 1st of 3 queries executed by typeorm
SELECT
    DISTINCT "distinctAlias"."Foo_id" AS "ids_Foo_id",
    "distinctAlias"."Foo**Foo_nested_id"
FROM
    (
        -- omitted for brevity
    ) "distinctAlias"
ORDER BY
    "distinctAlias"."Foo**Foo_nested_id" ASC,
    "Foo_id" ASC
LIMIT
    10

This is doing DISTINCT and LIMIT with the nested ids joined on. This is the problem. Here’s an example of the output. Notice that there are duplicates in Foo_id

Foo_id Foo**Foo_nested_id
10 null
16 2
4 15
4 18
4 22
15 17
6 null
21 7
21 23
14 null

This output is used in the 3rd and final query.

-- 3rd of 3 queries executed by typeorm
-- rest omitted for brevity
WHERE
    "Foo"."id" IN (10, 16, 4, 4, 4, 15, 6, 21, 21, 14) -- notice the duplicate ids

I don’t know how to fix this in typeorm though. But hope it can help fix the issue.

@Nilegfx now that we have a better handle on the issue (thanks everyone), I will rename it from original Unexpected behaviour with skip() and take() re queries with a particular order-by clause to QueryBuilder produces wrong SQL with order by + nested fields, breaks pagination-related queries with skip() and take() in hopes it helps get the issue more attention.

I’ve just encountered this bug too. Thank you firxworx for pointing out that the issue is with the orderby on nested field! In my case, I have a parent entity (left side of one to many), and a child entity with composite primary key (right side of one to many). One column of 2-column composite primary key references parent entity. I make the query builder as such:

const sessions = await sessionsRepository
  .createQueryBuilder('sessions')
  .leftJoinAndSelect('sessions.packets', 'packets')
  .select([
    'sessions.id',
    'packets.sequence',
    //... something else
  ])
  .where({
    userId: user.id,
  })
  .take(limit)
  .skip(offset)
  .orderBy('sessions.id', 'DESC')
  .addOrderBy('packets.sequence', 'ASC') // !!! THE ISSUE IS ON THIS LINE
  .getManyAndCount();

If I have the noted line in place, I see TypeORM produces the query:

SELECT DISTINCT "distinctAlias"."sessions_id" as "ids_sessions_id", "distinctAlias"."sessions_id", "distinctAlias"."packets_sequence" FROM ...

Which is incorrect: it selects primary column of parent entity AND BOTH primary columns of child entity, thus the result will have more than one row for one parent entity.

For comparison, without the noted line, here’s what TypeORM generates:

SELECT DISTINCT "distinctAlias"."sessions_id" as "ids_sessions_id", "distinctAlias"."sessions_id" FROM ...

Which is correct: it will give only one row per one parent entity (session).

The bug still persists

Hello there Im facing same issue here, using nextjs with typeorm v 0.3.10 but I did for testing and upgraded typeorm to latest version, and the same issue here is a workaround without using raw sql (i think is risky because sql injection), Context: I have trees table/entity that have many codes table/entity I’m using nestjs and with typeorm repository injection

const query = this.treesRepository.createQueryBuilder('trees');
    query
      .leftJoinAndSelect('trees.codes', 'codes')
      .where('trees.name LIKE :searchName', {searchName: `%some name%`}) //here just to show some parameters
      .orderBy('trees.name')
      .addOrderBy('codes.name');

    //Get total count
    const totalItems = await this.treesRepository.manager.connection.createQueryBuilder() // note creating queryBuilder from connection
    .select(`COUNT (DISTINCT subQ.tree_id) as itemCount`)
    .from(`(${query.getQuery()})`, 'subQ')
    .setParameters(query.getParameters())
    .getRawOne()
    .then((res) => {
      return parseInt(res.itemCount);
    });

    //select the trees desired
    await this.treesRepository.metadata.connection.createQueryBuilder()
      .select(`DISTINCT subQ.tree_id, subQ.tree_name`) // note you need the id, and the colum(s) that will be sorted
      .from(`(${query.getQuery()})`, 'subQ')
      .setParameters(query.getParameters())
      .orderBy('subQ.tree_name')
      .skip((page - 1) * limit).take(limit)  //the pagination 
      .getRawMany()
      .then(res => {
        //restrict the trees to be returned so we have the paginated result
        query.andWhere(
          'tree.id IN (:paginatedTrees)',
          { paginatedTrees: (res.length > 0) ? res.map(x => x.tree_id) : [null] }
        );
      });
    
    //now you got results that you wanted
    const result = await query.getMany();
    
    //method return could be something like this
    return {
      result, //the array with query results
      metadata: {
        page,   
        limit,
        totalItems // total items got before
      }
    }

So yes 3 times ping to DB which is really not ideal, but the first 2 times will return very small batches or results, so performance should not be ok hope it helps and if someone can make it better pls go for it and post to update my solution

Still facing the issue as well with 0.3.11

We are at 0.3.7 right now. I will check later and inform you.

Hi, I encountered the same issue with NestJS version 10.3.2.

Here’s part of the query that i was using:

return this.formRepository
  .createQueryBuilder('forms')
  .select(['forms.id', 'forms.name'])
  .leftJoinAndMapMany('forms.events', TemplateEvent, 'events')
  .orderBy('forms.id', 'ASC')
  .addOrderBy('events.id', 'ASC');

This query was only returning a single record, even when multiple records existed.

To fix this, I tried ordering the results by the child relation (events) first and then by the main entity (forms), like this:

.orderBy('events.id', 'ASC')
.addOrderBy('forms.id', 'ASC');

And that worked!

I hope this helps anyone facing a similar issue.

@firxworx , thanks for your reply.

Unfortunately, I’m trying to add a sorting feature to my library https://github.com/benjamin658/typeorm-cursor-pagination, and it relies on the query builder, so it’s not possible to use the raw query.

@benjamin658 if you need an escape hatch you can always hand-bomb a raw SQL query yourself. Refer to EntityManager.query(): https://typeorm.io/#/entity-manager-api. Of course, be careful re potential for SQL Injection vulnerabilities, etc. as the onus is now on you to be safe.

A part of me is surprised that this issue from last year is still kicking about… how are a ton of devs not facing this exact issue given the overall popularity of TypeORM especially w/ postgres!?!?