4 replies

creating a generic selection function

node-postgres

drizzle orm

Hey all,

I'm trying to extract some shared logic into a function that will generate a query based off an input selectedFields. I can't for the life of me figure out how to get the generic right, and it seems like this should be a fairly common use case:

export function createBaseQuery<TSelection extends SelectedFields>(
  selectedFields: TSelection,
  filters: SQL[],
  globalFilter: string,
) {
  const baseQuery = db
    .select(selectedFields)
    .from(assetTable)
    .innerJoin(locationTable, eq(assetTable.locationId, locationTable.id))
    .innerJoin(assetStatusTable, eq(assetTable.statusId, assetStatusTable.id))
    .innerJoin(modelTable, eq(assetTable.modelId, modelTable.id))
    .innerJoin(clientTable, eq(assetTable.clientId, clientTable.id))
    .innerJoin(
      manufacturerTable,
      eq(modelTable.manufacturerId, manufacturerTable.id),
    )
    .leftJoin(
      modelImageTable,
      eq(modelTable.defaultImageId, modelImageTable.id),
    )
    .where(
      and(
        ...filters,
        getGlobalFilters(globalFilter),
      ),
    )
    .$dynamic();

  return baseQuery;
}

export function createBaseQuery<TSelection extends SelectedFields>(
  selectedFields: TSelection,
  filters: SQL[],
  globalFilter: string,
) {
  const baseQuery = db
    .select(selectedFields)
    .from(assetTable)
    .innerJoin(locationTable, eq(assetTable.locationId, locationTable.id))
    .innerJoin(assetStatusTable, eq(assetTable.statusId, assetStatusTable.id))
    .innerJoin(modelTable, eq(assetTable.modelId, modelTable.id))
    .innerJoin(clientTable, eq(assetTable.clientId, clientTable.id))
    .innerJoin(
      manufacturerTable,
      eq(modelTable.manufacturerId, manufacturerTable.id),
    )
    .leftJoin(
      modelImageTable,
      eq(modelTable.defaultImageId, modelImageTable.id),
    )
    .where(
      and(
        ...filters,
        getGlobalFilters(globalFilter),
      ),
    )
    .$dynamic();

  return baseQuery;
}

if i remove the generic, the return type is [x: string]: unknown regardless of what I use for selectedFields,
if I use the provided type SelectedFields from :

 import type { SelectedFields } from "drizzle-orm/pg-core";

 import type { SelectedFields } from "drizzle-orm/pg-core";

the output is any.
If i use the other SelectedFields type from:

 import { type SelectedFields} from "drizzle-orm";

 import { type SelectedFields} from "drizzle-orm";

It's a generic that requires two arguments, TColumn and TTable,
however I don't see any helper for generating these two from a given table.

Has anyone successfully been able to abstract a select query?

Thanks for any help you can give,

Michael.

creating a generic selection function

node-postgres

drizzle orm

export function createBaseQuery<TSelection extends SelectedFields>(
  selectedFields: TSelection,
  filters: SQL[],
  globalFilter: string,
) {
  const baseQuery = db
    .select(selectedFields)
    .from(assetTable)
    .innerJoin(locationTable, eq(assetTable.locationId, locationTable.id))
    .innerJoin(assetStatusTable, eq(assetTable.statusId, assetStatusTable.id))
    .innerJoin(modelTable, eq(assetTable.modelId, modelTable.id))
    .innerJoin(clientTable, eq(assetTable.clientId, clientTable.id))
    .innerJoin(
      manufacturerTable,
      eq(modelTable.manufacturerId, manufacturerTable.id),
    )
    .leftJoin(
      modelImageTable,
      eq(modelTable.defaultImageId, modelImageTable.id),
    )
    .where(
      and(
        ...filters,
        getGlobalFilters(globalFilter),
      ),
    )
    .$dynamic();

  return baseQuery;
}

export function createBaseQuery<TSelection extends SelectedFields>(
  selectedFields: TSelection,
  filters: SQL[],
  globalFilter: string,
) {
  const baseQuery = db
    .select(selectedFields)
    .from(assetTable)
    .innerJoin(locationTable, eq(assetTable.locationId, locationTable.id))
    .innerJoin(assetStatusTable, eq(assetTable.statusId, assetStatusTable.id))
    .innerJoin(modelTable, eq(assetTable.modelId, modelTable.id))
    .innerJoin(clientTable, eq(assetTable.clientId, clientTable.id))
    .innerJoin(
      manufacturerTable,
      eq(modelTable.manufacturerId, manufacturerTable.id),
    )
    .leftJoin(
      modelImageTable,
      eq(modelTable.defaultImageId, modelImageTable.id),
    )
    .where(
      and(
        ...filters,
        getGlobalFilters(globalFilter),
      ),
    )
    .$dynamic();

  return baseQuery;
}

if i remove the generic, the return type is [x: string]: unknown regardless of what I use for selectedFields,
if I use the provided type SelectedFields from :

 import type { SelectedFields } from "drizzle-orm/pg-core";

 import type { SelectedFields } from "drizzle-orm/pg-core";

the output is any.
If i use the other SelectedFields type from:

 import { type SelectedFields} from "drizzle-orm";

 import { type SelectedFields} from "drizzle-orm";

creating a generic selection function

creating a generic selection function

Similar Threads

Similar Threads

Similar Threads