html_extraordinaire Comments

html_extraordinaire

Explore posts from servers

JCHJava Community | Help. Code. Learn.DTDrizzle Team

•Created by html_extraordinaire on 10/5/2024 in #help

Lateral joins

Looks like maybe it's not ready to merge because the PR only supports Postgres. Someone probably has to go add support for the other databases as well.

6 replies

DTDrizzle Team

•Created by html_extraordinaire on 10/5/2024 in #help

Lateral joins

Thanks for the response.

6 replies

DTDrizzle Team

•Created by html_extraordinaire on 10/5/2024 in #help

Lateral joins

How would you write the raw SQL of that query to get type-safe results though?

6 replies

DTDrizzle Team

•Created by html_extraordinaire on 8/22/2024 in #help

Looking for suggestions

If you change date_series_date to date, that'll cause the ambiguous column error.

2 replies

DTDrizzle Team

•Created by code9 on 7/15/2024 in #help

slow code suggestions

Also I used to work at a pretty big corporation and they had really strict security standards. They ran like two different endpoint security programs but it really slowed things down. I remember Intellisense in my editor being slow as hell after they added that second one. If you've got endpoint security that could also be it.

14 replies

DTDrizzle Team

•Created by code9 on 7/15/2024 in #help

slow code suggestions

That makes sense right? I think Drizzle's type system probably leans more heavily on type gymnastics.

14 replies

DTDrizzle Team

•Created by code9 on 7/15/2024 in #help

slow code suggestions

The difference is probably that Prisma generates the types every time you change the schema and run prisma generate and Drizzle doesn't. Every time your editor needs to find a type, Drizzle is going to look at some table in your schema file first. I do wonder if that means breaking those two big files into smaller ones would make TypeScript inference faster but I doubt it.

14 replies

DTDrizzle Team

•Created by html_extraordinaire on 7/22/2024 in #help

Are dynamic selects with type inference possible?

Still wondering if it's possible to get type inference from passing a boolean to the function though.

9 replies

DTDrizzle Team

•Created by html_extraordinaire on 7/22/2024 in #help

Are dynamic selects with type inference possible?

Settled on this.

type TotalShrinkInput = {
  irrigationClientId?: IrrigationClient['id'];
  irrigationYearId: IrrigationYear['id'];
};

type TotalShrinkOpts<TSelected> = {
  select?: TSelected;
  groupBy?: (PgColumn | SQL | SQL.Aliased)[];
};

export function shrinkSubquery<TSelected extends SelectedFields>(
  input: TotalShrinkInput,
  opts: TotalShrinkOpts<TSelected>,
  tx = db
) {
  const sq = tx
    .select({
      ...(opts?.select as TSelected),
      shrink: sum(shrink.amount).mapWith(Number).as('shrink'),
    })
    .from(shrink)
    .innerJoin(irrigationYear, eq(irrigationYear.id, shrink.irrigationYearId))
    .where(
      input.irrigationClientId
        ? and(
            eq(irrigationYear.id, input.irrigationYearId),
            eq(shrink.irrigationClientId, input.irrigationClientId)
          )
        : eq(irrigationYear.id, input.irrigationYearId)
    );

  if (opts?.groupBy) sq.groupBy(...opts.groupBy);

  return sq.as('shrinkSq');
}

type TotalShrinkInput = {
  irrigationClientId?: IrrigationClient['id'];
  irrigationYearId: IrrigationYear['id'];
};

type TotalShrinkOpts<TSelected> = {
  select?: TSelected;
  groupBy?: (PgColumn | SQL | SQL.Aliased)[];
};

export function shrinkSubquery<TSelected extends SelectedFields>(
  input: TotalShrinkInput,
  opts: TotalShrinkOpts<TSelected>,
  tx = db
) {
  const sq = tx
    .select({
      ...(opts?.select as TSelected),
      shrink: sum(shrink.amount).mapWith(Number).as('shrink'),
    })
    .from(shrink)
    .innerJoin(irrigationYear, eq(irrigationYear.id, shrink.irrigationYearId))
    .where(
      input.irrigationClientId
        ? and(
            eq(irrigationYear.id, input.irrigationYearId),
            eq(shrink.irrigationClientId, input.irrigationClientId)
          )
        : eq(irrigationYear.id, input.irrigationYearId)
    );

  if (opts?.groupBy) sq.groupBy(...opts.groupBy);

  return sq.as('shrinkSq');
}

9 replies

DTDrizzle Team

•Created by html_extraordinaire on 7/22/2024 in #help

Are dynamic selects with type inference possible?

Or actually I guess the issue is that groupBy needs a column so I can just cast column to a PgColumn.

9 replies

DTDrizzle Team

•Created by html_extraordinaire on 7/22/2024 in #help

Are dynamic selects with type inference possible?

Is this too complex? lol

9 replies

DTDrizzle Team

•Created by html_extraordinaire on 7/22/2024 in #help

Are dynamic selects with type inference possible?

The column in the if statement is also not being properly inferred but I'm guessing this has to do with my use of Object.values?

