Query builder, nested partial select does not work

[dor6-deepcred]

I'm running the following query and I want only to select the email of the user (memory wise)

const qb = em.createQueryBuilder(AuditLogParams, "a");
const result = await qb
	.select(["a.*", "u.email"])
	.leftJoin("a.user", "u")
	.leftJoinAndSelect("a.ticketAuditLog", "al");

raw sql:

select 
  "a".*, 
  "u"."email", 
  "al"."id" as "al__id", 
  "al"."created_at" as "al__created_at", 
  "al"."updated_at" as "al__updated_at", 
  "al"."params_id" as "al__params_id", 
  "al"."event_type" as "al__event_type", 
  "al"."ticket_id" as "al__ticket_id", 
  "al"."id" as "ticket_audit_log_id" 
from 
  "audit_log_params" as "a" 
  left join "users" as "u" on "a"."user_id" = "u"."id" 
  left join "ticket_audit_logs" as "al" on "a"."id" = "al"."params_id"

this is the result I get:

[
  AuditLogParams {
    id: 'ec9337e0-5a74-43ea-bff4-5dba5162789c',
    createdAt: 2023-05-03T09:23:27.030Z,
    updatedAt: 2023-05-03T09:23:27.030Z,
    company: Ref<Company<c099e584-74a2-41dc-bf4f-cd6de1f8e5a1>> { entity: [(Company)] },
    data: null,
    ticketAuditLog: TicketAuditLog {
      id: 'de063fc1-b024-4271-af09-d371d741f5b4',
      createdAt: 2023-05-03T09:23:27.035Z,
      updatedAt: 2023-05-03T09:23:27.035Z,
      params: [AuditLogParams],
      eventType: 'created',
      ticket: [(Ticket)]
    },
    integrationAuditLog: undefined,
    integrationResourceAuditLog: undefined,
    integrationResourceRoleAuditLog: undefined,
    bundleAuditLog: undefined,
    approvalAlgorithmAuditLog: undefined,
    accessReviewAuditLog: undefined,
    policyAuditLog: undefined,
    user: (User) { id: '51907708-307b-4587-a89a-4c1a3f8d081f' }
  }
]

as you can see the email field in the user is missing

if I'm using 'leftJoinAndSelect' it does map the user fields but than I cant select only the email:

const qb = em.createQueryBuilder(AuditLogParams, "a");
const result = await qb
	.select(["a.*"])
	.leftJoinAndSelect("a.user", "u")
	.leftJoinAndSelect("a.ticketAuditLog", "al");

raw sql:

select 
  "a".*, 
  "u"."id" as "u__id", 
  "u"."created_at" as "u__created_at", 
  "u"."updated_at" as "u__updated_at", 
  "u"."company_id" as "u__company_id", 
  "u"."deleted_at" as "u__deleted_at", 
  "u"."email" as "u__email", 
  "u"."role" as "u__role", 
  "u"."given_name" as "u__given_name", 
  "u"."family_name" as "u__family_name", 
  "al"."id" as "al__id", 
  "al"."created_at" as "al__created_at", 
  "al"."updated_at" as "al__updated_at", 
  "al"."params_id" as "al__params_id", 
  "al"."event_type" as "al__event_type", 
  "al"."ticket_id" as "al__ticket_id", 
  "al"."id" as "ticket_audit_log_id" 
from 
  "audit_log_params" as "a" 
  left join "users" as "u" on "a"."user_id" = "u"."id" 
  left join "ticket_audit_logs" as "al" on "a"."id" = "al"."params_id"

results in:

[
  AuditLogParams {
    id: 'ec9337e0-5a74-43ea-bff4-5dba5162789c',
    createdAt: 2023-05-03T09:23:27.030Z,
    updatedAt: 2023-05-03T09:23:27.030Z,
    company: Ref<Company<c099e584-74a2-41dc-bf4f-cd6de1f8e5a1>> { entity: [(Company)] },
    data: null,
    ticketAuditLog: TicketAuditLog {
      id: 'de063fc1-b024-4271-af09-d371d741f5b4',
      createdAt: 2023-05-03T09:23:27.035Z,
      updatedAt: 2023-05-03T09:23:27.035Z,
      params: [AuditLogParams],
      eventType: 'created',
      ticket: [(Ticket)]
    },
    integrationAuditLog: undefined,
    integrationResourceAuditLog: undefined,
    integrationResourceRoleAuditLog: undefined,
    bundleAuditLog: undefined,
    approvalAlgorithmAuditLog: undefined,
    accessReviewAuditLog: undefined,
    policyAuditLog: undefined,
    user: User {
      id: '51907708-307b-4587-a89a-4c1a3f8d081f',
      createdAt: 2023-05-03T09:23:25.825Z,
      updatedAt: 2023-05-16T11:09:38.926Z,
      company: [Ref<Company<c099e584-74a2-41dc-bf4f-cd6de1f8e5a1>>],
      deletedAt: null,
      email: 'acme-deepcred@acme-deepcred.com',
      role: 'admin',
      givenName: 'Admin',
      familyName: 'User',
    }
  }
]

[B4nan]

This is indeed not supported, only relations that are joined via the (left)JoinAndSelect method are mapped.

But it should work with the em.find.

[dor6-deepcred]

@B4nan it does not:

const result = await em.find(AuditLogParams, {}, { populate: ["user.email"] });

reuslts in:

[
  AuditLogParams {
    id: 'ec9337e0-5a74-43ea-bff4-5dba5162789c',
    createdAt: 2023-05-03T09:23:27.030Z,
    updatedAt: 2023-05-03T09:23:27.030Z,
    company: Ref<Company<c099e584-74a2-41dc-bf4f-cd6de1f8e5a1>> { entity: [(Company)] },
    data: null,
    ticketAuditLog: (TicketAuditLog) {
      id: 'de063fc1-b024-4271-af09-d371d741f5b4',
      params: [AuditLogParams]
    },
    integrationAuditLog: null,
    integrationResourceAuditLog: null,
    integrationResourceRoleAuditLog: null,
    bundleAuditLog: null,
    approvalAlgorithmAuditLog: null,
    accessReviewAuditLog: null,
    policyAuditLog: null,
    user: User {
      id: '51907708-307b-4587-a89a-4c1a3f8d081f',
      createdAt: 2023-05-03T09:23:25.825Z,
      updatedAt: 2023-05-16T11:09:38.926Z,
      company: [Ref<Company<c099e584-74a2-41dc-bf4f-cd6de1f8e5a1>>],
      email: 'acme-deepcred@acme-deepcred.com',
      role: 'admin',
      givenName: 'Admin',
      familyName: 'User',
      deletedAt: null,
    }
  }
]

[B4nan]

That is not how you do partial loading with em.find, you need to use fields, not populate.

[dor6-deepcred]

@B4nan thanks, sorry my mistake.
the problem is what i actually want to do is left join on the user as it is not required but inner join on the ticketAuditLogs
while selecting partial fields. there's not seems to be a solution for doing that

[B4nan]

I think it should be rather easy to implement it the QB support way:

const qb = em.createQueryBuilder(AuditLogParams, "a");
const result = await qb
//	.select(["a.*"]) // this is not really necessary, root entity is selected automatically
	.leftJoinAndSelect("a.user", "u", {}, ["email"])
	.leftJoinAndSelect("a.ticketAuditLog", "al");

The problem with mapping the things you select explicitly is that they might be aliased, or can be even raw fragments, so it would be harder to detect what you were up to - moreover, the approach with leftJoinAndSelect automatically handles prefixing of the field aliases, so you never fall into conflicts in the select part, e.g. imagine your AuditLogParams would have an email column too.

[dor6-deepcred]

@B4nan
that will be very cool & useful feature if it would be possible to add fields I want to select from in the query builder in join&select.
the reason we will need to use query builder is a lot of time for improving performance & one of the best way to improve query performance is to select few fields. I think it will be a great addition for the library
also i would think of adding an options form for the args because it will require to give the callback a lot of undefined to use the feature especially with inner join