Drizzle Team•9mo ago

Conditional join

Hey! I am looking for a way of making conditional joins based on some statement. I found only one post in this topic https://discord.com/channels/1043890932593987624/1128636955270324275 that does not help me much. lets say this is my query:

let query = await db
            .select()
            .from(documentLine)
            .leftJoin(document, eq(document.id, documentLine.documentId))
            .leftJoin(user, eq(user.id, documentLine.userId))

let query = await db
            .select()
            .from(documentLine)
            .leftJoin(document, eq(document.id, documentLine.documentId))
            .leftJoin(user, eq(user.id, documentLine.userId))

I need to make joins conditional, because some filters require joins and in other cases I do not need join so its like wasted computation time. So example showing my need:

if (filters.includes(documentId)){
query.add(.leftJoin(document, eq(document.id, documentLine.documentId)))
}

if (filters.includes(documentId)){
query.add(.leftJoin(document, eq(document.id, documentLine.documentId)))
}

I would be glad for any tip how to do this.

5 Replies

__doei__•7mo ago

Exact same question for me. Conditional joins would be really useful to have generic functions that can adapt their return value to what the caller needs.

freddie•7mo ago

https://orm.drizzle.team/docs/dynamic-query-building

let query = await db
            .select()
            .from(documentLine)
            .$dynamic()

if (filters.includes(documentId)) {
  // add to query (optionally use $dynamic() again)
  return query.leftJoin(document, eq(document.id, documentLine.documentId)));
}

// fallback to just running it
return query.run();

let query = await db
            .select()
            .from(documentLine)
            .$dynamic()

if (filters.includes(documentId)) {
  // add to query (optionally use $dynamic() again)
  return query.leftJoin(document, eq(document.id, documentLine.documentId)));
}

// fallback to just running it
return query.run();

Drizzle ORM - Dynamic query building

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

__doei__•7mo ago

I've tried every way I could come up with, including with $dynamic however the types never end up to be correct

__doei__•7mo ago

freddie•7mo ago

you’ll need dynamic with generics like the example, let me make u a sample when i’m back on computer

Gaming

Programming

Conditional join