Anthony
Anthony
PostsComments
DTDrizzle Team
Created by Anthony on 1/2/2025 in #help
How to filter by joined tables with Drizzle query syntax?
Hi Mario, no error, basically instead of using the subquery I want to do a basic SQL command involving joining two tables A and B and filtering by B.column instead of A.column but I can't do this using this API, @TOSL this seems like a workaround not a solution.
11 replies
DTDrizzle Team
Created by Anthony on 1/2/2025 in #help
How to filter by joined tables with Drizzle query syntax?
const config = {
        where: (
            leads,
            { eq, inArray, gte, lte, and, exists, or, notExists }
        ) => {
            const conditions = [
                inArray(
                    leads.campaignId,
                    db
                        .select({ id: campaigns.id })
                        .from(campaigns)
                        .where(eq(campaigns.organizationId, organizationId))
                ),
                ...(customConditions ?? []),
            ];

            return and(...conditions);
        },
        with: {
            participant: {
                with: {
                    organization: true,
                },
            },
            campaign: true,
        },
    } satisfies FindManyQueryConfig<typeof db.query.leads>;

    const qCount = db.query.leads.findMany({
        ...config,
        columns: { id: true },
    });

    const resultConfig = { ... };

    const [result, [{ total }]] = await Promise.all([
        db.query.leads.findMany(resultConfig),
        db.select({ total: count() }).from(sql`(${qCount}) as count_query`),
    ]);

    return { leads: result, total };
const config = {
        where: (
            leads,
            { eq, inArray, gte, lte, and, exists, or, notExists }
        ) => {
            const conditions = [
                inArray(
                    leads.campaignId,
                    db
                        .select({ id: campaigns.id })
                        .from(campaigns)
                        .where(eq(campaigns.organizationId, organizationId))
                ),
                ...(customConditions ?? []),
            ];

            return and(...conditions);
        },
        with: {
            participant: {
                with: {
                    organization: true,
                },
            },
            campaign: true,
        },
    } satisfies FindManyQueryConfig<typeof db.query.leads>;

    const qCount = db.query.leads.findMany({
        ...config,
        columns: { id: true },
    });

    const resultConfig = { ... };

    const [result, [{ total }]] = await Promise.all([
        db.query.leads.findMany(resultConfig),
        db.select({ total: count() }).from(sql`(${qCount}) as count_query`),
    ]);

    return { leads: result, total };
Here is my query. In the conditions array you can see that I'm doing a subquery to compare leads.campaignId to the campaigns with organizationId that matches the one provided outside the scope of this block of code. With a .select() I could just query where campaigns.organizationId == organizationId but here I need the subquery.
11 replies
DTDrizzle Team
Created by Anthony on 1/2/2025 in #help
How to filter by joined tables with Drizzle query syntax?
I'll share exactly what I'm working with
11 replies
DTDrizzle Team
Created by Anthony on 1/2/2025 in #help
How to filter by joined tables with Drizzle query syntax?
@TOSL got it sorry yeah I just tried to simplify the example and that was a typo
11 replies
DTDrizzle Team
Created by Anthony on 11/12/2024 in #help
Struggling with simple subquery statement in Drizzle
Oh ur right I will copy over my example in full
5 replies
DTDrizzle Team
Created by Anthony on 11/12/2024 in #help
Struggling with simple subquery statement in Drizzle
Hey @Angelelz , here's a minimal example: https://drizzle.run/qdym3nmhrqi30hoy9yndsors
5 replies
DTDrizzle Team
Created by Anthony on 10/28/2024 in #help
Race condition executing a common pattern (select or insert)
@Luxaritas I think we decided to go for an advisory lock
4 replies
DTDrizzle Team
Created by Anthony on 10/28/2024 in #help
Race condition executing a common pattern (select or insert)
My original though was an onConflictDoUpdate, but in this case the phoneNumber field is not unique.
4 replies
DTDrizzle Team
Created by Anthony on 10/19/2024 in #help
Struggling to build common pattern with queries -- fetching with count + offset/limit pagination
I see that it has to do with the <any, any> in 
export type FindManyQueryConfig<T extends RelationalQueryBuilder<any, any>> =
    Parameters<T['findMany']>[0];
export type FindManyQueryConfig<T extends RelationalQueryBuilder<any, any>> =
    Parameters<T['findMany']>[0];
but I'm not sure how to get that type dynamically.
8 replies
DTDrizzle Team
Created by Anthony on 10/19/2024 in #help
Struggling to build common pattern with queries -- fetching with count + offset/limit pagination
@Raphaël M (@rphlmr) ⚡ hey thanks for the tip -- I'm having a problem using this with Typescript. When adding the with: clause to a call to findMany it automatically updates the Return type however the FindManyQueryConfig<typeof db.query.slots> is defined as a slot return type so the with: clause is ignored and thus the resulting type returned from the above getUsers function is not typed correctly.
8 replies
DTDrizzle Team
Created by Anthony on 10/19/2024 in #help
Struggling to build common pattern with queries -- fetching with count + offset/limit pagination
@Raphaël M (@rphlmr) ⚡ this is perfect, you're amazing!
8 replies
DTDrizzle Team
Created by pato on 10/14/2024 in #help
Natively write SQL functions/triggers with Drizzle?
@pato see the Check option here if it fits your needs https://orm.drizzle.team/docs/indexes-constraints#check
9 replies
DTDrizzle Team
Created by pato on 10/14/2024 in #help
Natively write SQL functions/triggers with Drizzle?
No, this would require a custom migration
9 replies