typeorm: Skip and take doesn't work with join

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.18 (or put your version here)

I have 7 row in my table and it’s my first query.

First Query

Organization.find({
                relations,
                order: {
                    createdAt: "DESC"
                },
                skip: pagination.skip,
                take: pagination.take
            });

First test for First query

It’s my query log for just one request (my resolver dosn’t have any additional middleware) with skip: 1 and take: 2

query: SELECT DISTINCT "distinctAlias"."Organization_id" as "ids_Organization_id", "distinctAlias"."Organization_createdAt" FROM (SELECT "Organization"."id" AS "Organization_id", "Organization"."createdAt" AS "Organization_createdAt", "Organization"."updatedAt" AS "Organization_updatedAt", "Organization"."name" AS "Organization_name", "Organization"."address" AS "Organization_address", "Organization"."location" AS "Organization_location", "Organization"."contact" AS "Organization_contact", "Organization"."universityId" AS "Organization_universityId", "Organization"."type" AS "Organization_type", "Organization__university"."id" AS "Organization__university_id", "Organization__university"."createdAt" AS "Organization__university_createdAt", "Organization__university"."updatedAt" AS "Organization__university_updatedAt", "Organization__university"."name" AS "Organization__university_name", "Organization__university"."address" AS "Organization__university_address", "Organization__university"."location" AS "Organization__university_location", "Organization__university"."contact" AS "Organization__university_contact", "Organization__university"."type" AS "Organization__university_type", "Organization__units"."id" AS "Organization__units_id", "Organization__units"."createdAt" AS "Organization__units_createdAt", "Organization__units"."updatedAt" AS "Organization__units_updatedAt", "Organization__units"."name" AS "Organization__units_name", "Organization__units"."address" AS "Organization__units_address", "Organization__units"."location" AS "Organization__units_location", "Organization__units"."contact" AS "Organization__units_contact", "Organization__units"."allowedWareGroupsIds" AS "Organization__units_allowedWareGroupsIds", "Organization__units"."organizationId" AS "Organization__units_organizationId", "Organization__units"."type" AS "Organization__units_type" FROM "site" "Organization" LEFT JOIN "site" "Organization__university" ON "Organization__university"."id"="Organization"."universityId"  LEFT JOIN "site" "Organization__units" ON "Organization__units"."organizationId"="Organization"."id" WHERE  "Organization"."type" IN ($1)) "distinctAlias" ORDER BY "distinctAlias"."Organization_createdAt" DESC, "Organization_id" ASC LIMIT 2 OFFSET 1 -- PARAMETERS: ["Organization"]
query: SELECT "Organization"."id" AS "Organization_id", "Organization"."createdAt" AS "Organization_createdAt", "Organization"."updatedAt" AS "Organization_updatedAt", "Organization"."name" AS "Organization_name", "Organization"."address" AS "Organization_address", "Organization"."location" AS "Organization_location", "Organization"."contact" AS "Organization_contact", "Organization"."universityId" AS "Organization_universityId", "Organization"."type" AS "Organization_type", "Organization__university"."id" AS "Organization__university_id", "Organization__university"."createdAt" AS "Organization__university_createdAt", "Organization__university"."updatedAt" AS "Organization__university_updatedAt", "Organization__university"."name" AS "Organization__university_name", "Organization__university"."address" AS "Organization__university_address", "Organization__university"."location" AS "Organization__university_location", "Organization__university"."contact" AS "Organization__university_contact", "Organization__university"."type" AS "Organization__university_type", "Organization__units"."id" AS "Organization__units_id", "Organization__units"."createdAt" AS "Organization__units_createdAt", "Organization__units"."updatedAt" AS "Organization__units_updatedAt", "Organization__units"."name" AS "Organization__units_name", "Organization__units"."address" AS "Organization__units_address", "Organization__units"."location" AS "Organization__units_location", "Organization__units"."contact" AS "Organization__units_contact", "Organization__units"."allowedWareGroupsIds" AS "Organization__units_allowedWareGroupsIds", "Organization__units"."organizationId" AS "Organization__units_organizationId", "Organization__units"."type" AS "Organization__units_type" FROM "site" "Organization" LEFT JOIN "site" "Organization__university" ON "Organization__university"."id"="Organization"."universityId"  LEFT JOIN "site" "Organization__units" ON "Organization__units"."organizationId"="Organization"."id" WHERE  "Organization"."type" IN ($1) ORDER BY "Organization"."createdAt" DESC -- PARAMETERS: ["Organization"]

