join exact columns

[Laziz2L]

I have these entities:

@Entity({ tableName: 'posts', repository: () => PostRepository })
export class Post extends BaseEntity {
    @Property()
    title!: string;

    @Property()
    content!: string;

    @Property()
    published!: boolean;

    @ManyToOne({
        entity: () => Author,
        deleteRule: 'cascade',
        updateRule: 'cascade',
    })
    author?: Author;

    @Property({ persist: false })
    authorId?: number;

    @OneToMany({
        entity: () => Comment,
        mappedBy: 'post',
        persist: false,
    })
    comments?: Comment[];
}

@Entity({ tableName: 'comments' })
export class Comment {
    @Enum({ items: () => CommentObjectTypeEnum, nativeEnumName: 'comment_object_type_enum', primary: true })
    objectType!: string;

    @PrimaryKey()
    objectId!: number;

    @Property()
    content!: string;

    @Property()
    createdAt? = new Date();

    @Property({ onUpdate: () => new Date() })
    updatedAt? = new Date();

    @ManyToOne({
        entity: () => Post,
        persist: false,
        name: 'object_id',
    })
    post?: Post;
}

export enum CommentObjectTypeEnum {
    comment = 'comment',
    post = 'post',
}

In my PostRepositoryClass I want to have function that returns post by id with its comments and response schema like this :

[
  {
    "id": 2,
    "createdAt": "2024-04-08T13:09:42.585Z",
    "updatedAt": "2024-04-08T13:09:42.585Z",
    "title": "title of post",
    "content": "content of post",
    "published": true,
    "author": {
      "id": 2,
      "name": "author name",
      "biography": "author "
    },
    "comments": [
      {
        "content": "comment content",
        "createdAt": "2024-04-10T02:19:36.726Z",
        "updatedAt": "2024-04-10T02:19:39.178Z",
      }
    ]
  }
]

1. I tried:

this.createQueryBuilder('post')
                .leftJoinAndSelect('post.author', 'author', {}, ['author.id', 'author.name', 'author.biography'])
                .leftJoinAndSelect(
                    'post.comments',
                    'comments',
                    {
                        $and: [{ 'comments.objectType': CommentObjectTypeEnum.post }],
                    },
                    ['comments.content', 'comments.createdAt', 'comments.updatedAt'],
                )
                .where({ id: postId })
                .getResult()

it returns:

[
  {
    "id": 2,
    "createdAt": "2024-04-08T13:09:42.585Z",
    "updatedAt": "2024-04-08T13:09:42.585Z",
    "title": "title2",
    "content": "content of post",
    "published": true,
    "author": {
      "id": 2
    },
    "authorId": 2,
    "comments": [
      {
        "objectType": "post",
        "objectId": 2,
        "post": 2
      }
    ]
  }
]

2. I tried:

this.createQueryBuilder('post')
                .leftJoinAndSelect('post.author', 'author')
                .leftJoinAndSelect('post.comments', 'comments', {
                    $and: [{ 'comments.objectType': CommentObjectTypeEnum.post }],
                })
                .select([
                    'post.id',
                    'post.title',
                    'post.content',
                    'post.published',
                    'post.createdAt',
                    'post.updatedAt',
                    'comments.content', //also tried 'comments.content as comments__content' - the result is same
                    'comments.created_at',
                    'comments.updated_at',
                    'author.id',
                    'author.name',
                    'author.biography',
                ])
                .where({ id: postId })
                .getResult()

it returns:

[
  {
    "id": 2,
    "createdAt": "2024-04-10T02:19:36.726Z",
    "updatedAt": "2024-04-10T02:19:39.178Z",
    "title": "title2",
    "content": "comment content",
    "published": true,
    "author": null,
    "comments": []
  }
]

3. I tried:

const commentsSubQuery = this.em
    .createQueryBuilder(Comment, 'comments')
    .select(['comments.content', 'comments.created_at', 'comments.updated_at'])
    .where({ 'comments.objectType': CommentObjectTypeEnum.post, 'comments.objectId': postId });

this.createQueryBuilder('post')
    .leftJoinAndSelect('post.author', 'author')
    .leftJoinAndSelect(['post.comments', commentsSubQuery], 'comments')
    .where({ id: postId })
    .getResult()

