Drizzle Team•3w ago

Custom postgres JSON_AGG function help

I want to create a function simmilar to db.select that for JsonAgg. I also want to be able to do things like jsonAgg({...}).orderBy(...).filter(...).as(..) In my sql queries to make it more like the rest of the ORM. (I need these orderBy and filters optionally, and I know I can just pass them in as arguments but i'm trying to keep the api similar to how the rest of drizzle would implement a feature like this. I tried extending the SQL class function like this

class JsonAgg<
  T extends SelectedFields,
  AllowNull extends boolean = false,
> extends SQL<never> {
  private filterProp: SQL | null = null;
  private orderByProp: SQL | null = null;
  private allowNull: AllowNull;

  constructor(select: T, allowNull = false as AllowNull) {
    const builtJson = jsonBuild(select);
    super([sql`COALESCE(json_agg(${builtJson}`]);
    this.allowNull = allowNull;
  }

  orderBy(orderBy: SQL | undefined) {
    this.orderByProp = orderBy ?? null;
    return this;
  }

  filter(filterProp: SQL | undefined) {
    this.filterProp = filterProp ?? null;
    return this;
  }

  //@ts-expect-error this function is hard to override
  override as(
    alias: string,
  ): SQL.Aliased<
    AllowNull extends true
      ? SelectResultFields<T>[] | null
      : SelectResultFields<T>[]
  > {
    if (this.orderByProp) {
      this.append(sql`ORDER BY ${this.orderByProp}`);
    }
    this.append(sql`)`); 
    if (this.filterProp) {
      this.append(sql`FILTER (WHERE ${this.filterProp})`);
    }
    this.append(sql`,${this.allowNull ? sql.raw("null") : sql.raw("'[]'")})`);
    if (!alias) throw new Error("deprecated");
    return super.as(alias);
  }
}

class JsonAgg<
  T extends SelectedFields,
  AllowNull extends boolean = false,
> extends SQL<never> {
  private filterProp: SQL | null = null;
  private orderByProp: SQL | null = null;
  private allowNull: AllowNull;

  constructor(select: T, allowNull = false as AllowNull) {
    const builtJson = jsonBuild(select);
    super([sql`COALESCE(json_agg(${builtJson}`]);
    this.allowNull = allowNull;
  }

  orderBy(orderBy: SQL | undefined) {
    this.orderByProp = orderBy ?? null;
    return this;
  }

  filter(filterProp: SQL | undefined) {
    this.filterProp = filterProp ?? null;
    return this;
  }

  //@ts-expect-error this function is hard to override
  override as(
    alias: string,
  ): SQL.Aliased<
    AllowNull extends true
      ? SelectResultFields<T>[] | null
      : SelectResultFields<T>[]
  > {
    if (this.orderByProp) {
      this.append(sql`ORDER BY ${this.orderByProp}`);
    }
    this.append(sql`)`); 
    if (this.filterProp) {
      this.append(sql`FILTER (WHERE ${this.filterProp})`);
    }
    this.append(sql`,${this.allowNull ? sql.raw("null") : sql.raw("'[]'")})`);
    if (!alias) throw new Error("deprecated");
    return super.as(alias);
  }
}

The issue is I can't find a function I can actually override that will let me finish the builder pattern. I know at this point I could just not override as and not extend SQL and add a build function. I was just wondering if there was a way to do this without having to do that?

0 Replies

No replies yetBe the first to reply to this messageJoin

Gaming

Programming

Custom postgres JSON_AGG function help