2 replies

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;
}

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));
}

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

Drizzle Team•2y ago•

2 replies

michaelp7725

Optionally chaining joins and typescript issues.

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;
}

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));
}

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

Optionally chaining joins and typescript issues.

Optionally chaining joins and typescript issues.

Similar Threads

Similar Threads

Similar Threads