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 there! I was wondering if there was a way to query by a joined table using drizzle query syntax. Here's a concise example: 
TABLE users
id
name

TABLE info
id
user_id
organization_id
description

users.id <-> info.user_id (one-to-one)
TABLE users
id
name

TABLE info
id
user_id
organization_id
description

users.id <-> info.user_id (one-to-one)
import { posts } from './schema';

const results = await db.users.findMany({
  with: {
    info: true;
  },
  where: (users, { eq }) => {
    return [eq(info.organizationId, '0')];
  }
});

console.log(results);

// error: PostgresError: column users.organization_id does not exist
import { posts } from './schema';

const results = await db.users.findMany({
  with: {
    info: true;
  },
  where: (users, { eq }) => {
    return [eq(info.organizationId, '0')];
  }
});

console.log(results);

// error: PostgresError: column users.organization_id does not exist
So how is this supposed to be done? users <-> info is 1 to 1 so with .select() query I would just filter by the joined table like I'm trying above, but this is not working in this case. I can't add a where to the info block since it's a one to one.
11 replies
DTDrizzle Team
Created by Anthony on 11/19/2024 in #help
Double 'where' clause in dynamic query overriding the original 'where' clause not combining
Hi I am using Drizzle to fetch some records: 
    const query = db
        .select({
            // some fields
        })
        .from(leads)
        .innerJoin(participants, eq(participants.id, leads.participantId))
        .innerJoin(
            organizations,
            eq(organizations.id, participants.organizationId)
        )
        .innerJoin(campaigns, eq(campaigns.id, leads.campaignId))
        .leftJoin(agents, eq(agents.id, leads.agentId))
        .leftJoin(sites, eq(sites.id, leads.siteId))
        .leftJoin(studies, eq(studies.id, leads.studyId))
        .where((t) => {
            // ... a bunch of conditions
        })
        .orderBy(desc(leads.updatedAt))
        .$dynamic();
    const query = db
        .select({
            // some fields
        })
        .from(leads)
        .innerJoin(participants, eq(participants.id, leads.participantId))
        .innerJoin(
            organizations,
            eq(organizations.id, participants.organizationId)
        )
        .innerJoin(campaigns, eq(campaigns.id, leads.campaignId))
        .leftJoin(agents, eq(agents.id, leads.agentId))
        .leftJoin(sites, eq(sites.id, leads.siteId))
        .leftJoin(studies, eq(studies.id, leads.studyId))
        .where((t) => {
            // ... a bunch of conditions
        })
        .orderBy(desc(leads.updatedAt))
        .$dynamic();
and I want to do an additional level of filtering as well later in the function like so: 
await query.where(inArray(participants.id, searchResults)))
await query.where(inArray(participants.id, searchResults)))
However these results now don't still have the original filters from the where clause in initial dynamic query? Is this intentional and how do I fix it?
2 replies
DTDrizzle Team
Created by Anthony on 11/13/2024 in #help
For update skip locked
Is there a skip locked option given a .for('update') in the select clause in drizzle?
2 replies
DTDrizzle Team
Created by Anthony on 11/12/2024 in #help
Struggling with simple subquery statement in Drizzle
Hi friends, I have a complex query like so that I've made into a subquery: 
const query = db
        .select({
            ...
        })
        .from(leads)
        .innerJoin(participants, eq(participants.id, leads.participantId))
        .innerJoin(campaigns, eq(campaigns.id, leads.campaignId))
        .leftJoin(agents, eq(agents.id, leads.agentId))
        .leftJoin(sites, eq(sites.id, leads.siteId))
        .leftJoin(studies, eq(studies.id, leads.studyId))
        .where((t) => {
            const conditions = [eq(campaigns.organizationId, organizationId)];

            ...

            return and(...conditions);
        })
        .as('query');
const query = db
        .select({
            ...
        })
        .from(leads)
        .innerJoin(participants, eq(participants.id, leads.participantId))
        .innerJoin(campaigns, eq(campaigns.id, leads.campaignId))
        .leftJoin(agents, eq(agents.id, leads.agentId))
        .leftJoin(sites, eq(sites.id, leads.siteId))
        .leftJoin(studies, eq(studies.id, leads.studyId))
        .where((t) => {
            const conditions = [eq(campaigns.organizationId, organizationId)];

            ...

            return and(...conditions);
        })
        .as('query');
and I want to do some additional filtering on the query and fetch the count as well like so: 
const [{ count: total }] = await db.select({ count: count() }).from(query);

    const basicLeads = await db
        .select()
        .from(query)
        .where((t) =>
            participantIds
                ? inArray(t.participantId, participantIds)
                : undefined
        )
        .limit(perPage)
        .offset(Math.max(0, page - 1) * perPage);
const [{ count: total }] = await db.select({ count: count() }).from(query);

    const basicLeads = await db
        .select()
        .from(query)
        .where((t) =>
            participantIds
                ? inArray(t.participantId, participantIds)
                : undefined
        )
        .limit(perPage)
        .offset(Math.max(0, page - 1) * perPage);
However I am getting the following error: Error: Your "lead->id" field references a column "leads"."id", but the table "leads" is not part of the query! Did you forget to join it? any ideas why this doesn't work? Docs + GPT have been no help.
5 replies
DTDrizzle Team
Created by Anthony on 10/28/2024 in #help
Race condition executing a common pattern (select or insert)
I'm having trouble understanding how to execute a common pattern in Postgres and I'm using Drizzle as an ORM adapted. The common pattern is this: I want to fetch a single row by some query, for example 
let record = await db.query.records.findFirst({
    where: eq(records.phoneNumber, '+15555555555'),
    orderBy: desc(records.createdAt)
});
let record = await db.query.records.findFirst({
    where: eq(records.phoneNumber, '+15555555555'),
    orderBy: desc(records.createdAt)
});
But if that record doesn't exist I want to create a new record and use that one later on in the file. 
let record = await db.query.records.findFirst({
    where: eq(records.phoneNumber, '+15555555555'),
    orderBy: asc(records.createdAt)
});

if (!record) {
    const [newRecord] = await db.insert(records)
        .values({
            phoneNumber: '+15555555555'
        })
        .returning();
    
    record = newRecord;
}
let record = await db.query.records.findFirst({
    where: eq(records.phoneNumber, '+15555555555'),
    orderBy: asc(records.createdAt)
});

if (!record) {
    const [newRecord] = await db.insert(records)
        .values({
            phoneNumber: '+15555555555'
        })
        .returning();
    
    record = newRecord;
}
Now, the thing is I am doing this in an API route that might be called multiple times at the same time and I want to avoid race conditions so I wrapped this in a transaction. 
const record = await db.transaction(async (tx) => {
    // First try to find the existing record
    const existingRecord = await tx.query.records.findFirst({
        where: eq(records.phoneNumber, '+15555555555'),
        orderBy: asc(records.createdAt)
    });

    if (existingRecord) {
        return existingRecord;
    }

    // If no record exists, create a new one
    const [newRecord] = await tx.insert(records)
        .values({
            phoneNumber: '+15555555555'
        })
        .returning();

    return newRecord;
});
const record = await db.transaction(async (tx) => {
    // First try to find the existing record
    const existingRecord = await tx.query.records.findFirst({
        where: eq(records.phoneNumber, '+15555555555'),
        orderBy: asc(records.createdAt)
    });

    if (existingRecord) {
        return existingRecord;
    }

    // If no record exists, create a new one
    const [newRecord] = await tx.insert(records)
        .values({
            phoneNumber: '+15555555555'
        })
        .returning();

    return newRecord;
});
Now the problem is that even though this is wrapped in a transaction, race conditions can still occur where 2 users call my API endpoint at the exact same time, and 2 new records are created. How do I update my API endpoint to avoid this scenario using Drizzle syntax or PostgreSQL?
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
Hi team! Love what you all are doing with Drizzle. I have an extremely common pattern that I use in my application which is using Drizzle queries to create paginated results like so: 
export const getFullSlots = async (options: GetFullSlotsOptions) => {
    const {
        ...
        page = 1,
        perPage = 20,
    } = options;

    return await db.query.slots.findMany({
        with: {
            campaign: true,
            site: true,
            study: true,
        },
        where: (slots, { and, gte, lte }) => (
               /* some filters */
            ),
        orderBy: (slots, { asc, sql }) => [
            sql`CASE WHEN ${slots.startTime} > CURRENT_TIMESTAMP THEN 0 ELSE 1 END`,
            asc(slots.startTime),
        ],
        limit: perPage,
        offset: Math.max(0, page - 1) * perPage,
    });
};
export const getFullSlots = async (options: GetFullSlotsOptions) => {
    const {
        ...
        page = 1,
        perPage = 20,
    } = options;

    return await db.query.slots.findMany({
        with: {
            campaign: true,
            site: true,
            study: true,
        },
        where: (slots, { and, gte, lte }) => (
               /* some filters */
            ),
        orderBy: (slots, { asc, sql }) => [
            sql`CASE WHEN ${slots.startTime} > CURRENT_TIMESTAMP THEN 0 ELSE 1 END`,
            asc(slots.startTime),
        ],
        limit: perPage,
        offset: Math.max(0, page - 1) * perPage,
    });
};
Now this works great for fetching rows -- the problem is for paginated results I want to also return the total count of rows, that the query returns. Not just the rows. I have managed to do this using the SQL API by creating a subquery, then fetching the count + the rows from that query and returning both. The problem is that when I use the query builder like above (which I prefer for complex nested objects) I don't know how to return the count or return the count before the limit and offset are applied. From what I've read in the docs, it seems like there is no way to do this with findMany and I have to refactor all my queries to use SQL syntax, am I correct? Thanks!
8 replies