michaelp7725 Posts - Answer Overflow

michaelp7725

Explore posts from servers

•Created by michaelp7725 on 11/19/2024 in #help

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

TtRPC

•Created by michaelp7725 on 11/4/2024 in #❓-help

What are the benefits of adding db to context rather than importing it directly in the router?

As per title, the docs and most examples I've seen using TRPC put the database into context as follows,

import db from "../db";
export const createContext = async (opts: CreateNextContextOptions) => {
 
  return {
    db,
  };
};

import db from "../db";
export const createContext = async (opts: CreateNextContextOptions) => {
 
  return {
    db,
  };
};

can anyone explain what the actual benefit of this is over just importing it in the routers like below?

import { protectedProcedure, router } from "../trpc";
import { eq } from "drizzle-orm";
import { db } from "../db";
import {
  clients,
} from "../schemas/clients.schema";

export default router({
  update: protectedProcedure
    .input(getAllSchema)
    .query(async ({ ctx, input }) => {
      const updatedClient = await db
    .update(clients)
    .set(input)
    .where(eq(clients.id, input.id))
    .returning();
    return updatedClient
    }),
})

import { protectedProcedure, router } from "../trpc";
import { eq } from "drizzle-orm";
import { db } from "../db";
import {
  clients,
} from "../schemas/clients.schema";

export default router({
  update: protectedProcedure
    .input(getAllSchema)
    .query(async ({ ctx, input }) => {
      const updatedClient = await db
    .update(clients)
    .set(input)
    .where(eq(clients.id, input.id))
    .returning();
    return updatedClient
    }),
})

I'm sure there must be some reasoning so I want to make sure I don't require a needless refactoring later on. Thanks! Michael

3 replies

DTDrizzle Team

•Created by michaelp7725 on 6/10/2024 in #help

Optionally chaining joins and typescript issues.

Hey everyone, I'm currently a bit stuck trying to brainstorm how to solve the following problem and I'm sure there's an easy way to do it that I'm completely overlooking. I'm implementing a backend route for a data table where certain columns can be shown or hidden. I want my database query to only include joins of tables when a relevant column is to be shown. I'm using Typescript, Drizzle, PostgreSQL, trpc, react, tanstack table and zod if any of that is relevant. my basic query is as follows:

export function getAllAssets(
    { columns, globalFilter, pageIndex, pageSize, sorting }: GetAllAssetsInput,
    db: TRPCContext["db"]
) {
    const selectFields = getSelectFields(columns);
    let query = db.select(selectFields).from(assets).$dynamic();
    if (columns.location_name) {
        query = withLocation(query);
    }
    if (columns.status_name) {
        query = withStatus(query);
    }
    if (columns.client_name) {
        query = withClient(query);
    }
    if (globalFilter !== "") {
        query = withGlobalFilter(query, globalFilter, columns);
    } else {
        query = query.where(isNull(assets.deletedAt));
    }
    if (sorting.length !== 0) {
        query = withSorting(query, sorting);
    } else {
        query = query.orderBy(assets.id);
    }
    const finalQuery = withPagination(query, pageIndex, pageSize);
    return finalQuery;
}

where each corresponding join is done through a function like this:

function withStatus<T extends PgSelectQueryBuilder>(query: T) {
    return query.leftJoin(assetStatuses, eq(assets.statusId, assetStatuses.id));
}

The problem is that typescript is complaining that the type of query is changing between it's initialization and with each re-assignment. I've tried typecasting query but i'm unsure what type to use as drizzle doesn't have much documention on its types. If anyone could point me in the right direction, that would be great! Thanks, Michael

3 replies

Gaming

Programming