Drizzle Team•4w ago

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.

6 Replies

TOSL•3w ago

If you still need help, share the whole relevant schema. Initial error that's immediately visible is the db.users.findMany() should be db.query.users.findMany()

AnthonyOP•3w ago

@TOSL got it sorry yeah I just tried to simplify the example and that was a typo I'll share exactly what I'm working with

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.

Mario564•3w ago

@Anthony In the above code you provided, where's the error being thrown? The above query looks very different from your stripped down example so I'm not sure. Also, it may be relevant to know what conditions are/could be present in customConditions.

TOSL•3w ago

You want to use a where condition that relies on the related table which the relation/query api has some restricting limitations in the current implementation. These limitations of reducing if not going away entirely in V2 on the api. https://github.com/drizzle-team/drizzle-orm/discussions/2316 You can do something like

const subquery = db.select({ id: info.userId }).from(info).where(eq(info.orgId, 1)))

const result = await db.query.users.findMany({
  where: inArray(users.id, subquery,
  with: {
    info: true
  },
});

const subquery = db.select({ id: info.userId }).from(info).where(eq(info.orgId, 1)))

const result = await db.query.users.findMany({
  where: inArray(users.id, subquery,
  with: {
    info: true
  },
});

Basically, you use a subquery.

GitHub

Relational API v2 · drizzle-team drizzle-orm · Discussion #2316

We've launched Drizzle Relational Queries exactly a year ago and it's time to ship a fundamental upgrade. We've gathered a massive amount of valuable feedback from the community and goi...

AnthonyOP•2w ago

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.

TOSL•2w ago

What's your point? I already told you it's not directly possible using Drizzle's relation API. I gave you the only way to make it work. Use select if you want direct solution

Gaming

Programming

How to filter by joined tables with Drizzle query syntax?

Did you find this page helpful?