it returns error in query:
ERROR [ExceptionsHandler] select "post".*, "author"."id" as "author__id", "author"."created_at" as "author__created_at", "author"."updated_at" as "author__updated_at", "author"."name" as "author__name", "author"."biography" as "author__biography", "comments"."object_type" as "comments__object_type", "comments"."object_id" as "comments__object_id", "comments"."content" as "comments__content", "comments"."created_at" as "comments__created_at", "comments"."updated_at" as "comments__updated_at", "comments"."object_id" as "comments__object_id" from "posts" as "post" left join "authors" as "author" on "post"."author_id" = "author"."id" left join (select "comments"."content", "comments"."created_at", "comments"."updated_at" from "comments" as "comments" where "comments"."object_type" = 'post' and "comments"."object_id" = 2) as "comments" where "post"."id" = 2 - syntax error at or near "where"

How do I should write this code?
Actually, I would also like to use virtual entities to join comments table - create virtual entity for each objectType of comments and use entities to join them without extra condition). Is there any way to do this too?

[B4nan]

QB does not return a JSON as you are saying, it returns entity instances (at least when execute it this way with qb.getResult() or await it directly), which you later on serialize to JSON (and since you haven't mentioned how, you probably do it implicitly via JSON.stringify, or better say, some layer of your app is doing that when serializing the response object). You can control the serialization explicitly, or even bring your own, which if you expect a response that is not matching shape of your entities, you will need to do anyway.

It's hard to tell more, ideally provide a complete reproduction.

---

Also, next time please be patient, if you open a GH discussion, and do not get a response in 30 minutes, it does not mean you need to create yet another issue about the very same, without any additional context.

[B4nan]

So the problem with the join condition is that there is no way to know what will be exposed in the subquery you join to, so in a normal subquery join, and we also don't know what is the other side of the relation, so you need to be explicit with the on condition. But in the case you are trying out, with the array syntax, we know the property name, so we could infer it automatically most probably, will take a look at that.

[Laziz2L]

// src/example.test.ts
import {
	OptionalProps,
	Entity,
	MikroORM,
	PrimaryKey,
	Property,
	OneToMany,
	ManyToOne,
	Enum,
	raw,
} from "@mikro-orm/postgresql";

abstract class BaseEntity {
	[OptionalProps]?: "createdAt" | "updatedAt";

	@PrimaryKey()
	id!: number;

	@Property({ defaultRaw: "now()" })
	createdAt = new Date();

	@Property({ defaultRaw: "now()", onUpdate: () => new Date() })
	updatedAt = new Date();
}

@Entity({ tableName: "authors" })
class Author extends BaseEntity {
	@Property()
	name!: string;

	@Property({ nullable: true })
	biography?: string;

	@OneToMany(() => Post, (post) => post.author)
	posts?: Post[];
}

@Entity({ tableName: "posts" })
class Post extends BaseEntity {
	@Property()
	title!: string;

	@Property()
	content!: string;

	@Property()
	published!: boolean;

	@ManyToOne({
		entity: () => Author,
		deleteRule: "cascade",
		updateRule: "cascade",
	})
	author!: Author;

	@Property({ persist: false })
	authorId?: number;

	@OneToMany({
		entity: () => Comment,
		mappedBy: "post",
		persist: false,
	})
	comments?: Comment[];
}

@Entity({ tableName: "comments" })
class Comment {
	@Enum({
		items: () => CommentObjectTypeEnum,
		nativeEnumName: "comment_object_type_enum",
		primary: true,
	})
	objectType!: string;

	@PrimaryKey()
	objectId!: number;

	@Property()
	content!: string;

	@Property()
	createdAt? = new Date();

	@Property({ onUpdate: () => new Date() })
	updatedAt? = new Date();

	@ManyToOne({
		entity: () => Post,
		persist: false,
		name: "object_id",
	})
	post?: Post;
}

export enum CommentObjectTypeEnum {
	comment = "comment",
	post = "post",
}

let orm: MikroORM;
let author: Author, post: Post, comment: Comment;

beforeAll(async () => {
	orm = await MikroORM.init({
		host: "localhost",
		port: 5432,
		user: "laziz",
		password: "",
		dbName: "callme_local",
		entities: [Author, Post, Comment],
		debug: ["query", "query-params"],
		allowGlobalContext: true, // only for testing
	});
	await orm.schema.refreshDatabase();

	author = await orm.em.create(Author, {
		id: 3,
		name: "author name",
		biography: "author bio",
	});
	post = await orm.em.create(Post, {
		id: 5,
		title: "post title",
		content: "post content",
		published: true,
		author,
	});
	comment = await orm.em.create(Comment, {
		objectType: CommentObjectTypeEnum.post,
		objectId: 5,
		content: "comment content",
		createdAt: new Date(),
		updatedAt: new Date(),
	});
	await orm.em.flush();
	orm.em.clear();
});

afterAll(async () => {
	await orm.em.removeAndFlush(comment);
	await orm.em.removeAndFlush(post);
	await orm.em.removeAndFlush(author);
	await orm.close(true);
});

test("define joined columns in leftJoinAndSelect()", async () => {
	let posts = await orm.em
		.createQueryBuilder(Post, "post")
		.leftJoinAndSelect("post.author", "author", {}, [
			"author.id",
			"author.name",
			"author.biography",
		])
		.leftJoinAndSelect(
			"post.comments",
			"comments",
			{
				$and: [{ "comments.objectType": CommentObjectTypeEnum.post }],
			},
			["comments.content", "comments.createdAt", "comments.updatedAt"]
		)
		.where({ id: 5 })
		.getResult();

	expect(posts[0].id).toBe(5);
	expect(posts[0].author).toBeDefined();
	expect(posts[0].author.id).toBe(3);
	expect(posts[0].author.name).toBe("author name");
	expect(posts[0].author.biography).toBe("author bio");
	expect(posts[0].comments).toBeDefined();
	expect(posts[0].comments![0]).toBeDefined();
	expect(posts[0].comments![0].content).toBe("comment content");
	expect(posts[0].comments![0].createdAt).toBeDefined();
	expect(posts[0].comments![0].updatedAt).toBeDefined();
});

test("define joined columns in select()", async () => {
	let posts = await orm.em
		.createQueryBuilder(Post, "post")
		.leftJoinAndSelect("post.author", "author")
		.leftJoinAndSelect("post.comments", "comments", {
			$and: [{ "comments.objectType": CommentObjectTypeEnum.post }],
		})
		.select([
			"post.id",
			"post.title",
			"post.content",
			"post.published",
			"post.createdAt",
			"post.updatedAt",
			"comments.content",
			"comments.created_at",
			"comments.updated_at",
			"author.id",
			"author.name",
			"author.biography",
		])
		.where({ id: 5 })
		.getResult();

	// console.log(posts[0]);
	// Post {
	//   createdAt: 2024-04-10T18:35:59.830Z,
	//   updatedAt: 2024-04-10T18:35:59.830Z,
	//   title: 'post title',
	//   content: 'comment content',
	//   published: true,
	//   author: null,
	//   id: 3,
	//   comments: Collection<Comment> { initialized: true, dirty: false }
	// }

	// expect(posts[0].id).toBe(5); // id is overwritted by "author.id"
	expect(posts[0].author).not.toBe(null);
	// expect(posts[0].biography).toBeDefined(); Property 'biography' does not exist on type 'Post'. But it is provided in select list
});

test("use subquery for comments", async () => {
	const commentsSubQuery = orm.em //? can I do entity @filter with filtering by objectType and then use it in joins?
		.createQueryBuilder(Comment, "comments")
		.select([
			"comments.content",
			"comments.created_at",
			"comments.updated_at",
		])
		.where({
			"comments.objectType": CommentObjectTypeEnum.post,
			"comments.objectId": 5,
		});

	const posts = orm.em
		.createQueryBuilder(Post, "post")
		.leftJoinAndSelect("post.author", "author")
		.leftJoinAndSelect(["post.comments", commentsSubQuery], "comments", {
			$ne: { content: null }, //? I want condition "ON comments.content is not null", cant figure how to do it
		})
		.where({ id: 5 })
		.getResult();
});

[Laziz2L]

I only changed sqlite to postgresql. Hope that it will work out that way)

