Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Wrong table alias prefixes are generated when querying a JsonType through a (1:N) relation. #3242

Closed
JulianCissen opened this issue Jun 22, 2022 · 1 comment
Labels
bug Something isn't working

Comments

@JulianCissen
Copy link

JulianCissen commented Jun 22, 2022

Describe the bug
When trying to query an entity by adding a condition on a JsonType field for a related 1:N entity, Mikro-ORM will generate a broken query.

To Reproduce

import 'reflect-metadata';
import { Collection, Entity, JsonType, ManyToOne, MikroORM, OneToMany, PrimaryKey, Property, wrap } from "@mikro-orm/core";
import { PostgreSqlDriver } from '@mikro-orm/postgresql';

@Entity()
export class Author {
    @PrimaryKey()
    id!: string;

    @OneToMany({
        entity: () => Book,
        mappedBy: 'author',
    })
    books = new Collection<Book>(this);
}

@Entity()
export class Book {
    @PrimaryKey()
    id!: string;

    @Property({ type: JsonType })
    data!: Record<string, unknown>;

    @ManyToOne({
        entity: () => Author,
        inversedBy: 'books',
    })
    author!: Author;
}

const start = async () => {
    const db = await MikroORM.init<PostgreSqlDriver>({
        entities: [Book, Author],
        type: 'postgresql',
        dbName: 'debug',
        host: 'localhost',
        port: 5432,
        user: 'postgres',
        password: 'postgres',
        debug: true,
    });

    // Faulty:
    const query1 = await db.em.fork().find(Author, {
        books: { data: { title: 'test' } },
    });
    // Query output:
    // select "a0".* from "author" as "a0" left join "book" as "b1" on "a0"."id" = "b1"."author_id" where b1."b1"."data"->>'title' = 'test'
    // Observe how the data property condition is prefixed with a double table alias.

    // Correct:
    const query2 = await db.em.fork().find(Author, {
        books: { id: 'test' },
    });
    // Query output:
    // select "a0".* from "author" as "a0" left join "book" as "b1" on "a0"."id" = "b1"."author_id" where "b1"."id" = 'test'

    // Also correct:
    const query3 = await db.em.fork().find(Book, {
        data: { title: 'test' },
    });
    // Query output:
    // select "b0".* from "book" as "b0" where "b0"."data"->>'title' = 'test'
}

start();

Expected behavior
Mikro-ORM will generate a working query for query 1. Specifically, in the WHERE clause the "data" column will have a single alias.

Actual behavior
Mikro-ORM generates a WHERE clause containing double table aliases. Postgres (and probably other databases too) will generate an error: TableNotFoundException: select "a0".* from "author" as "a0" left join "book" as "b1" on "a0"."id" = "b1"."author_id" where b1."b1"."data"->>'title' = 'test' - invalid reference to FROM-clause entry for table "b1"

Additional context
Add any other context about the problem here.

Versions

Dependency Version
node v17.4.0
typescript 4.7.3
mikro-orm 5.2.0
postgresql 5.2.0
@B4nan B4nan added the bug Something isn't working label Jun 23, 2022
@B4nan
Copy link
Member

B4nan commented Jun 23, 2022

Thanks for the repro!

@B4nan B4nan closed this as completed in 847ff46 Jun 23, 2022
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
bug Something isn't working
Projects
None yet
Development

No branches or pull requests

2 participants