DT
Drizzle Team•15mo ago
Noahh

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
Noahh
Noahh•15mo 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
mcgrealife•14mo ago
@ngregrichardson nice! do you have ane example using the paginatedQuery? I am having trouble passing a value that satisfies TTable type.
Want results from more Discord servers?
Add your server
More Posts
Does onConflictDoUpdate work with composite primary keys?I have a model defined below: ```ts export const userDevices = pgTable( 'user_devices', { iSupport for multiple with statements?Is it possible to have multiple with statements? ``` const carriers = await tx .with(cahow to use placeholders for Prepared Insert statements?Is this possible?use constant in between operatorHello guys, Is there any way to use constant in between filter instead of column?Type error: Could not find a declaration file for module 'drizzle-kit'.Any idea how to fix this problem? I try to run this: npm i --save-dev @types/drizzle-kit: npm ERR! Typescript error that doesn't make a whole lot of sense when calling db.select or db.insertThis is my account model ``` export const accounts = mysqlTable("accounts", { id: nanoid("id", {Are default values not transferred over via drizzle-zod?It seems like when I use a `default([])` in my `schema` file that when I create a Zod object, I haveReplace on InsertI can see that there is a `.ignore()` option added to insert, but is there an equivalent `.replace()