but i got my whole 7 rows and it seem two query executed!!! In above query log, in first log LIMIT and OFFSET clause exist but in the second query they don’t exist.

Second test for First query

It’s my second log for skip: 7 and take: 2

query: SELECT DISTINCT "distinctAlias"."Organization_id" as "ids_Organization_id", "distinctAlias"."Organization_createdAt" FROM (SELECT "Organization"."id" AS "Organization_id", "Organization"."createdAt" AS "Organization_createdAt", "Organization"."updatedAt" AS "Organization_updatedAt", "Organization"."name" AS "Organization_name", "Organization"."address" AS "Organization_address", "Organization"."location" AS "Organization_location", "Organization"."contact" AS "Organization_contact", "Organization"."universityId" AS "Organization_universityId", "Organization"."type" AS "Organization_type", "Organization__university"."id" AS "Organization__university_id", "Organization__university"."createdAt" AS "Organization__university_createdAt", "Organization__university"."updatedAt" AS "Organization__university_updatedAt", "Organization__university"."name" AS "Organization__university_name", "Organization__university"."address" AS "Organization__university_address", "Organization__university"."location" AS "Organization__university_location", "Organization__university"."contact" AS "Organization__university_contact", "Organization__university"."type" AS "Organization__university_type", "Organization__units"."id" AS "Organization__units_id", "Organization__units"."createdAt" AS "Organization__units_createdAt", "Organization__units"."updatedAt" AS "Organization__units_updatedAt", "Organization__units"."name" AS "Organization__units_name", "Organization__units"."address" AS "Organization__units_address", "Organization__units"."location" AS "Organization__units_location", "Organization__units"."contact" AS "Organization__units_contact", "Organization__units"."allowedWareGroupsIds" AS "Organization__units_allowedWareGroupsIds", "Organization__units"."organizationId" AS "Organization__units_organizationId", "Organization__units"."type" AS "Organization__units_type" FROM "site" "Organization" LEFT JOIN "site" "Organization__university" ON "Organization__university"."id"="Organization"."universityId"  LEFT JOIN "site" "Organization__units" ON "Organization__units"."organizationId"="Organization"."id" WHERE  "Organization"."type" IN ($1)) "distinctAlias" ORDER BY "distinctAlias"."Organization_createdAt" DESC, "Organization_id" ASC LIMIT 2 OFFSET 7 -- PARAMETERS: ["Organization"]

As i expected, result was empty and just one query executed (i have just 7 row in my table) LIMIT and OFFSET clause exist in query log.

Second Query

I changed my query to just retrieve rows without any join (relations).

Organization.find({
                order: {
                    createdAt: "DESC"
                },
                skip: pagination.skip,
                take: pagination.take
            });

First test for Second query

It’s my log with skip: 1 and take: 2

query: SELECT "Organization"."id" AS "Organization_id", "Organization"."createdAt" AS "Organization_createdAt", "Organization"."updatedAt" AS "Organization_updatedAt", "Organization"."name" AS "Organization_name", "Organization"."address" AS "Organization_address", "Organization"."location" AS "Organization_location", "Organization"."contact" AS "Organization_contact", "Organization"."universityId" AS "Organization_universityId", "Organization"."type" AS "Organization_type" FROM "site" "Organization" WHERE  "Organization"."type" IN ($1) ORDER BY "Organization"."createdAt" DESC LIMIT 2 OFFSET 1 -- PARAMETERS: ["Organization"]

Everything works properly and i got expected rows.

Conclusion

I can only say it’s strange behavior.

About this issue

