QueryBuilder joins not working as expected #3812
Comments
|
Reproductions should be minimal, this is really huge. You don't need a web server, you don't need 10 entities, you don't need dozens of nested folders and files to reproduce this... Please simplify this to bare minimum, I don't mind a nest app per se, but it should have only one module and controller, no abstractions, no services and other unrelated stuff. |
|
Hey B4nan, thanks for the fast response! I updated the repo and changed the structure to make it easier and left only 3 related entites which are enough to reproduce the issue. The query builder is now being executed directly in I thought of leaving nest as it might be a serialization problem related to it. |
|
Hey, thanks for the awesome ORM, Martin! Really liking it so far. I've ran into pretty much the same issue as the OP, so I'm definitely subscribing to news on this! |
|
Got it reproduced, this is indeed caused by the missing populate hints, e.g. // works
const res = await orm.em.find(SerialNumber, {}, { populate: ['logs.step'] });
// fails
const res = await orm.em
.createQueryBuilder(SerialNumber, 'sn')
.select('*')
.leftJoinAndSelect('sn.logs', 'l')
.leftJoinAndSelect('l.step', 's')
.getResultList();Which should be fixable, we should be able to construct the populate hint from |
|
Hey B4nan, thank you for the fix! I can confirm that |
|
@B4nan getting the same issue as ☝️ where My setup looks something like erDiagram
author ||--o{ book : oneToMany
book ||--o{ comment : oneToMany
book }o--|| bookTemplate : manyToOne
export class AuthorRepository extends EntityRepository<Author> {
async findAllWithRelations(commenterId: string): Promise<Author[]> {
return this.qb()
.select('*')
.joinAndSelect('books', 'b')
.joinAndSelect('b.bookTemplate', 'bt')
.leftJoinAndSelect('b.comments', 'bc', {
'bc.commenterId': commenterId,
});
}
}The expected result is [
{
id: 'some-author-id',
books: [
{
id: 'some-book-id',
bookTemplate: {
id: 'some-book-template-id',
foo: 'bar',
},
comments: [
{
id: 'some-comment-id',
bar: 'baz'
},
{
id: 'other-comment-id',
bar: 'baz'
},
]
}
]
}
]But getting [
{
id: 'some-author-id',
books: [
{
id: 'some-book-id',
bookTemplate: 'some-book-template-id', // <<< expecting populated bookTemplate
comments: [
{
id: 'some-comment-id',
bar: 'baz'
},
{
id: 'other-comment-id',
bar: 'baz'
},
]
}
]
}
]Should we configure it to be populated, even if it is already selected with |
Describe the bug
I am using MikroORM with Nest.js and PostgreSQL. This is how my database model looks like:
I want to use the query builder to fetch all the serial numbers and do left join with the
logentity. The query I am using is as follows:However it seems that MikroORM is also populating sn.logs.serialNumber relation which it shouldn't according to my query.
Minimal reproduction repo - https://github.com/fifi98/mikro-orm-repro. The query is being executed in the serial-numbers service (
src/serial-numbers/serial-numbers.controller.ts).Stack trace
None
To Reproduce
yarnand thenyarn start:devExpected behavior
The expected results of the executed query should be as follows:
However this is what the query returns:
Additional context
The query that gets executed by MikroORM seems to be correct:
[Nest] 64056 - 02/12/2022, 11:45:53 LOG [MikroORM] [query] select "sn".*, "l"."id" as "l__id", "l"."created_at" as "l__created_at", "l"."updated_at" as "l__updated_at", "l"."deleted_at" as "l__deleted_at", "l"."serial_number_id" as "l__serial_number_id", "l"."step_id" as "l__step_id", "l"."operation_date" as "l__operation_date", "l"."current" as "l__current", "l"."returned" as "l__returned", "s"."id" as "s__id", "s"."created_at" as "s__created_at", "s"."updated_at" as "s__updated_at", "s"."deleted_at" as "s__deleted_at", "s"."name" as "s__name", "s"."number" as "s__number", "s"."order" as "s__order", "s"."type" as "s__type" from "serial_number" as "sn" left join "log" as "l" on "sn"."id" = "l"."serial_number_id" left join "step" as "s" on "l"."step_id" = "s"."id" [took 180 ms]Thank you for the great ORM and the hard work that's been put into this, I really like using MikroORM! Please let me know if there is any additional information that I should provide.
Versions
The text was updated successfully, but these errors were encountered: