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.

5 Replies

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

Sillvva•4mo 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.

Jakesdoc•4mo 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.

Sillvva•4mo 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))
  )
})

Jakesdoc•4mo ago

That worked, thank you very much!

Gaming

Programming

Select parent rows where child exists