Original URL
State: open
Created 5 years ago
Reactions: 32
Comments: 47 (3 by maintainers)

Commits related to this issue

Change `limit()/offset()` to `take()/skip()` for pagination When the query has `join`, `take()/skip()` should be used. ref: https://github.com/typeorm/typeorm/issues/4742#issuecomment-783857414 — committed to Fanta335/dev-meetup-backend by Fanta335 2 years ago

Most upvoted comments

skip and take doesn’t work but limit and offset works.

+112

Menci on Sep 28, 2020

So, according to my debugging (using NestJS + MySQL) this is what I’ve found:

limit and offset should only be used when your query has no joins. As soon as you have joins, you will end up with “duplicate” rows, something like this:

| id  | first_name | hobbies     |
| --- | ---        | ---         |
| 1   | Bob        | Sports      |
| 1   | Bob        | Drawing     |
| 1   | Bob        | Programming |
| 2   | Alice      | Drawing     |
| 2   | Alice      | Singing     |

If you would do .limit(2) then this would use LIMIT 2 in the query itself, thus resulting in only the first 2 records:

| id  | first_name | hobbies     |
| --- | ---        | ---         |
| 1   | Bob        | Sports      |
| 1   | Bob        | Drawing     |

but actually, these are just 2 results of 2 users where hobbies is an array of hobbies and can only result in 2 result if you let TypeORM handle these. See the next point for further explanation.

skip and take should be used as soon as you’re using and kind of join. Basically what it does is get all the results, merge them together into entities and then apply skip / take, which performance-wise a bit slower, but there’s no other way to handle this. If you have no joins in your query, it is recommended to use offset and limit for faster queries.
Something worth mentioning as well is the issue of using .orderBy() with .skip() and .take() which is also partially related to #747. When you use .offset() and .limit() you should use .orderBy('entity.created_at', 'DESC') where created_at is the raw field name. When you decide to use .orderBy() with .skip() and .take() you should use .orderBy('entity.createdAt', 'DESC') where createdAt refers to the entity field name like so:

@Entity('posts')
export class Post {
  @PrimaryGeneratedColumn({ unsigned: true })
  id: number;

  @CreateDateColumn({ name: 'created_at' }) // <--- use `.orderBy('entity.creatd_at', 'DESC')` with `.offset()` and `.limit()`
  createdAt: Date; // <--- use `.orderBy('entity.creatdAt', 'DESC')` with `.skip()` and `.take()`
}

NOTE: The above examples I mentioned are always assumed to use getMany() or getOne(). Try to avoid getRawMany() with big and advanced queries.

+26

kkoomen on Feb 23, 2021

Awaiting for 6 month and it still haven’t fixed yet. I will give a try on Prisma

+21

to-long on Jun 7, 2022

3 years and still not fixed? I though TypeORM was a serious ORM 😦

+10

chemic on Feb 2, 2023

3 years and still not fixed? I though TypeORM was a serious ORM 😦

This is just Open Source ORM, maybe the owner have no time, and its depend on the community too btw.

LuckyArdhika on Feb 2, 2023

This happens to me with both skip, take and limit, offset I’m using postgres, when I use getQuery, see what the query really looks like, with skip and take it does not write Limit and Offset to the actual SQL query but it does with limit and offset, however when I get the result from the database using getMany something happens that it does not bring the right amount of results.

mikews93 on Feb 17, 2021

I have the same issue using skip and take, with a non-trivial query. The problem is that a distinct clause is added (don’t know why) to the query, causing it to fail.

BogdanStanciu on Dec 11, 2020

ohh, now 2023, near 2024. still a problem. i spent 2 hours to resolve this problem. finally,use limit()&offset(), not take()&skip()

lolicrafter on Dec 15, 2023

Any new updates here? We’re facing the same today.

cdbattags on Feb 23, 2023

2022 and I having the same issue on postgres. I solved replacing skip() and take() by limit() and offset()

netojose on May 21, 2022

I’m experiencing this issue also, using .leftJoinAndSelect and .leftJoinAndMapOne. When I use skip + take the data is not returning correctly. Same goes with offset + limit

