Drizzle Team•2y ago

Typing columns based on Table

I'm trying to make a generic paginateQuery function that would let me pass in the table and then an array of only columns from that table would be accepted. Is there any way to do this with the Drizzle types? I feel like I have gotten close a few times but nothing fully gets there. Thanks!

const getFilterSql = <TColumn extends AnyPgColumn>(
  filter: string,
  filterableColumns: TColumn[],
) => {
  if (!filter || filter.trim() === '') return or();

  return or(...filterableColumns.map((c) => ilike(c, `%${filter}%`)));
};


export const paginatedQuery = <TTable extends AnyPgTable>(
  context: PgDatabase<NodePgQueryResultHKT>,
  table: TTable,
  paginationDto: PaginationDto,
  filterableColumns: (/* Not too sure what to put here */)[],
  defaultWhere?: SQL,
) => {
  return context
    .select()
    .from(table)
    .where(
      and(defaultWhere, getFilterSql(paginationDto.filter, filterableColumns)), // TypeScript gives an error here
    )
    .offset(paginationDto.page * paginationDto.pageSize)
    .limit(paginationDto.pageSize);
};

const getFilterSql = <TColumn extends AnyPgColumn>(
  filter: string,
  filterableColumns: TColumn[],
) => {
  if (!filter || filter.trim() === '') return or();

  return or(...filterableColumns.map((c) => ilike(c, `%${filter}%`)));
};


export const paginatedQuery = <TTable extends AnyPgTable>(
  context: PgDatabase<NodePgQueryResultHKT>,
  table: TTable,
  paginationDto: PaginationDto,
  filterableColumns: (/* Not too sure what to put here */)[],
  defaultWhere?: SQL,
) => {
  return context
    .select()
    .from(table)
    .where(
      and(defaultWhere, getFilterSql(paginationDto.filter, filterableColumns)), // TypeScript gives an error here
    )
    .offset(paginationDto.page * paginationDto.pageSize)
    .limit(paginationDto.pageSize);
};

2 Replies

NoahhOP•2y ago

Wish I had given it a few more minutes...

export const paginatedQuery = <TTable extends AnyPgTable>(
  context: PgDatabase<NodePgQueryResultHKT>,
  table: TTable,
  paginationDto: PaginationDto,
  filterableColumns: (keyof TTable['_']['columns'])[],
  defaultWhere?: SQL,
) => {
  return context
    .select()
    .from(table)
    .where(
      and(defaultWhere, getFilterSql(paginationDto.filter, filterableColumns)),
    )
    .offset(paginationDto.page * paginationDto.pageSize)
    .limit(paginationDto.pageSize);
};

export const paginatedQuery = <TTable extends AnyPgTable>(
  context: PgDatabase<NodePgQueryResultHKT>,
  table: TTable,
  paginationDto: PaginationDto,
  filterableColumns: (keyof TTable['_']['columns'])[],
  defaultWhere?: SQL,
) => {
  return context
    .select()
    .from(table)
    .where(
      and(defaultWhere, getFilterSql(paginationDto.filter, filterableColumns)),
    )
    .offset(paginationDto.page * paginationDto.pageSize)
    .limit(paginationDto.pageSize);
};

this might work, i have to restructure some stuff to see, but it autocompletes the strings in the array when I use the function. not sure if there is a better way to do it where I can pass in table.id instead of "id", would still love to know that Nevermind again! Man I took so long working on this and got there right as I posted it :P Here is my final product:

const getFilterSql = <TColumn extends AnyPgColumn>(
  filter: string,
  filterableColumns: TColumn[],
) => {
  if (!filter || filter.trim() === '') return or();

  return or(...filterableColumns.map((c) => ilike(c, `%${filter}%`)));
};

export const paginatedQuery = <
  TTable extends AnyPgTable,
  TColumnKey extends keyof TTable['_']['columns'],
  TColumn extends TTable['_']['columns'][TColumnKey],
>(
  context: PgDatabase<NodePgQueryResultHKT>,
  table: TTable,
  paginationDto: PaginationDto,
  filterableColumns: TColumn[],
  defaultWhere?: SQL,
) => {
  return context
    .select()
    .from(table)
    .where(
      and(defaultWhere, getFilterSql(paginationDto.filter, filterableColumns)),
    )
    .offset(paginationDto.page * paginationDto.pageSize)
    .limit(paginationDto.pageSize);
};

const getFilterSql = <TColumn extends AnyPgColumn>(
  filter: string,
  filterableColumns: TColumn[],
) => {
  if (!filter || filter.trim() === '') return or();

  return or(...filterableColumns.map((c) => ilike(c, `%${filter}%`)));
};

export const paginatedQuery = <
  TTable extends AnyPgTable,
  TColumnKey extends keyof TTable['_']['columns'],
  TColumn extends TTable['_']['columns'][TColumnKey],
>(
  context: PgDatabase<NodePgQueryResultHKT>,
  table: TTable,
  paginationDto: PaginationDto,
  filterableColumns: TColumn[],
  defaultWhere?: SQL,
) => {
  return context
    .select()
    .from(table)
    .where(
      and(defaultWhere, getFilterSql(paginationDto.filter, filterableColumns)),
    )
    .offset(paginationDto.page * paginationDto.pageSize)
    .limit(paginationDto.pageSize);
};

mcgrealife•2y ago

@ngregrichardson nice! do you have ane example using the paginatedQuery? I am having trouble passing a value that satisfies TTable type.

Gaming

Programming

Typing columns based on Table