9 replies

DTDrizzle Team

•Created by html_extraordinaire on 7/22/2024 in #help

Are dynamic selects with type inference possible?

Removing the explicit type from the select function seems to have restored type inference on the other fields now.

9 replies

DTDrizzle Team

•Created by html_extraordinaire on 7/22/2024 in #help

Are dynamic selects with type inference possible?

I guess the problem with adding <T & SelectedFields<PgColumn, PgTable>> to the select function is that you don't get type inference on the other fields. How do I fix that? lol

9 replies

DTDrizzle Team

•Created by html_extraordinaire on 7/22/2024 in #help

Are dynamic selects with type inference possible?

After I worked on it a little bit I came up with this.

type TotalShrinkInput = {
  irrigationYearId: number;
  irrigationClientId?: number;
};

type TotalShrinkOpts<T> = {
  tx: typeof db;
  groupBy?: T;
};

export function shrinkSubquery<T extends SelectedFields<PgColumn, PgTable>>(
  input: TotalShrinkInput,
  opts: TotalShrinkOpts<T> = { tx: db }
) {
  const andArgs = {
    irrigationYearId: eq(irrigationYear.id, input.irrigationYearId),
    irrigationClientId: eq(
      auxiliaryIssuance.irrigationClientId,
      input.irrigationClientId!
    ),
  };
  const filteredArgs = Object.entries(andArgs)
    .filter(([k, v]) => {
      switch (k as keyof typeof andArgs) {
        case 'irrigationYearId':
          return true;
        case 'irrigationClientId':
          if (input.irrigationClientId) return true;
          return false;
      }
    })
    .map(([k, v]) => v);
  const whereClause = and(...filteredArgs);

  const sq = opts.tx
    .select<T & SelectedFields<PgColumn, PgTable>>({
      ...opts.groupBy as T,
      shrink: sum(shrink.amount).mapWith(Number).as('shrink'),
    })
    .from(shrink)
    .innerJoin(irrigationYear, eq(irrigationYear.id, shrink.irrigationYearId))
    .where(whereClause);


  if (opts.groupBy) {
    const [column] = Object.values(opts.groupBy);
    sq.groupBy(column); // also not being property inferred
  }

  return sq.as('shrinkSq');
}

type TotalShrinkInput = {
  irrigationYearId: number;
  irrigationClientId?: number;
};

type TotalShrinkOpts<T> = {
  tx: typeof db;
  groupBy?: T;
};

export function shrinkSubquery<T extends SelectedFields<PgColumn, PgTable>>(
  input: TotalShrinkInput,
  opts: TotalShrinkOpts<T> = { tx: db }
) {
  const andArgs = {
    irrigationYearId: eq(irrigationYear.id, input.irrigationYearId),
    irrigationClientId: eq(
      auxiliaryIssuance.irrigationClientId,
      input.irrigationClientId!
    ),
  };
  const filteredArgs = Object.entries(andArgs)
    .filter(([k, v]) => {
      switch (k as keyof typeof andArgs) {
        case 'irrigationYearId':
          return true;
        case 'irrigationClientId':
          if (input.irrigationClientId) return true;
          return false;
      }
    })
    .map(([k, v]) => v);
  const whereClause = and(...filteredArgs);

  const sq = opts.tx
    .select<T & SelectedFields<PgColumn, PgTable>>({
      ...opts.groupBy as T,
      shrink: sum(shrink.amount).mapWith(Number).as('shrink'),
    })
    .from(shrink)
    .innerJoin(irrigationYear, eq(irrigationYear.id, shrink.irrigationYearId))
    .where(whereClause);


  if (opts.groupBy) {
    const [column] = Object.values(opts.groupBy);
    sq.groupBy(column); // also not being property inferred
  }

  return sq.as('shrinkSq');
}

It seems to know now that irrigationClientId is a field on the subquery that gets returned from the function:

const shrinkSq = shrinkSubquery(
  { irrigationYearId: input.irrigationYearId },
  { groupBy: { irrigationClientId: shrink.irrigationClientId }, tx }
);

const shrinkSq = shrinkSubquery(
  { irrigationYearId: input.irrigationYearId },
  { groupBy: { irrigationClientId: shrink.irrigationClientId }, tx }
);

But what if I just want to pass "groupByIrrigationClientId: true"? Can I get type inference from the function somehow?

9 replies

DTDrizzle Team

•Created by html_extraordinaire on 7/19/2024 in #help

Subquery in select statement?

So what I ended up doing was doing a .leftJoin(subquery, sql'true') instead of putting it into select but would it be even better to put it in a CTE? What would the difference be if any?

9 replies

DTDrizzle Team

•Created by html_extraordinaire on 7/19/2024 in #help

Subquery in select statement?

Appreciate the help, thank you!

9 replies

DTDrizzle Team

•Created by html_extraordinaire on 7/19/2024 in #help

Subquery in select statement?

Ah, question though. Does this query happen on each row that gets returned or just once and the result is "cached"?

9 replies

DTDrizzle Team