jannomeister on Feb 23, 2021

Instead of writing skip/take (that creates outer subquery with DISTINCT)

const [users, count] = await query
      .skip(10)
      .take(10)
      .orderBy('createdAt', 'ASC')
      .getManyAndCount();

I managed to do it like this:

const [users, count] = await query
      .distinct()
      .offset(10)
      .limit(10)
      .orderBy('createdAt', 'ASC')
      .getManyAndCount();

So basically I gave limit/offset an opportunity to fetch dublicates, but erased them using distinct. To increase performance you can try using distinctOn

dev1pavlo on Aug 4, 2023

Any new updates here? We’re facing the same today.

Same, As I know not fixed yet

hastalavistababy on Apr 18, 2023

Same problem here. When I add take and limit I have this error:

Unknown column 'distinctAlias.car_updatedAt' in 'field list'

WIthout pagination, It works fine.

santimakci on Sep 7, 2023

I have the same issue using skip and take, with a non-trivial query. The problem is that a distinct clause is added (don’t know why) to the query, causing it to fail.

Yes, I have a problem with DISTINCT too which is added to query by .take() method. Issue: https://github.com/typeorm/typeorm/issues/8605

Gleb-Gaiduk on Feb 4, 2022

So, according to my debugging (using NestJS + MySQL) this is what I’ve found:

limit and offset should only be used when your query has no joins. As soon as you have joins, you will end up with “duplicate” rows, something like this:
| id  | first_name | hobbies     |
| --- | ---        | ---         |
| 1   | Bob        | Sports      |
| 1   | Bob        | Drawing     |
| 1   | Bob        | Programming |
| 2   | Alice      | Drawing     |
| 2   | Alice      | Singing     |
If you would do .limit(2) then this would use LIMIT 2 in the query itself, thus resulting in only the first 2 records:
| id  | first_name | hobbies     |
| --- | ---        | ---         |
| 1   | Bob        | Sports      |
| 1   | Bob        | Drawing     |
but actually, these are just 2 results of 2 users where hobbies is an array of hobbies and can only result in 2 result if you let TypeORM handle these. See the next point for further explanation.

skip and take should be used as soon as you’re using and kind of join. Basically what it does is get all the results, merge them together into entities and then apply skip / take, which performance-wise a bit slower, but there’s no other way to handle this. If you have no joins in your query, it is recommended to use offset and limit for faster queries.

Something worth mentioning as well is the issue of using .orderBy() with .skip() and .take() which is also partially related to [Question] QueryBuilder - Issues with executing a query builder. #747. When you use .offset() and .limit() you should use .orderBy('entity.created_at', 'DESC') where created_at is the raw field name. When you decide to use .orderBy() with .skip() and .take() you should use .orderBy('entity.createdAt', 'DESC') where createdAt refers to the entity field name like so:
@Entity('posts')
export class Post {
  @PrimaryGeneratedColumn({ unsigned: true })
  id: number;

  @CreateDateColumn({ name: 'created_at' }) // <--- use `.orderBy('entity.creatd_at', 'DESC')` with `.offset()` and `.limit()`
  createdAt: Date; // <--- use `.orderBy('entity.creatdAt', 'DESC')` with `.skip()` and `.take()`
}
NOTE: The above examples I mentioned are always assumed to use getMany() or getOne(). Try to avoid getRawMany() with big and advanced queries.

What if I want to order by joined table’s column? I use the take and addOrderBy, the behavior is really confusing.

benjamin658 on Jul 13, 2021

I have a query with joins and I’m using skip and take but they’re not being applied to entities so my result ends up with fewer entities than expected because of the duplicates that @kkoomen just explained (I could verify that by comparing the SQL query result and the entities returned with getMany()). Even though the SQL query doesn’t contain any LIMIT. I’m also using .orderBy() with entity and fieldname.

yossely on Mar 17, 2021

@imnotjames not only postgres, but the driver mariadb also has this bug.

Menci on Oct 8, 2020