Difference between .limit() and .take() in TypeORM

Question

I am confused about different TypeORM methods with similar purposes. From TypeORM docs:

• .take() — pagination limit. Sets maximal number of entities to take.
• .skip() — pagination offset. Sets number of entities to skip.

I poorly understand what "pagination limit/offset" means. But, unfortunately, I couldn't find any information about distinguishing of, for instance, .take() and .limit(). I decided to see descriptions of these methods in TypeORM's source code:

• .limit() — Set's LIMIT - maximum number of rows to be selected. NOTE that it may not work as you expect if you are using joins. If you want to implement pagination, and you are having join in your query, then use instead take method instead.
• .offset() — Set's OFFSET - selection offset. NOTE that it may not work as you expect if you are using joins. If you want to implement pagination, and you are having join in your query, then use instead skip method instead.

Why these two methods cannot be used for pagination? What are their purposes then? Please, could anyone provide me with clear examples of using all these 4 methods? Thanks in advance.

Meat

Answer 1

The difference is that take and skip will be not part of the query you will execute, typeorm perform it after get results. This is util overall when your query include any kind of join because result is not like TypeORM map to us.

On the other hand, limit and offset are included in the query, but may not work as you expect if you are using joins. For example, if you have an entity A with a relation OneToMany with B, and you try get the first three entries on A (using limit 3) and you do the join with B, if the first entry has 3 B, then you will get one A only.

Take a look at the description that we can see if we use offset(same for limit)

Aberdare

So you mean .limit() may possibly cause unexpected behavior? If I wish to 'limit' 3 entities A joining B, it may take 1 entity A and 3 entities B, right? But why? – Meat

yes, as I said, limit() include it in SQL query and take(), not... following the example above, if you run the query SELECT * FROM A a LEFT JOIN B b ON b.aId = a.id LIMIT 3, you will get three rows(AB1, AB2, AB3), then when TypeORM map it to entities, you get [{ bs: [B1, B2, B3] }]... otherwise if you use take(), the query will dont include limit SELECT * FROM A a LEFT JOIN B b ON b.aId = a.id, it will be free of get all rows that match it, map to entity and finally take the amount of entries A need... both options has not difference if there is no joins, limit is optimal if you can use – Aberdare

I like separate in two queries for the cases when I need perform joins with the goal of use limit() and offset() always, first to get the ids of entity A that satisfy the restrictions, then another for get all data of entries A with ids included in previous list, this prevent load in memory unnecessary data – Aberdare

@LeonardoDiPierro BTW Sorry for my english – Aberdare

Oh, thanks! Everything is clear. – Meat

Sounds like a bad idea to use take and skip with large result sets and not as an option for pagination if TypeORM loads the whole result set into memory first just to throw it away afterwards... – Lemus

Answer 2

I tried running it on PostgreSQL to see the actual queries. Not sure why the other answer says that take is not part of the query, looks like it is, but in different way.

My TypeORM code looks something like this:

const result = manager.getRepository(UserEntity)
    .createQueryBuilder('u')
    .select()
    .leftJoinAndSelect('u.orders', 'o')
    .orderBy('u.column', 'DESC')
    .limit(3); // or .take(3)

In case of .limit(3) it makes a query like:

SELECT ... 
FROM user LEFT JOIN order ON order.user_id = user.id
ORDER BY user.column 
LIMIT 3

And it's actually clear why you can get less then 3 of user objects with such query. Consider the result of SELECT query looking like this:

user.id | order.id | ...other columns
-------------------------------------
u1        o1
u1        o2
u2        o3   
u3        o4  <--- over limit

First user u1 made two orders. We have 4 rows in joined table and miss the 3rd user u3 in our result.

But if I use .take(3), I see TypeORM making two queries. First one selects ids:

SELECT DISTINCT user.id FROM (
   SELECT ... FROM user JOIN order ON ...
) LIMIT 3

Which results in 3 user ids (u1, u2, and u3). And then it makes a second query, filtering result by these ids.

SELECT ... 
FROM user LEFT JOIN order ON ...
WHERE user.id IN ('u1', 'u2', 'u3')
ORDER BY u.column

So in 2nd query number of rows in joined table can be more than the specified limit, but in result array in JS code you get exactly number of user objects specified in take().

I omitted offset() / skip() for clarity, but they both just add SQL OFFSET (in case of .take() - to the first query of two)

So, .take() makes two queries instead of one, but gets exactly specified number of entity objects. I don't see that TypeORM does something not on DB level here. So, unless you have huge limits, .take() performance should be OK.

Viole

Answer 3

So in any case, as for joining tables limit and offset don't always work as we would expect them to, just to be on the safe side, using take and skip instead of limit and offset would be preferable.

Ballarat

specifically limit and offset are not stateful and will return the same or skip results between 'pages' if limit and offset are used for pagination and records are added/deleted between page requests. Using take and skip doesn't solve this, what exactly do you mean by don't always work as we would expect them to? As far as the resultant SQL query without state; take and skip are a subset of limit and offset making them less predictable and not more preferable as you put it – Hoy