Let's define for example these entities:
@Entity()
export class Author {
@OneToMany(() => Book, book => book.author)
books = new Collection<Book>(this);
}
@Entity()
export class Book {
@Property()
name: string;
@ManyToOne()
author: Author;
}
const authors = await authorRepository.find({ books: {name: 'Test'} }, {
limit: 10
});
As you can see i want to select all authors that have books with name 'Test', but this will generate the following query:
select `e0`.* from `author` as `e0`
left join `book` as `e1` on `e0`.`id` = `e1`.`author_id`
where `e1`.`name` = 'Test' limit 10
The problem is when i have more than 2 authors and each of them has more than 10 books with name 'Test', this query will return only the first author because of the limit clause.
I am not sure if this is a bug in the ORM or it's a expected behavior.
One way to fix that is to select all rows without the limit clause and do the pagination in memory like they are doing in hibernate, but i am not sure how much memory will be used with very large tables and this might block the event loop in NodeJS while processing them.
You could fallback to query builder here to apply group by clause:
const qb = await authorRepository.createQueryBuilder('a');
qb.select('a.*').where({ books: { name: 'Test' } }).limit(10).groupBy('a.id');
const authors = await qb.getResult();
Will think about how to support this use case directly via EM/repository API.