Skip and take doesn't work with join #4742
Comments
|
@AlexMesser @pleerock Any idea? |
|
Could you please share what the "relations" object looks like in your first query? |
|
It's just an array of string like the example in find-option document. |
|
Sadly I can't replicate the described issue with relations or sub relations. Can you provide a failing test? Otherwise the .find method maps to getMany in the SelectQueryBuilder; could you try shoe horning in getRawMany. Curious if the pagination logic in executeEntitiesAndRawResults is having an affect. |
|
Unfortunately i can't provide anything. I don't access to the code anymore because this issue was related to my previous job. |
|
No worries at all, I saw a similar issue with incorrect select aliases - although your code omitted this I was curious what a getRawMany would return. @pleerock this can be closed? |
|
I had this issue as well, take and skip are typeorm level operations that use limit and offset. However, limit and offset do not work well because they limit all rows being returned after the join, which is not the behavior intended (at least not in my case). The solution in my case was to fetch first my base objects with skip and take (or limit and offset), and then do the next query with a whereInIds based off of the ids of my base objects. |
|
I think I'm facing this issue too. My query is a little complex so I have no idea why this happens. class ... {
async queryProblemsAndCount(
user: UserEntity,
hasPrivilege: boolean,
keyword: string,
tagIds: number[],
ownerId: number,
nonpublic: boolean,
skipCount: number,
takeCount: number
): Promise<[problems: ProblemEntity[], count: number]> {
const queryBuilder = this.problemRepository.createQueryBuilder("problem").select("problem.id", "id");
let groupByAdded = false;
if (tagIds && tagIds.length > 0) {
queryBuilder
.innerJoin(ProblemTagMapEntity, "map", "problem.id = map.problemId")
.andWhere("map.problemTagId IN (:...tagIds)", { tagIds })
.groupBy("problem.id");
groupByAdded = true;
if (tagIds.length > 1) queryBuilder.having("COUNT(DISTINCT map.problemTagId) = :count", { count: tagIds.length });
}
if (keyword) {
queryBuilder
.innerJoin(
LocalizedContentEntity,
"localizedContent",
'localizedContent.type = :type AND problem.id = localizedContent.objectId',
{ type: LocalizedContentType.PROBLEM_TITLE }
)
.andWhere("localizedContent.data LIKE :like", { like: `%${escapeLike(keyword)}%` });
if (!groupByAdded)
queryBuilder.groupBy("problem.id");
}
if (!hasPrivilege && !(user && ownerId === user.id)) {
if (user)
queryBuilder.andWhere(
new Brackets(brackets =>
brackets.where("problem.isPublic = 1").orWhere("problem.ownerId = :ownerId", { ownerId: user.id })
)
);
else queryBuilder.andWhere("problem.isPublic = 1");
} else if (nonpublic) {
queryBuilder.andWhere("problem.isPublic = 0");
}
if (ownerId) {
queryBuilder.andWhere("problem.ownerId = :ownerId", { ownerId });
}
// QueryBuilder.getManyAndCount() has bug with GROUP BY
const count = Number(
(
await this.connection
.createQueryBuilder()
.select("COUNT(*)", "count")
.from(`(${queryBuilder.getQuery()})`, "temp")
.setParameters(queryBuilder.expressionMap.parameters)
.getRawOne()
).count
);
queryBuilder
.orderBy("problem.displayId IS NOT NULL", "DESC")
.addOrderBy("problem.displayId", "ASC")
.addOrderBy("problem.id", "ASC");
const result = await queryBuilder.skip(skipCount).take(takeCount).getRawMany();
return [await this.findProblemsByExistingIds(result.map(row => row.id)), count];
}
}The generated SQL is: query: SELECT `problem`.`id` AS `id` FROM `problem` `problem` INNER JOIN `localized_content` `localizedContent` ON `localizedContent`.`type` = ? AND `problem`.`id` = `localizedContent`.`objectId` WHERE `localizedContent`.`data` LIKE ? GROUP BY `problem`.`id` ORDER BY `problem`.`displayId` IS NOT NULL DESC, `problem`.`displayId` ASC, `problem`.`id` ASC -- PARAMETERS: ["PROBLEM_TITLE","%a%"] |
|
|
|
@imnotjames not only postgres, but the driver mariadb also has this bug. |
|
I have the same issue using |
|
This happens to me with both |
|
I'm experiencing this issue also, using |
|
I think I already have a solution for my problem. Might share it here. Based on this #2912 (comment), when you add the third argument which is the condition for the join it will work now as expected |
|
So, according to my debugging (using NestJS + MySQL) this is what I've found:
If you would do but actually, these are just 2 results of 2 users where
@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 |
|
I have a query with joins and I'm using |
i have same problem too,, if i remove the join skip and take working just fine |
ups.. it work using limit and offset |
Yep that fixed it for me. Very interesting. |
thanks. it works like a charm. |
What if I want to order by joined table's column? I use the |
Is there any way around this? Right now skip and take give me wrong values, and I guess it's because of an orderBy. const query = Session.createQueryBuilder("session")
.leftJoinAndSelect("session.reviews", "reviews")
.orderBy({
"session.date": "DESC",
"reviews.created": "ASC",
});
console.log(await query.getMany())
console.log(await query.skip(1).take(1).getMany())This interestingly returns the entries in correct order, but the skip seems to be ignored: [
Session {
id: 'b1e5bf67-0e91-4569-9d73-a8a4286cee16',
},
Session {
id: '3c5255d4-a7a9-4f8c-a2b2-fe77f6b3174f',
}
] [
Session {
id: 'b1e5bf67-0e91-4569-9d73-a8a4286cee16',
}
]as you can see, the skip query returns the first item, not the second. |
|
2022 and I having the same issue on postgres. I solved replacing |
|
Awaiting for 6 month and it still haven't fixed yet. I will give a try on Prisma |
When the query has `join`, `take()/skip()` should be used. ref: typeorm/typeorm#4742 (comment)
|
Facing same issue for mysql also, it's working for limit and offfset. |
Same with postgres, works using limit and offset. |
|
Same with mariadb, works using limit. |
|
I ran into the same issue with mssql, I had a pagination middleware that used the |
|
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. |
|
Any new updates here? We're facing the same today. |
Same, As I know not fixed yet |
|
4 years and still not fixed? really? I cannot use |
|
Instead of writing skip/take (that creates outer subquery with DISTINCT) I managed to do it like this: So basically I gave limit/offset an opportunity to fetch dublicates, but erased them using distinct. |
|
Me too, since 2022 replacing |
|
I've been trying the use of |
|
Same here, any update on this? |
|
Neither of these work if you try to sort by a joined column. |
|
Based on my experience this is solved in postgres, I'm using multiple complex find queries with Can you bring more examples with all the related entities or an example repo to reproduce? |
|
not work in mysql, 4 years later.... |
|
Same problem here. When I add take and limit I have this error:
WIthout pagination, It works fine. |
|
ohh, now 2023, near 2024. still a problem. i spent 2 hours to resolve this problem. finally,use limit()&offset(), not take()&skip() |
and others
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[ ]
expoTypeORM version:
[ ]
latest[ ]
@next[x]
0.2.18(or put your version here)Hi
I have 7 row in my table and it's my first query.
First Query
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
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
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).
First test for Second query
It's my log with skip: 1 and take: 2
Everything works properly and i got expected rows.
Conclusion
I can only say it's strange behavior.
The text was updated successfully, but these errors were encountered: