Nested select query from multiple tables

[issamwahbi]

Hello

First of all thank you for this great package.
I am using Slonik in a DDD context where Slonik is my persistance layer.
I need to do nested Select to retrieve in one shot multiple table and construct one object from them.
For exemple, I have 2 Zod schema as bellow :

export const employeeSchema = z.object({
  id: z.string().uuid(),
  organizationId: z.string().uuid(),
  createdAt: z.preprocess((val: any) => new Date(val), z.date()),
  updatedAt: z.preprocess((val: any) => new Date(val), z.date()),
  firstName: z.string().min(2).max(20),
  lastName: z.string().min(2).max(20),
  email: z.string().email(),
  phoneNumber: z.string().min(10).max(10).optional().nullable(),
  accountId: z.string().uuid(),
  reference: z.string().min(1).max(30).optional().nullable(),
  department: z.string().min(1).max(30).optional().nullable(),
  status: z.string(),
  vehicules: z.array(vehiculeModelSchema),
});

export const vehiculeModelSchema = z.object({
  id: z.string().uuid(),
  organizationId: z.string().uuid(),
  createdAt: z.preprocess((val: any) => new Date(val), z.date()),
  updatedAt: z.preprocess((val: any) => new Date(val), z.date()),
  employeeId: z.string().uuid(),
  name: z.string().min(2).max(20),
  licencePlate: z.string().min(2).max(20),
});

I cannot do queries directly on the vehicules table following the DDD principales... So I need to query the employees table and get the vehicules along it.

The SQL query that I am trying to execute is the following :

const query = sql.type(employeeSchema)`
    SELECT employees.*, 
        (
            SELECT json_agg(vehicles)
            FROM ${sql.identifier(["vehicles"])}
            WHERE vehicles."employeeid" = employees."id"
        ) AS vehicle
    FROM ${sql.identifier([this.tableName])}
    LIMIT ${params.limit}
    OFFSET ${params.offset}
`;

It is working well when done like above. But since I am using a repository pattern I need to make it dynamic.
I did the following, which is working well. But I cannot tell if the solution above is bullet prove or has some downsides:

const nestedModelsQuery = this.generateNestedModelsQuery()
    const query = sql.type(this.schema)`
    SELECT ${sql.identifier([this.tableName])}.*,
    ${nestedModelsQuery}
    FROM ${sql.identifier([this.tableName])}
    LIMIT ${params.limit}
    OFFSET ${params.offset}
    `;

  private generateNestedModelsQuery() {
    const nestedModelsNames = this.getNestedModelsNames();
    if(nestedModelsNames.length === 0) return '';
    const selectQueries = nestedModelsNames.map(nestedObjectName => {
      const joinCondition = sql`${sql.identifier([nestedObjectName])}."employeeId" = ${sql.identifier([this.tableName])}."id"`;
      return sql`(SELECT json_agg(${sql.identifier([nestedObjectName])}) FROM ${sql.identifier([nestedObjectName])} WHERE ${joinCondition}) as ${sql.identifier([nestedObjectName])} `;
    });
  
    const nestedObjectQuery = selectQueries[0];
  
    return nestedObjectQuery;
  }