Faulty query on virtual relation

[JulianCissen]

Describe the bug
When defining a virtual relation (by defining formula in a relation decorator), applying filters on that relation will result in a faulty query.

Stack trace

error: column a0.first_book_id does not exist\n    at Parser.parseErrorMessage (C:\\sources\\mikro-orm-sandbox\\node_modules\\pg-protocol\\src\\parser.ts:369:69)\n    at Parser.handlePacket (C:\\sources\\mikro-orm-sandbox\\node_modules\\pg-protocol\\src\\parser.ts:188:21)\n    at Parser.parse (C:\\sources\\mikro-orm-sandbox\\node_modules\\pg-protocol\\src\\parser.ts:103:30)\n    at Socket.<anonymous> (C:\\sources\\mikro-orm-sandbox\\node_modules\\pg-protocol\\src\\index.ts:7:48)\n    at Socket.emit (node:events:517:28)\n    at Socket.emit (node:domain:489:12)\n    at addChunk (node:internal/streams/readable:335:12)\n    at readableAddChunk (node:internal/streams/readable:308:9)\n    at Socket.Readable.push (node:internal/streams/readable:245:10)\n    at TCP.onStreamRead (node:internal/stream_base_commons:190:23)

To Reproduce
Steps to reproduce the behavior:

1. Run the following code:

import {
  Collection,
  DateType,
  Entity,
  ManyToOne,
  OneToMany,
  OneToOne,
  PrimaryKey,
  Property,
  Ref,
  ref,
} from '@mikro-orm/core';
import { MikroORM } from '@mikro-orm/postgresql';
import { v4 } from 'uuid';

@Entity()
class Author {

  @PrimaryKey()
  id = v4();

  @Property()
  name!: string;

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

  @OneToOne({
    entity: () => Book,
    ref: true,
    formula: alias =>
      `(select "b"."id"
      from (
        select "b"."author_id", min("b"."release_date") "release_date"
        from "book" "b"
        where "b"."author_id" = ${alias}."id"
        group by "b"."author_id"
      ) "s1"
      join "book" "b"
      on "b"."author_id" = "s1"."author_id"
      and "b"."release_date" = "s1"."release_date")`,
  })
  firstBook?: Ref<Book>;

  constructor(name: string) {
    this.name = name;
  }

}

@Entity()
class Book {

  @PrimaryKey()
  id = v4();

  @Property({ type: DateType })
  releaseDate!: Date;

  @Property()
  name!: string;

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

  constructor(releaseDate: Date = new Date(), name: string, author: Author) {
    this.releaseDate = releaseDate;
    this.name = name;
    this.author = ref((author));
  }

}

let orm: MikroORM;

beforeAll(async () => {
  orm = await MikroORM.init({
    entities: [Book, Author],
    dbName: '4759',
    debug: true,
  });
  await orm.schema.refreshDatabase();
});

afterAll(() => orm.close(true));

test(`GH issue 4759`, async () => {
  const author = new Author('John');
  const book1 = new Book(new Date('2023-09-01'), 'My second book', author);
  const book2 = new Book(new Date('2023-01-01'), 'My first book', author);
  await orm.em.fork().persistAndFlush([author, book1, book2]);

  const authorFound = await orm.em.find(Author, { firstBook: { name: 'My first book' } }, { populate: ['books', 'firstBook'] });
  console.log(authorFound[0].firstBook?.$.name);
});

Expected behavior
A working query is produced that filters on the virtual firstBook relation.

Actual behavior
The following query is produced:

select
    "a0".*,
    (
        select
            "b"."id"
        from
            (
                select
                    "b"."author_id",
                    min("b"."release_date") "release_date"
                from
                    "book" "b"
                where
                    "b"."author_id" = "a0"."id"
                group by
                    "b"."author_id"
            ) "s1"
            join "book" "b" on "b"."author_id" = "s1"."author_id"
            and "b"."release_date" = "s1"."release_date"
    ) as "first_book_id"
from
    "author" as "a0"
    left join "book" as "b1" on "a0"."first_book_id" = "b1"."id"
where
    "b1"."name" = 'My first book'

Additional context
Not sure if defining virtual relations like this is officially supported, but I figured it was since it works nicely as long as you don't apply filters on the virtual relation.

Versions

Dependency	Version
node	18.18.0
typescript	5.1.6
mikro-orm	5.8.4
your-driver	5.8.4

[B4nan]

The problem here is that you can't use an aliased expression in the join clause, so even if we fix that alias in there, we end up with this:

select "a0".*, (select "b"."id"
      from (
        select "b"."author_id", min("b"."release_date") "release_date"
        from "book" "b"
        where "b"."author_id" = "a0"."id"
        group by "b"."author_id"
      ) "s1"
      join "book" "b"
      on "b"."author_id" = "s1"."author_id"
      and "b"."release_date" = "s1"."release_date") as "first_book_id" from "author" as "a0" left join "book" as "b1" on "first_book_id" = "b1"."id" where "b1"."name" = 'My first book'
 - column "first_book_id" does not exist

This would require a join on the subquery instead - which is actually something I added few days ago to v6:

#4747

But you would have to use the query builder (and update to v6)

[JulianCissen]

Good to know.

Since this is being implemented in query builder, does that mean that defining virtual entities this way will not be supported? It would be quite the change for me to move the codebase to use query builder for this, since I've got some quite complex filters on my entities that I'd prefer to reuse in stead of using query builder separately. If this won't be supported, what's the actual use-case for defining a formula on relation decorators?

[B4nan]

what's the actual use-case for defining a formula on relation decorators?

Well, it was never meant to be supported, formulas are tested only for scalar properties :]

I am sure we can find a way to make this work in v6, its more about designing the right API, implementation wise it should be pretty simple to wire those two features.

[B4nan]

This was actually much simpler, and can be part of v5.

[JulianCissen]

Great, thank you!

[squivix]

Was this implemented in v6 or 5? I don't see formula in relationship decorators in the docs

[B4nan]

its part of 5.8.5 as you can see from the commit detail