Drizzle Team•2mo ago

creating a generic selection function

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.

2 Replies

michaelp7725OP•2mo ago

this looks like this is a fairly common question on here but I can't find a single answer that actually solves the problem. This seems like basic abstraction, is there really no way to do this without type casting random parts of the query to avoid it?

Angelelz•5w ago

This is not as simple as you might think, in fact, this is the reason why Drizzle is popular. Generating correct types for all those joined tables where they might or might not be null depending of the type of the join Also, you can have nested objects in the selected fields and drizzle takes care of that for you

Gaming

Programming

creating a generic selection function