[B4nan]

Few notes:

• your m:1 is virtual, that's your first issue, I guess you are coming from typeorm, seeing this entity definition? you don't need the scalar properties, but you need the relation ones, they define both the property value and the FK.
• no point in adding persist: false on collection properties, they are virtual by definition
• you need the Collection wrapper for them
• you don't have to use the $and operator in join condition, just a plain object condition is enough - using $and/$or with a single item in the array makes no sense in general
• the second approach is wrong, you need qb.joinAndSelect for hydration to work, you need the metadata which are otherwise not there, you need the aliasing that the method generates
• the third test now works thanks to 3dfbbde
• and finally - you are supposed to be passing property names of the relation, no aliases there, that is the bit that actually solves your problem with qb.joinAndSelect

Here is a passing repro with fixed entity definition:

import {
  OptionalProps,
  Entity,
  MikroORM,
  PrimaryKey,
  Property,
  OneToMany,
  ManyToOne,
  Enum, Collection, Opt,
} from '@mikro-orm/postgresql';

abstract class BaseEntity {

  [OptionalProps]?: 'createdAt' | 'updatedAt';

  @PrimaryKey()
  id!: number;

  @Property({ defaultRaw: 'now()' })
  createdAt = new Date();

  @Property({ defaultRaw: 'now()', onUpdate: () => new Date() })
  updatedAt = new Date();

}

@Entity({ tableName: 'authors' })
class Author extends BaseEntity {

  @Property()
  name!: string;

  @Property({ nullable: true })
  biography?: string;

  @OneToMany(() => Post, post => post.author)
  posts = new Collection<Post>(this);

}

@Entity({ tableName: 'posts' })
class Post extends BaseEntity {

  @Property()
  title!: string;

  @Property()
  content!: string;

  @Property()
  published!: boolean;

  @ManyToOne({
    entity: () => Author,
    deleteRule: 'cascade',
    updateRule: 'cascade',
  })
  author!: Author;

  @Property({ persist: false })
  authorId?: number;

  @OneToMany({
    entity: () => Comment,
    mappedBy: 'post',
  })
  comments = new Collection<Comment>(this);

}

@Entity({ tableName: 'comments' })
class Comment {

  @Enum({
    items: () => CommentObjectTypeEnum,
    nativeEnumName: 'comment_object_type_enum',
    primary: true,
  })
  objectType!: string;

  @Property({ persist: false })
  objectId!: number & Opt;

  @Property()
  content!: string;

  @Property()
  createdAt? = new Date();

  @Property({ onUpdate: () => new Date() })
  updatedAt? = new Date();

  @ManyToOne({
    entity: () => Post,
    name: 'object_id',
    primary: true, // edit: forgot about this one initially
  })
  post!: Post;

}

export enum CommentObjectTypeEnum {
  comment = 'comment',
  post = 'post',
}

let orm: MikroORM;
let author: Author, post: Post, comment: Comment;

beforeAll(async () => {
  orm = await MikroORM.init({
    dbName: '5445',
    entities: [Author, Post, Comment],
    debug: ['query', 'query-params'],
  });
  await orm.schema.refreshDatabase();

  author = orm.em.create(Author, {
    id: 3,
    name: 'author name',
    biography: 'author bio',
  });
  post = orm.em.create(Post, {
    id: 5,
    title: 'post title',
    content: 'post content',
    published: true,
    author,
  });
  comment = orm.em.create(Comment, {
    objectType: CommentObjectTypeEnum.post,
    post: 5,
    content: 'comment content',
    createdAt: new Date(),
    updatedAt: new Date(),
  });
  await orm.em.flush();
  orm.em.clear();
});

afterAll(async () => {
  await orm.close(true);
});

test('define joined columns in leftJoinAndSelect()', async () => {
  const posts = await orm.em
    .createQueryBuilder(Post, 'post')
    .leftJoinAndSelect('post.author', 'author', {}, [
      'id',
      'name',
      'biography',
    ])
    .leftJoinAndSelect(
      'post.comments',
      'comments',
      {
        'comments.objectType': CommentObjectTypeEnum.post,
      },
      ['content', 'createdAt', 'updatedAt'],
    )
    .where({ id: 5 })
    .getResult();

  expect(posts[0].id).toBe(5);
  expect(posts[0].author).toBeDefined();
  expect(posts[0].author.id).toBe(3);
  expect(posts[0].author.name).toBe('author name');
  expect(posts[0].author.biography).toBe('author bio');
  expect(posts[0].comments).toBeDefined();
  expect(posts[0].comments![0]).toBeDefined();
  expect(posts[0].comments![0].content).toBe('comment content');
  expect(posts[0].comments![0].createdAt).toBeDefined();
  expect(posts[0].comments![0].updatedAt).toBeDefined();
});

Some of those things might not be needed, except the very last one, but take them as suggestions on how this tool is supposed to be used.

For the last test where you tried the $ne operator - you cannot use normal operators like that, only group operators can be on top level (not/and/or). $ne means not equal, you need to specify the left hand side too. You want content: { $ne: null } instead - which reads content not equals null.

[B4nan]

The third test actually needed more adjustments. The idea behind the subquery joins is that you link the entities dynamically, you don't need the explicit condition in your subquery. You are also missing the PK in the select clause of the subquery, so it can be linked.

Here are both tests with the changes to make them work (again, with the changes in master, so with the latest dev version):

import {
  OptionalProps,
  Entity,
  MikroORM,
  PrimaryKey,
  Property,
  OneToMany,
  ManyToOne,
  Enum, Collection, Opt,
} from '@mikro-orm/postgresql';

abstract class BaseEntity {

  [OptionalProps]?: 'createdAt' | 'updatedAt';

  @PrimaryKey()
  id!: number;

  @Property({ defaultRaw: 'now()' })
  createdAt = new Date();

  @Property({ defaultRaw: 'now()', onUpdate: () => new Date() })
  updatedAt = new Date();

}

@Entity({ tableName: 'authors' })
class Author extends BaseEntity {

  @Property()
  name!: string;

  @Property({ nullable: true })
  biography?: string;

  @OneToMany(() => Post, post => post.author)
  posts = new Collection<Post>(this);

}

@Entity({ tableName: 'posts' })
class Post extends BaseEntity {

  @Property()
  title!: string;

  @Property()
  content!: string;

  @Property()
  published!: boolean;

  @ManyToOne({
    entity: () => Author,
    deleteRule: 'cascade',
    updateRule: 'cascade',
  })
  author!: Author;

  @Property({ persist: false })
  authorId?: number;

  @OneToMany({
    entity: () => Comment,
    mappedBy: 'post',
  })
  comments = new Collection<Comment>(this);

}

@Entity({ tableName: 'comments' })
class Comment {

  @Enum({
    items: () => CommentObjectTypeEnum,
    nativeEnumName: 'comment_object_type_enum',
    primary: true,
  })
  objectType!: string;

  @Property({ persist: false })
  objectId!: number & Opt;

  @Property()
  content!: string;

  @Property()
  createdAt? = new Date();

  @Property({ onUpdate: () => new Date() })
  updatedAt? = new Date();

  @ManyToOne({
    entity: () => Post,
    name: 'object_id',
    primary: true,
  })
  post!: Post;

}

export enum CommentObjectTypeEnum {
  comment = 'comment',
  post = 'post',
}

let orm: MikroORM;
let author: Author, post: Post, comment: Comment;

beforeAll(async () => {
  orm = await MikroORM.init({
    dbName: '5445',
    entities: [Author, Post, Comment],
    debug: ['query', 'query-params'],
  });
  await orm.schema.refreshDatabase();

  author = orm.em.create(Author, {
    id: 3,
    name: 'author name',
    biography: 'author bio',
  });
  post = orm.em.create(Post, {
    id: 5,
    title: 'post title',
    content: 'post content',
    published: true,
    author,
  });
  comment = orm.em.create(Comment, {
    objectType: CommentObjectTypeEnum.post,
    post: 5,
    content: 'comment content',
    createdAt: new Date(),
    updatedAt: new Date(),
  });
  await orm.em.flush();
  orm.em.clear();
});

afterAll(async () => {
  await orm.close(true);
});

test('define joined columns in leftJoinAndSelect()', async () => {
  const posts = await orm.em
    .createQueryBuilder(Post, 'post')
    .leftJoinAndSelect('post.author', 'author', {}, [
      'id',
      'name',
      'biography',
    ])
    .leftJoinAndSelect(
      'post.comments',
      'comments',
      {
        'comments.objectType': CommentObjectTypeEnum.post,
      },
      ['content', 'createdAt', 'updatedAt'],
    )
    .where({ id: 5 })
    .getResult();

  expect(posts[0].id).toBe(5);
  expect(posts[0].author).toBeDefined();
  expect(posts[0].author.id).toBe(3);
  expect(posts[0].author.name).toBe('author name');
  expect(posts[0].author.biography).toBe('author bio');
  expect(posts[0].comments).toBeDefined();
  expect(posts[0].comments![0]).toBeDefined();
  expect(posts[0].comments![0].content).toBe('comment content');
  expect(posts[0].comments![0].createdAt).toBeDefined();
  expect(posts[0].comments![0].updatedAt).toBeDefined();
});

test('use subquery for comments', async () => {
  const commentsSubQuery = orm.em
    .createQueryBuilder(Comment, 'comments')
    .select([
      'post',
      'objectType',
      'content',
      'createdAt',
      'updatedAt',
    ]);

  const posts = await orm.em
    .createQueryBuilder(Post, 'post')
    .leftJoinAndSelect('post.author', 'author')
    .leftJoinAndSelect(['post.comments', commentsSubQuery], 'comments', {
      objectType: CommentObjectTypeEnum.post,
      content: { $ne: null },
    })
    .where({ id: 5 })
    .getResult();
});

The second test now generates the following query:

select "post".*, "author"."id" as "author__id", "author"."created_at" as "author__created_at", "author"."updated_at" as "author__updated_at", "author"."name" as "author__name", "author"."biography" as "author__biography", "comments"."object_type" as "comments__object_type", "comments"."object_id" as "comments__object_id", "comments"."content" as "comments__content", "comments"."created_at" as "comments__created_at", "comments"."updated_at" as "comments__updated_at" 
  from "posts" as "post" 
  left join "authors" as "author"
    on "post"."author_id" = "author"."id" 
  left join (select "comments"."object_id", "comments"."object_type", "comments"."content", "comments"."created_at", "comments"."updated_at" from "comments" as "comments") as "comments" 
    on "post"."id" = "comments"."object_id" and "comments"."object_type" = 'post' and "comments"."content" is not null 
  where "post"."id" = 5

Of course, this makes little sense, as you are now just replacing with the 4th parameter of qb.joinAndSelect is doing (but in a less performant and less readable way).

Here is the query that approach produces in this test:

select "post".*, "author"."id" as "author__id", "author"."name" as "author__name", "author"."biography" as "author__biography", "comments"."object_type" as "comments__object_type", "comments"."object_id" as "comments__object_id", "comments"."content" as "comments__content", "comments"."created_at" as "comments__created_at", "comments"."updated_at" as "comments__updated_at" 
  from "posts" as "post"
  left join "authors" as "author"
    on "post"."author_id" = "author"."id" 
  left join "comments" as "comments" 
    on "post"."id" = "comments"."object_id" and "comments"."object_type" = 'post' 
where "post"."id" = 5

? can I do entity @filter with filtering by objectType and then use it in joins?

You can use em.applyFilters() to create the condition based on active filters and use that in qb.where().

[B4nan]

Added support for the aliased fields hint, so what you initially tried would work better too: da68503

Still, you would need to pass in list of property names, not column names. Which sounds like a bug on its own, so also did this to fix that: ccdf018

[Laziz2L]

Thank you very much, good man! Answers helped a lot, I can work with it now.

The thing about comments table and what I was searching is the best way to join this table with less code and prettier code. Comments table has two columns - object_type and object_id. object_type tells us to which entyty this comment belongs, and object_id is id of this entity. There are two types now, but it can be a lot of them in future and I would like to have the smallest construction in queries with conditions carried out - for me it seems the better way to do this if we could join virtual entities - object_id condition would be from relations decorators and object_type filtering would be in virtual entity expression. Something like this:

@Entity({
    expression: `
    select object_id as post_id,
           content,
           created_at,
           updated_at
    from comments
    where object_type = ${CommentObjectTypeEnum.post}
`,
})
export class PostComment {
    @Property()
    postId!: number;

    @Property()
    content!: string;

    @Property()
    createdAt!: Date;

    @Property()
    updatedAt!: Date;

    @ManyToOne({
        entity: () => Post,
        name: 'post_id',
        persist: false,
    })
    post?: Post;
}

and join without conditions like this

const posts = await orm.em
    .createQueryBuilder(Post, 'post')
    .leftJoinAndSelect('post.author', 'author', {}, [
      'id',
      'name',
      'biography',
    ])
    .leftJoinAndSelect('post.comments', 'comments') // no extra logic in query
    .where({ id: 5 })
    .getResult();

Maybe there is another way to do this (hiding conditions for join) supported?

And maybe we can set "ON true" condition to join subquery by default if no conditions provided? It would be even better cause subqueries could be generated dinamically.

[B4nan]

But how is this better than having no virtual entity and adding a one additional join condition to the QB? As that feels much simpler to what you want (and does the same, right?).