DT
Drizzle TeamJakesdoc

Select parent rows where child exists

I want to use a relational query to select all users and their posts where users have at least one post.
S
Sillvva•13d ago
This is from my code. You can use the exists method to filter rows based on a subquery. 
    const dms = await q.dungeonMasters.findMany({
        with: {
            logs: {
                with: {
                    character: {
                        columns: {
                            id: true,
                            name: true,
                            userId: true
                        }
                    }
                }
            }
        },
        where: (dms, { or, eq, and, exists }) =>
            and(
                id
                    ? and(or(eq(dms.owner, user.id), eq(dms.uid, user.id)), eq(dms.id, id))
                    : or(eq(dms.owner, user.id), eq(dms.uid, user.id)),
                exists(
                    db
                        .select({ id: logs.id })
                        .from(logs)
                        .innerJoin(characters, eq(logs.characterId, characters.id))
                        .where(and(eq(logs.dungeonMasterId, dms.id)))
                )
            )
    });
    const dms = await q.dungeonMasters.findMany({
        with: {
            logs: {
                with: {
                    character: {
                        columns: {
                            id: true,
                            name: true,
                            userId: true
                        }
                    }
                }
            }
        },
        where: (dms, { or, eq, and, exists }) =>
            and(
                id
                    ? and(or(eq(dms.owner, user.id), eq(dms.uid, user.id)), eq(dms.id, id))
                    : or(eq(dms.owner, user.id), eq(dms.uid, user.id)),
                exists(
                    db
                        .select({ id: logs.id })
                        .from(logs)
                        .innerJoin(characters, eq(logs.characterId, characters.id))
                        .where(and(eq(logs.dungeonMasterId, dms.id)))
                )
            )
    });
S
Sillvva•13d ago
https://orm.drizzle.team/docs/operators#exists
Drizzle ORM - Filters
Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.
J
Jakesdoc•13d ago
@Sillvva Maybe I'm just missing something, but it doesn't appear to do what I want. 
const users = await db.query.users.findMany({
  where: exists(
    db.select({id: post.id}).from(post).innerJoin(post, eq(post.userId, user.id))
  )
})
const users = await db.query.users.findMany({
  where: exists(
    db.select({id: post.id}).from(post).innerJoin(post, eq(post.userId, user.id))
  )
})
Does that look right? Because that still just returns every user.
S
Sillvva•13d ago
const users = await db.query.users.findMany({
  where: (users, { exists, eq }) => exists(
    db.select({id: post.id}).from(post).where(eq(post.userId, users.id))
  )
})
const users = await db.query.users.findMany({
  where: (users, { exists, eq }) => exists(
    db.select({id: post.id}).from(post).where(eq(post.userId, users.id))
  )
})
J
Jakesdoc•13d ago
That worked, thank you very much!
Want results from more Discord servers?
Add your server
More Posts
Creating a View with Turso/Sqlite```export const jobsView = sqliteView("jobsView").as((qb) => qb .select({ id: job.id, How to update "updated_at" column automaticly when related row is updated?Hello. I'm using PostgreSQL as a dbms. So driver is pg. I want to update the "updated_at" column wwrap migrations in transactiondoes drizzle wrap migrations in transaction?Error with Nested TransactionsI'm experiencing an issue with nested transactions on neon serverless 0.9.1, drizzle-orm 0.30.9, andSupabase branching w/ Drizzle?Does anyone know if it is possible to get Supabase branching working with Drizzle migrations? (httpsType inference is not working when findFirst is extractedHey typescript wizards, could anyone spot what I'm doing wrong here? ```ts type OrgTableQueryParamsDrizzle Query, Relationship Sub-QueryI have Domains and Products, and a pivot between them (DomainProducts). I'd like to query (`findFirMariaDB driver and generate migrationsThe company im working for insists on using MariaDB but im having issues generating migrations for mNaming Migrationshow do i set a name for a new migration when running `drizzle-kit generate:mysql` ? i tried setting [postgres][zod] createInsertSchemaHey! Trying to make my Sveltekit form schema. Here is my example: ```export const userAccessesFormSUse schema definition as normal typesI want to use my schema definition as a normal type. Basically what .select().from() returns - plainWhat is a recommended way to execute migration on CI ?Build facilities for migration assume it happens during app execution I don't sure it a good idea an